"""
Management command to pre-compute and cache savings account balances.

Run nightly via cron:
    0 1 * * * /path/to/venv/bin/python manage.py refresh_savings_balance_cache

Or for a specific org:
    python manage.py refresh_savings_balance_cache --org_id 41
"""
from django.core.management.base import BaseCommand
from django.db import connection
from datetime import date


class Command(BaseCommand):
    help = 'Pre-compute savings account balances into cache table for fast report loading'

    def add_arguments(self, parser):
        parser.add_argument('--org_id', type=int, default=None,
                            help='Refresh only a specific organisation (default: all)')
        parser.add_argument('--as_at', type=str, default=None,
                            help='Balance date YYYY-MM-DD (default: today)')

    def handle(self, *args, **options):
        as_at = options['as_at'] or str(date.today())
        org_id = options['org_id']

        if org_id:
            org_ids = [org_id]
        else:
            with connection.cursor() as cur:
                cur.execute("SELECT DISTINCT branch_organisation_id FROM organisation_branch ORDER BY 1")
                org_ids = [row[0] for row in cur.fetchall()]

        self.stdout.write(f"Refreshing savings balance cache as_at={as_at} for {len(org_ids)} org(s)...")

        for oid in org_ids:
            try:
                with connection.cursor() as cur:
                    cur.execute("SELECT public.refresh_savings_balance_cache(%s, %s)", [oid, as_at])
                    rows = cur.fetchone()[0]
                    self.stdout.write(f"  org {oid}: {rows} rows upserted")
            except Exception as e:
                self.stderr.write(f"  org {oid}: ERROR - {e}")

        self.stdout.write(self.style.SUCCESS("Cache refresh complete."))