"""Macro Sync: fetch FRED series → macro_observations table."""
from __future__ import annotations

import argparse
import asyncio
import datetime as dt
import uuid

from sqlalchemy import select
from sqlalchemy.dialects.postgresql import insert

from libs.common.config import get_settings
from libs.common.ids import new_job_run_id
from libs.common.logging import bind_job_run_id, configure_logging, get_logger
from libs.db.models import JobRun, MacroObservation, MacroSeries, SyncCheckpoint
from libs.db.session import get_session
from libs.oracle_client import FredService, make_oracle_client

logger = get_logger(__name__)


async def run_macro_sync(run_id: str) -> dict[str, int]:
    settings = get_settings()
    fred_series_config = settings.get_fred_series()
    stats = {"seen": 0, "written": 0, "errors": 0}

    async with make_oracle_client() as client:
        svc = FredService(client)

        async with get_session() as session:
            job = JobRun(
                job_run_id=uuid.UUID(run_id),
                job_name="macro_sync",
                source_name="fred",
                run_date=dt.date.today(),
                status="running",
            )
            session.add(job)
            await session.flush()

            for series_config in fred_series_config:
                series_id = series_config["id"]
                try:
                    # Ensure macro_series row exists
                    existing = await session.execute(
                        select(MacroSeries).where(MacroSeries.series_id == series_id)
                    )
                    if existing.scalar_one_or_none() is None:
                        ms = MacroSeries(
                            series_id=series_id,
                            title=series_config.get("title"),
                            frequency=series_config.get("frequency"),
                            source_name="fred",
                        )
                        session.add(ms)
                        await session.flush()

                    # Fetch observations (last 2 years)
                    start = (dt.date.today() - dt.timedelta(days=730)).isoformat()
                    response = await svc.get_observations(series_id, start=start)
                    stats["seen"] += len(response.observations)

                    rows = []
                    for obs in response.observations:
                        try:
                            val = float(obs.value) if obs.value is not None else None
                        except (TypeError, ValueError):
                            val = None
                        rows.append({
                            "series_id": series_id,
                            "observation_date": dt.date.fromisoformat(obs.date),
                            "value": val,
                            "created_at_utc": dt.datetime.now(tz=dt.UTC),
                        })

                    if rows:
                        stmt = (
                            insert(MacroObservation)
                            .values(rows)
                            .on_conflict_do_nothing(
                                constraint="uq_macro_obs_series_date"
                            )
                        )
                        await session.execute(stmt)
                        stats["written"] += len(rows)

                    # Update checkpoint
                    cp_stmt = (
                        insert(SyncCheckpoint)
                        .values(
                            domain=f"fred:{series_id}",
                            last_sync_at_utc=dt.datetime.now(tz=dt.UTC),
                            last_sync_params={"start": start},
                            status="success",
                            created_at_utc=dt.datetime.now(tz=dt.UTC),
                            updated_at_utc=dt.datetime.now(tz=dt.UTC),
                        )
                        .on_conflict_do_update(
                            constraint="uq_sync_checkpoints_domain",
                            set_={
                                "last_sync_at_utc": dt.datetime.now(tz=dt.UTC),
                                "status": "success",
                                "updated_at_utc": dt.datetime.now(tz=dt.UTC),
                            },
                        )
                    )
                    await session.execute(cp_stmt)

                    logger.info("fred_series_synced", series_id=series_id, count=len(rows))

                except Exception as exc:
                    logger.error("fred_sync_error", series_id=series_id, error=str(exc))
                    stats["errors"] += 1

            job.status = "succeeded" if stats["errors"] == 0 else "partial"
            job.finished_at_utc = dt.datetime.now(tz=dt.UTC)
            job.records_seen = stats["seen"]
            job.records_written = stats["written"]
            job.error_count = stats["errors"]

    logger.info("macro_sync_done", **stats)
    return stats


def main() -> None:
    parser = argparse.ArgumentParser(description="Macro Sync")
    parser.add_argument("--run-id", default=new_job_run_id())
    args = parser.parse_args()

    settings = get_settings()
    configure_logging(settings.log_level)
    bind_job_run_id(args.run_id)

    asyncio.run(run_macro_sync(args.run_id))


if __name__ == "__main__":
    main()