#!/usr/bin/env python3
"""Build the public Plenti Market Lab SQLite database and browser snapshot.

Official values are extracted from hash-pinned ONS workbooks with cell lineage.
Source periods are rejected when they begin before the project's two-year cutoff.
Historical client artifacts remain in a separate evidence lane and never enter the
current market score.
"""

from __future__ import annotations

import json
import sqlite3
from datetime import datetime, timezone
from pathlib import Path

from extract_official_data import extract_official_data


ROOT = Path(__file__).resolve().parent
SNAPSHOT_PATH = ROOT / "data" / "source_snapshot.json"
SCHEMA_PATH = ROOT / "schema.sql"
DATABASE_PATH = ROOT / "plenti_market_lab.sqlite"
BROWSER_DATA_PATH = ROOT.parent / "data" / "market-lab.json"


MARKET_SQL = """
SELECT
  region,
  pressure_score,
  average_rent,
  rent_growth,
  young_adult_share,
  unrelated_adult_household_share,
  one_person_household_share,
  house_price,
  one_bed_rent,
  two_bed_rent,
  population,
  households_thousands
FROM v_market_pressure
ORDER BY pressure_score DESC, average_rent DESC;
""".strip()

SURVEY_SQL = """
SELECT
  s.label AS segment,
  s.sample_size AS n,
  m.label AS metric,
  o.category,
  ROUND(o.value, 2) AS value,
  m.unit,
  COALESCE(g.name, '—') AS geography,
  o.note
FROM survey_observation o
JOIN survey_segment s ON s.segment_id = o.segment_id
JOIN survey_metric m ON m.metric_id = o.metric_id
LEFT JOIN geography g ON g.geography_id = o.geography_id
ORDER BY s.segment_id, m.metric_id, o.value DESC;
""".strip()

ROUTING_SQL = """
SELECT
  from_q.question_order AS after_order,
  from_q.code AS after_question,
  COALESCE(condition_q.code, '—') AS condition_question,
  COALESCE(a.label, 'Always') AS condition_answer,
  next_q.code AS next_question,
  r.rule_expression
FROM routing_rule r
JOIN question from_q ON from_q.question_id = r.from_question_id
LEFT JOIN question condition_q ON condition_q.question_id = r.condition_question_id
LEFT JOIN answer_option a ON a.option_id = r.answer_option_id
JOIN question next_q ON next_q.question_id = r.next_question_id
ORDER BY from_q.question_order, r.priority;
""".strip()

FRESHNESS_SQL = """
SELECT
  source_kind,
  publisher,
  title,
  COALESCE(reference_period_start, 'Unknown') AS reference_period_start,
  COALESCE(reference_period_end, 'Unknown') AS reference_period_end,
  cutoff_date,
  release_date,
  freshness_status
FROM v_source_freshness
ORDER BY source_kind = 'client_artifact', reference_period_start DESC;
""".strip()

QUALITY_SQL = """
SELECT
  reported_sample_size,
  traceable_row_count,
  reported_sample_size - traceable_row_count AS unexplained_difference,
  (SELECT COUNT(*) FROM respondent) AS published_respondent_rows,
  (SELECT COUNT(*) FROM survey_observation) AS published_aggregate_rows,
  (SELECT s.title FROM survey_wave_source ws JOIN source_dataset s USING (source_id)
   WHERE ws.wave_id = survey_wave.wave_id AND ws.source_role = 'reported_sample') AS reported_sample_source,
  (SELECT s.title FROM survey_wave_source ws JOIN source_dataset s USING (source_id)
   WHERE ws.wave_id = survey_wave.wave_id AND ws.source_role = 'traceable_rows') AS traceable_rows_source
FROM survey_wave
WHERE code = 'plenti_original_2024';
""".strip()


def rows_as_dicts(connection: sqlite3.Connection, sql: str) -> list[dict]:
    cursor = connection.execute(sql)
    columns = [description[0] for description in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall()]


def enforce_freshness(snapshot: dict) -> None:
    cutoff = snapshot["build"]["freshness_cutoff"]
    stale = []
    for source in snapshot["sources"]:
        if source["source_kind"] != "official_statistic":
            continue
        start = source["reference_period_start"]
        if start is None or start < cutoff:
            stale.append(f"{source['source_id']} ({start or 'unknown'})")
    if stale:
        raise ValueError(
            "Official sources fail the strict reference-period cutoff "
            f"{cutoff}: {', '.join(stale)}"
        )


def insert_sources(connection: sqlite3.Connection, snapshot: dict) -> None:
    connection.executemany(
        """
        INSERT INTO source_dataset (
          source_id, source_kind, publisher, title, url, release_date,
          reference_period_start, reference_period_end, retrieved_date,
          licence, geography_level, notes
        ) VALUES (
          :source_id, :source_kind, :publisher, :title, :url, :release_date,
          :reference_period_start, :reference_period_end, :retrieved_date,
          :licence, :geography_level, :notes
        )
        """,
        snapshot["sources"],
    )


def insert_source_files(connection: sqlite3.Connection, snapshot: dict) -> None:
    connection.executemany(
        """
        INSERT INTO source_file (
          file_id, source_id, local_path, sha256, file_format, notes
        ) VALUES (
          :file_id, :source_id, :path, :sha256, :format, :notes
        )
        """,
        snapshot["raw_files"],
    )


def insert_geographies(connection: sqlite3.Connection, snapshot: dict) -> dict[str, int]:
    ids: dict[str, int] = {}
    for item in snapshot["geographies"]:
        parent_id = ids.get(item["parent"])
        cursor = connection.execute(
            """
            INSERT INTO geography (code, name, geography_type, parent_geography_id)
            VALUES (?, ?, ?, ?)
            """,
            (item["code"], item["name"], item["type"], parent_id),
        )
        ids[item["code"]] = cursor.lastrowid
    return ids


def insert_indicators(connection: sqlite3.Connection, snapshot: dict) -> dict[str, int]:
    ids: dict[str, int] = {}
    for item in snapshot["indicators"]:
        cursor = connection.execute(
            """
            INSERT INTO indicator (code, label, unit, topic, description)
            VALUES (:code, :label, :unit, :topic, :description)
            """,
            item,
        )
        ids[item["code"]] = cursor.lastrowid
    return ids


def insert_market_observations(
    connection: sqlite3.Connection,
    snapshot: dict,
    geography_ids: dict[str, int],
    indicator_ids: dict[str, int],
) -> None:
    metric_map = {
        "population": ("population_total", "ons_population_mid_2025", "2025-06-30", "2025-06-30", "final"),
        "age_18_24": ("population_18_24", "ons_population_mid_2025", "2025-06-30", "2025-06-30", "final"),
        "age_25_34": ("population_25_34", "ons_population_mid_2025", "2025-06-30", "2025-06-30", "final"),
        "young_share": ("population_18_34_share", "ons_population_mid_2025", "2025-06-30", "2025-06-30", "final"),
        "density": ("population_density", "ons_population_mid_2025", "2025-06-30", "2025-06-30", "final"),
        "median_age": ("median_age", "ons_population_mid_2025", "2025-06-30", "2025-06-30", "final"),
        "households": ("households_total_thousands", "ons_households_2025", "2025-04-01", "2025-06-30", "survey_estimate"),
        "one_person_share": ("one_person_household_share", "ons_households_2025", "2025-04-01", "2025-06-30", "survey_estimate"),
        "unrelated_adults": ("unrelated_adult_households_thousands", "ons_households_2025", "2025-04-01", "2025-06-30", "survey_estimate"),
        "unrelated_share": ("unrelated_adult_household_share", "ons_households_2025", "2025-04-01", "2025-06-30", "survey_estimate"),
        "rent": ("average_monthly_rent", "ons_private_rents_2026_07", "2026-07-01", "2026-07-31", "provisional"),
        "rent_growth": ("rent_growth_annual", "ons_private_rents_2026_07", "2025-07-01", "2026-07-31", "provisional"),
        "one_bed_rent": ("one_bed_monthly_rent", "ons_private_rents_2026_07", "2026-07-01", "2026-07-31", "provisional"),
        "two_bed_rent": ("two_bed_monthly_rent", "ons_private_rents_2026_07", "2026-07-01", "2026-07-31", "provisional"),
        "flat_rent": ("flat_monthly_rent", "ons_private_rents_2026_07", "2026-07-01", "2026-07-31", "provisional"),
        "house_price": ("average_house_price", "ons_house_prices_2026_06", "2026-06-01", "2026-06-30", "provisional"),
        "house_price_growth": ("house_price_growth_annual", "ons_house_prices_2026_06", "2025-06-01", "2026-06-30", "provisional"),
    }
    for region in snapshot["regions"]:
        for key, value in region.items():
            if key == "code":
                continue
            indicator_code, source_id, period_start, period_end, status = metric_map[key]
            note = ""
            if source_id == "ons_households_2025":
                note = "LFS estimate; totals may not sum because of rounding."
            elif status == "provisional":
                note = "Latest published period is provisional and may be revised."
            cursor = connection.execute(
                """
                INSERT INTO area_observation (
                  geography_id, indicator_id, source_id, period_start,
                  period_end, value, status, note
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    geography_ids[region["code"]],
                    indicator_ids[indicator_code],
                    source_id,
                    period_start,
                    period_end,
                    value,
                    status,
                    note,
                ),
            )
            entries = snapshot["_lineage"].get(region["code"], {}).get(key, [])
            if not entries:
                raise ValueError(f"Missing source-cell lineage for {region['code']} × {key}")
            for entry in entries:
                connection.execute(
                    """
                    INSERT INTO observation_lineage (
                      observation_id, file_id, sheet_name, cell_range, transformation
                    ) VALUES (?, ?, ?, ?, ?)
                    """,
                    (
                        cursor.lastrowid,
                        entry["file_id"],
                        entry["sheet_name"],
                        entry["cell_range"],
                        entry["transformation"],
                    ),
                )


def insert_survey(
    connection: sqlite3.Connection,
    snapshot: dict,
    geography_ids: dict[str, int],
) -> None:
    survey = snapshot["survey"]
    wave = survey["wave"]
    cursor = connection.execute(
        """
        INSERT INTO survey_wave (
          code, name, fieldwork_start, fieldwork_end, reported_sample_size,
          traceable_row_count, data_level, caveat
        ) VALUES (
          :code, :name, :fieldwork_start, :fieldwork_end, :reported_sample_size,
          :traceable_row_count, :data_level, :caveat
        )
        """,
        wave,
    )
    wave_id = cursor.lastrowid

    for source in wave["sources"]:
        connection.execute(
            """
            INSERT INTO survey_wave_source (wave_id, source_id, source_role, note)
            VALUES (?, ?, ?, ?)
            """,
            (wave_id, source["source_id"], source["role"], source["note"]),
        )

    connection.execute(
        """
        INSERT INTO survey_version (wave_id, code, version_label, status, notes)
        VALUES (?, 'plenti_original_v1', 'Original fielded survey', 'fielded',
                'Sixteen visible fields in the raster table; exact platform logic export unavailable.')
        """,
        (wave_id,),
    )
    cursor = connection.execute(
        """
        INSERT INTO survey_version (wave_id, code, version_label, status, notes)
        VALUES (?, 'plenti_redesign_v2', 'Proposed survey with executable screener', 'redesign',
                'Eleven revised questions from final presentation slide 28 plus one independently added tenure screener; no response data was collected for this version.')
        """,
        (wave_id,),
    )
    redesign_id = cursor.lastrowid

    segment_ids: dict[str, int] = {}
    for segment in survey["segments"]:
        parent_id = segment_ids.get(segment["parent"])
        cursor = connection.execute(
            """
            INSERT INTO survey_segment (wave_id, code, label, sample_size, parent_segment_id)
            VALUES (?, ?, ?, ?, ?)
            """,
            (wave_id, segment["code"], segment["label"], segment["sample_size"], parent_id),
        )
        segment_ids[segment["code"]] = cursor.lastrowid

    metric_ids: dict[str, int] = {}
    for metric in survey["metrics"]:
        cursor = connection.execute(
            """
            INSERT INTO survey_metric (code, label, unit, question_context)
            VALUES (:code, :label, :unit, :context)
            """,
            metric,
        )
        metric_ids[metric["code"]] = cursor.lastrowid

    for observation in survey["observations"]:
        connection.execute(
            """
            INSERT INTO survey_observation (
              segment_id, metric_id, geography_id, source_id, category,
              value, sample_size, is_aggregate, note
            ) VALUES (?, ?, ?, ?, ?, ?, ?, 1, ?)
            """,
            (
                segment_ids[observation["segment"]],
                metric_ids[observation["metric"]],
                geography_ids.get(observation.get("geography")),
                observation["source"],
                observation.get("category", ""),
                observation["value"],
                observation["n"],
                observation["note"],
            ),
        )

    for segment_code, counts in survey["housing_counts"].items():
        segment_n = next(s["sample_size"] for s in survey["segments"] if s["code"] == segment_code)
        for category, count in counts.items():
            for metric_code, value, note in (
                ("housing_type_count", count, "Count resolved from the visible raster."),
                ("housing_type_share", round(100 * count / segment_n, 4), "Computed from the traceable segment denominator."),
            ):
                connection.execute(
                    """
                    INSERT INTO survey_observation (
                      segment_id, metric_id, source_id, category, value,
                      sample_size, is_aggregate, note
                    ) VALUES (?, ?, 'plenti_survey_raster_2024', ?, ?, ?, 1, ?)
                    """,
                    (segment_ids[segment_code], metric_ids[metric_code], category, value, segment_n, note),
                )

    all_counts = {
        category: survey["housing_counts"]["renter"][category] + survey["housing_counts"]["owner"][category]
        for category in survey["housing_counts"]["renter"]
    }
    for category, count in all_counts.items():
        for metric_code, value, note in (
            ("housing_type_count", count, "Count resolved from all 95 visible raster rows."),
            ("housing_type_share", round(100 * count / 95, 4), "Computed from all 95 visible raster rows."),
        ):
            connection.execute(
                """
                INSERT INTO survey_observation (
                  segment_id, metric_id, source_id, category, value,
                  sample_size, is_aggregate, note
                ) VALUES (?, ?, 'plenti_survey_raster_2024', ?, ?, 95, 1, ?)
                """,
                (segment_ids["all"], metric_ids[metric_code], category, value, note),
            )

    renter_counts = survey["housing_counts"]["renter"]
    for item in survey["renter_housing_metrics"]:
        category = item["category"]
        n = renter_counts[category]
        for metric_code, value in (
            ("housing_type_average_rent", item["average_rent"]),
            ("housing_type_same_size_premium", item["same_size"]),
            ("housing_type_smaller_space_premium", item["smaller_space"]),
        ):
            connection.execute(
                """
                INSERT INTO survey_observation (
                  segment_id, metric_id, source_id, category, value,
                  sample_size, is_aggregate, note
                ) VALUES (?, ?, 'plenti_survey_raster_2024', ?, ?, ?, 1,
                          'Aggregate chart value; dispersion and weighting are undocumented.')
                """,
                (segment_ids["renter"], metric_ids[metric_code], category, value, n),
            )

    question_ids: dict[str, int] = {}
    option_ids: dict[tuple[str, str], int] = {}
    for item in survey["questions"]:
        cursor = connection.execute(
            """
            INSERT INTO question (
              version_id, code, question_order, prompt, audience, response_type, origin
            ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """,
            (
                redesign_id,
                item["code"],
                item["order"],
                item["prompt"],
                item["audience"],
                item["type"],
                item.get("origin", "client_redesign"),
            ),
        )
        question_ids[item["code"]] = cursor.lastrowid
        for index, label in enumerate(item["options"], start=1):
            code = label.lower().replace(" ", "_")
            option_cursor = connection.execute(
                """
                INSERT INTO answer_option (question_id, code, label, sort_order)
                VALUES (?, ?, ?, ?)
                """,
                (cursor.lastrowid, code, label, index),
            )
            option_ids[(item["code"], code)] = option_cursor.lastrowid

    routes = [
        ("Q00", "Q01", None, None, "always"),
        ("Q01", "Q02", None, None, "always"),
        ("Q02", "Q03", None, None, "always"),
        ("Q03", "Q04", None, None, "always"),
        ("Q04", "Q05", "Q00", "renter", "Q00 = 'Renter'"),
        ("Q04", "Q06", "Q00", "homeowner", "Q00 = 'Homeowner'"),
        ("Q05", "Q07", None, None, "renter path"),
        ("Q06", "Q08", None, None, "owner path"),
        ("Q07", "Q09", None, None, "renter path rejoins"),
        ("Q08", "Q09", None, None, "owner path rejoins"),
        ("Q09", "Q10", None, None, "always"),
        ("Q10", "Q11", None, None, "always"),
    ]
    for priority, (start, end, condition_question, condition_option, expression) in enumerate(routes, start=1):
        condition_question_id = question_ids.get(condition_question)
        answer_option_id = option_ids.get((condition_question, condition_option)) if condition_question else None
        connection.execute(
            """
            INSERT INTO routing_rule (
              from_question_id, condition_question_id, answer_option_id,
              next_question_id, priority, rule_expression
            ) VALUES (?, ?, ?, ?, ?, ?)
            """,
            (
                question_ids[start],
                condition_question_id,
                answer_option_id,
                question_ids[end],
                priority,
                expression,
            ),
        )


def build_browser_payload(connection: sqlite3.Connection, snapshot: dict, built_at: str) -> dict:
    region_rows = rows_as_dicts(connection, MARKET_SQL)
    source_rows = rows_as_dicts(
        connection,
        """
        SELECT source_id, source_kind, publisher, title, url, release_date,
               reference_period_start, reference_period_end, licence, notes
        FROM source_dataset
        ORDER BY source_kind DESC, reference_period_end DESC
        """,
    )
    housing_rows = rows_as_dicts(
        connection,
        """
        SELECT s.code AS segment_code, s.label AS segment, s.sample_size AS n,
               o.category, MAX(CASE WHEN m.code = 'housing_type_count' THEN o.value END) AS count,
               MAX(CASE WHEN m.code = 'housing_type_share' THEN o.value END) AS share
        FROM survey_observation o
        JOIN survey_segment s ON s.segment_id = o.segment_id
        JOIN survey_metric m ON m.metric_id = o.metric_id
        WHERE m.code IN ('housing_type_count', 'housing_type_share')
        GROUP BY s.segment_id, o.category
        ORDER BY s.segment_id, count DESC
        """,
    )
    summary_rows = rows_as_dicts(
        connection,
        """
        SELECT s.code AS segment_code, s.label AS segment, s.sample_size AS n,
          MAX(CASE WHEN m.code = 'average_age' THEN o.value END) AS average_age,
          MAX(CASE WHEN m.code = 'average_housing_cost' THEN o.value END) AS average_housing_cost,
          MAX(CASE WHEN m.code = 'smart_wall_preference_share' THEN o.value END) AS preference_share,
          MAX(CASE WHEN m.code = 'same_size_premium' THEN o.value END) AS same_size_premium,
          MAX(CASE WHEN m.code = 'smaller_space_premium' THEN o.value END) AS smaller_space_premium
        FROM survey_segment s
        JOIN survey_observation o ON o.segment_id = s.segment_id
        JOIN survey_metric m ON m.metric_id = o.metric_id
        WHERE s.code IN ('renter', 'owner')
        GROUP BY s.segment_id
        ORDER BY s.segment_id
        """,
    )
    route_rows = rows_as_dicts(connection, ROUTING_SQL)
    table_counts = rows_as_dicts(
        connection,
        """
        SELECT 'source_dataset' AS table_name, COUNT(*) AS row_count FROM source_dataset
        UNION ALL SELECT 'source_file', COUNT(*) FROM source_file
        UNION ALL SELECT 'geography', COUNT(*) FROM geography
        UNION ALL SELECT 'area_observation', COUNT(*) FROM area_observation
        UNION ALL SELECT 'observation_lineage', COUNT(*) FROM observation_lineage
        UNION ALL SELECT 'survey_wave_source', COUNT(*) FROM survey_wave_source
        UNION ALL SELECT 'survey_observation', COUNT(*) FROM survey_observation
        UNION ALL SELECT 'question', COUNT(*) FROM question
        UNION ALL SELECT 'routing_rule', COUNT(*) FROM routing_rule
        UNION ALL SELECT 'respondent', COUNT(*) FROM respondent
        """,
    )

    query_specs = [
        ("market", "Rank regional space pressure", "Join demography, households, rents and prices by ONS region code.", MARKET_SQL),
        ("survey", "Audit every survey aggregate", "Keep segment n, category, geography and source attached to each result.", SURVEY_SQL),
        ("routing", "Reconstruct the revised survey", "Represent questions and route-specific skips without false missingness.", ROUTING_SQL),
        ("freshness", "Audit source freshness", "Prove current market inputs begin after the strict cutoff; quarantine the historical client study.", FRESHNESS_SQL),
        ("quality", "Catch the 96-versus-95 mismatch", "Compare the deck-reported sample with visible raster rows and their separate sources.", QUALITY_SQL),
    ]
    queries = []
    for code, label, description, sql in query_specs:
        rows = rows_as_dicts(connection, sql)
        queries.append(
            {
                "code": code,
                "label": label,
                "description": description,
                "sql": sql,
                "columns": list(rows[0].keys()) if rows else [],
                "rows": rows,
            }
        )

    return {
        "meta": {
            "built_at": built_at,
            "as_of": snapshot["build"]["as_of"],
            "freshness_cutoff": snapshot["build"]["freshness_cutoff"],
            "market_source_count": sum(1 for source in snapshot["sources"] if source["source_kind"] == "official_statistic"),
            "raw_source_file_count": len(snapshot["raw_files"]),
            "region_count": len(region_rows),
            "reported_survey_n": snapshot["survey"]["wave"]["reported_sample_size"],
            "traceable_survey_rows": snapshot["survey"]["wave"]["traceable_row_count"],
            "integrity": "ok",
        },
        "regions": region_rows,
        "survey": {
            "summary": summary_rows,
            "housing": housing_rows,
            "routes": route_rows,
            "quality": rows_as_dicts(connection, QUALITY_SQL)[0],
        },
        "sources": source_rows,
        "schema": table_counts,
        "queries": queries,
    }


def main() -> None:
    snapshot = json.loads(SNAPSHOT_PATH.read_text(encoding="utf-8"))
    extracted = extract_official_data(snapshot["raw_files"])
    snapshot["regions"] = extracted["regions"]
    snapshot["_lineage"] = extracted["lineage"]
    enforce_freshness(snapshot)

    DATABASE_PATH.unlink(missing_ok=True)
    connection = sqlite3.connect(DATABASE_PATH)
    try:
        connection.executescript(SCHEMA_PATH.read_text(encoding="utf-8"))
        with connection:
            insert_sources(connection, snapshot)
            insert_source_files(connection, snapshot)
            geography_ids = insert_geographies(connection, snapshot)
            indicator_ids = insert_indicators(connection, snapshot)
            insert_market_observations(connection, snapshot, geography_ids, indicator_ids)
            insert_survey(connection, snapshot, geography_ids)

        integrity = connection.execute("PRAGMA integrity_check").fetchone()[0]
        if integrity != "ok":
            raise RuntimeError(f"SQLite integrity check failed: {integrity}")

        official_failures = connection.execute(
            """
            SELECT COUNT(*)
            FROM v_source_freshness f
            JOIN source_dataset s USING (source_id)
            WHERE s.source_kind = 'official_statistic'
              AND f.freshness_status <> 'within_two_years'
            """
        ).fetchone()[0]
        if official_failures:
            raise RuntimeError(f"{official_failures} official sources failed the freshness view")

        lineage_failures = connection.execute(
            """
            SELECT COUNT(*)
            FROM area_observation o
            WHERE NOT EXISTS (
              SELECT 1 FROM observation_lineage l WHERE l.observation_id = o.observation_id
            )
            """
        ).fetchone()[0]
        if lineage_failures:
            raise RuntimeError(f"{lineage_failures} official observations have no raw-cell lineage")

        built_at = datetime.now(timezone.utc).replace(microsecond=0).isoformat()
        source_count = connection.execute("SELECT COUNT(*) FROM source_dataset").fetchone()[0]
        observation_count = connection.execute(
            "SELECT (SELECT COUNT(*) FROM area_observation) + (SELECT COUNT(*) FROM survey_observation)"
        ).fetchone()[0]
        with connection:
            connection.execute(
                """
                INSERT INTO refresh_run (
                  built_at, freshness_cutoff, source_count, observation_count, integrity_result
                ) VALUES (?, ?, ?, ?, ?)
                """,
                (built_at, snapshot["build"]["freshness_cutoff"], source_count, observation_count, integrity),
            )

        payload = build_browser_payload(connection, snapshot, built_at)
        BROWSER_DATA_PATH.parent.mkdir(parents=True, exist_ok=True)
        BROWSER_DATA_PATH.write_text(json.dumps(payload, indent=2) + "\n", encoding="utf-8")
        connection.execute("PRAGMA optimize")
        connection.commit()
    finally:
        connection.close()

    print(f"Built {DATABASE_PATH.name}")
    print(f"Built {BROWSER_DATA_PATH.relative_to(ROOT.parent)}")


if __name__ == "__main__":
    main()