Python Microsoft Fabric - Sending reports on a schedule

# -*- coding: utf-8 -*-

# stdlib
import csv
import io
from datetime import date, timedelta

# Zato
from zato.common import SMTPMessage
from zato.server.service import Service

class MonthlyInvoiceSummary(Service):

    name = 'reports.monthly-invoice-summary'

    def handle(self):

        workspace_id = '11111111-2222-3333-4444-555555555555'
        lakehouse_id = '66666666-7777-8888-9999-000000000000'

        conn = self.microsoft.fabric['My Fabric']

        # Last month - from its first day up to, not including, the first day of this month.
        today = date.today()
        month_start = today.replace(day=1)
        previous_month_end = month_start - timedelta(days=1)
        previous_month_start = previous_month_end.replace(day=1)
        month_label = previous_month_start.strftime('%Y-%m')

        range_start = previous_month_start.isoformat()
        range_end = month_start.isoformat()

        # One row per insurer - what was invoiced and what has been paid.
        sql = f'''
        select
            insurer,
            count(*) as invoices,
            round(sum(amount), 2) as invoiced,
            round(sum(case when status = 'Paid' then amount else 0 end), 2) as paid
        from invoices
        where sent_at >= '{range_start}'
          and sent_at < '{range_end}'
        group by insurer
        order by insurer
        '''

        rows = conn.query(workspace_id, lakehouse_id, sql)

        # Turn the rows into CSV ..
        buffer = io.StringIO()
        writer = csv.DictWriter(buffer, fieldnames=['insurer', 'invoices', 'invoiced', 'paid'])
        writer.writeheader()
        writer.writerows(rows)

        text = buffer.getvalue()
        file_name = f'invoice-summary-{month_label}.csv'

        # .. put it where the finance system picks files up ..
        remote_path = f'/reports/{file_name}'

        sftp = self.sftp['Finance SFTP']
        sftp.write(text, remote_path)

        # .. and email it.
        msg = SMTPMessage()
        msg.subject = f'Invoice summary {month_label}'
        msg.to = 'finance-reports@example.com'
        msg.from_ = 'zato@example.com'
        msg.body = f'The invoice summary for {month_label} is attached.'
        msg.attach(file_name, text)

        smtp = self.email.smtp.get('Reports Email')
        smtp.conn.send(msg)

        self.response.payload = {'file': file_name, 'rows': len(rows)}