"""
Create Metabase dashboards programmatically.

Reads an existing "source" dashboard, creates a replica with cards pointing at
a different base question, and optionally creates a comparison dashboard with
native SQL cards.

This is useful for:
- Migrating dashboards to a new data model (same layout, new data source)
- Creating A/B comparison dashboards between two models
- Automating dashboard creation from SQL files

Usage:
    uv run scripts/create_dashboards.py

Configuration:
    Edit the constants at the top of the file to match your setup.

Environment variables (from .env):
    METABASE_URL      - e.g. https://metabase.example.com
    METABASE_API_KEY  - Metabase API key
"""

from __future__ import annotations

import json
import os
import sys
from pathlib import Path

import requests
from dotenv import load_dotenv

# ---------------------------------------------------------------------------
# Configuration — edit these for your project
# ---------------------------------------------------------------------------

PROJECT_ROOT = Path(__file__).resolve().parents[2]
load_dotenv(PROJECT_ROOT / ".env")

METABASE_URL = os.environ.get("METABASE_URL", "").rstrip("/")
METABASE_API_KEY = os.environ.get("METABASE_API_KEY", "")

# IDs specific to your Metabase instance
SOURCE_DASHBOARD_ID = 2  # Dashboard to replicate
SOURCE_BASE_CARD_ID = 34  # The "base question" that source cards reference
DATABASE_ID = 2  # Metabase database ID (find via GET /api/database)
TARGET_COLLECTION_ID = None  # Collection to save new cards/dashboards in

# Path to the SQL file for the new base question
BASE_QUERY_SQL_PATH = PROJECT_ROOT / "metabase" / "base_query.sql"

# Path to comparison query SQL files (optional)
COMPARISON_QUERIES_DIR = PROJECT_ROOT / "metabase" / "comparison_queries"

# ---------------------------------------------------------------------------
# API helpers
# ---------------------------------------------------------------------------


def api_headers() -> dict[str, str]:
    return {
        "Content-Type": "application/json",
        "x-api-key": METABASE_API_KEY,
    }


def api_get(path: str) -> dict:
    resp = requests.get(
        f"{METABASE_URL}/api{path}", headers=api_headers(), timeout=30
    )
    resp.raise_for_status()
    return resp.json()


def api_post(path: str, payload: dict) -> dict:
    resp = requests.post(
        f"{METABASE_URL}/api{path}",
        headers=api_headers(),
        json=payload,
        timeout=30,
    )
    if not resp.ok:
        print(
            f"ERROR POST {path}: {resp.status_code} {resp.text[:500]}",
            file=sys.stderr,
        )
    resp.raise_for_status()
    return resp.json()


def api_put(path: str, payload: dict) -> dict:
    resp = requests.put(
        f"{METABASE_URL}/api{path}",
        headers=api_headers(),
        json=payload,
        timeout=30,
    )
    if not resp.ok:
        print(
            f"ERROR PUT {path}: {resp.status_code} {resp.text[:500]}",
            file=sys.stderr,
        )
    resp.raise_for_status()
    return resp.json()


# ---------------------------------------------------------------------------
# Step 1: Create the new base question
# ---------------------------------------------------------------------------


def create_base_question(
    name: str,
    sql_path: Path,
    collection_id: int | None = None,
) -> int:
    """Create a native SQL base question and return its card ID."""
    sql = sql_path.read_text()

    # Remove Metabase template tags (e.g. {{filter}}) — handle parameters
    # separately via dashboard parameter mappings
    sql_clean = "\n".join(
        line for line in sql.splitlines() if "{{" not in line
    )

    payload = {
        "name": name,
        "dataset_query": {
            "type": "native",
            "native": {"query": sql_clean},
            "database": DATABASE_ID,
        },
        "display": "table",
        "visualization_settings": {},
    }
    if collection_id:
        payload["collection_id"] = collection_id

    result = api_post("/card", payload)
    card_id = result["id"]
    print(f"  Created base question: card {card_id}")
    return card_id


# ---------------------------------------------------------------------------
# Step 2: Replicate a dashboard with a new base question
# ---------------------------------------------------------------------------


def _rewrite_source_table(query: dict, new_base_card_id: int) -> None:
    """Recursively rewrite source-table references from old base to new base."""
    source = query.get("source-table")
    if isinstance(source, str) and source.startswith("card__"):
        old_id = int(source.replace("card__", ""))
        if old_id == SOURCE_BASE_CARD_ID:
            query["source-table"] = f"card__{new_base_card_id}"

    # Handle nested queries (joins, etc.)
    for join in query.get("joins", []):
        join_source = join.get("source-table")
        if isinstance(join_source, str) and join_source.startswith("card__"):
            old_id = int(join_source.replace("card__", ""))
            if old_id == SOURCE_BASE_CARD_ID:
                join["source-table"] = f"card__{new_base_card_id}"

    # source-query nesting
    if "source-query" in query:
        _rewrite_source_table(query["source-query"], new_base_card_id)


def replicate_card(
    poc_card_id: int,
    new_base_card_id: int,
    collection_id: int | None = None,
) -> int:
    """Clone a child card, rewriting its source-table to the new base question."""
    card_detail = api_get(f"/card/{poc_card_id}")

    # Skip the base question itself — we already created it
    if card_detail["id"] == SOURCE_BASE_CARD_ID:
        return new_base_card_id

    dataset_query = card_detail.get("dataset_query", {})

    # Rewrite structured queries that reference the old base card
    if dataset_query.get("type") == "query":
        query = dataset_query.get("query", {})
        _rewrite_source_table(query, new_base_card_id)

    new_card_payload = {
        "name": card_detail["name"],
        "description": card_detail.get("description"),
        "dataset_query": dataset_query,
        "display": card_detail.get("display", "table"),
        "visualization_settings": card_detail.get("visualization_settings", {}),
    }
    if collection_id:
        new_card_payload["collection_id"] = collection_id

    result = api_post("/card", new_card_payload)
    new_id = result["id"]
    print(f"  Replicated card '{card_detail['name']}' → new card {new_id}")
    return new_id


def create_replica_dashboard(
    source_dashboard: dict,
    new_base_card_id: int,
    dashboard_name: str,
    collection_id: int | None = None,
) -> int:
    """Create a new dashboard with all cards replicated from the source."""

    dashboard_payload = {
        "name": dashboard_name,
        "parameters": source_dashboard.get("parameters", []),
    }
    if collection_id:
        dashboard_payload["collection_id"] = collection_id

    dashboard = api_post("/dashboard", dashboard_payload)
    dashboard_id = dashboard["id"]
    print(f"\nCreated replica dashboard: ID {dashboard_id}")

    # Replicate each card and add to dashboard
    dashcards = []
    old_to_new_card = {}

    for dashcard in source_dashboard.get("dashcards", []):
        card_info = dashcard.get("card", {})
        if not card_info or not card_info.get("id"):
            # Text/heading cards — copy as-is
            dashcards.append(
                {
                    "id": dashcard.get("id"),
                    "card_id": None,
                    "row": dashcard.get("row", 0),
                    "col": dashcard.get("col", 0),
                    "size_x": dashcard.get("size_x", 4),
                    "size_y": dashcard.get("size_y", 4),
                    "visualization_settings": dashcard.get(
                        "visualization_settings", {}
                    ),
                    "parameter_mappings": [],
                }
            )
            continue

        source_card_id = card_info["id"]

        # Deduplicate: reuse if we already replicated this card
        if source_card_id in old_to_new_card:
            new_card_id = old_to_new_card[source_card_id]
        else:
            new_card_id = replicate_card(
                source_card_id, new_base_card_id, collection_id
            )
            old_to_new_card[source_card_id] = new_card_id

        # Rebuild parameter mappings with the new card ID
        param_mappings = []
        for pm in dashcard.get("parameter_mappings", []):
            new_pm = dict(pm)
            new_pm["card_id"] = new_card_id
            param_mappings.append(new_pm)

        dashcards.append(
            {
                "id": dashcard.get("id"),
                "card_id": new_card_id,
                "row": dashcard.get("row", 0),
                "col": dashcard.get("col", 0),
                "size_x": dashcard.get("size_x", 4),
                "size_y": dashcard.get("size_y", 4),
                "series": dashcard.get("series", []),
                "visualization_settings": dashcard.get(
                    "visualization_settings", {}
                ),
                "parameter_mappings": param_mappings,
            }
        )

    api_put(f"/dashboard/{dashboard_id}", {"dashcards": dashcards})
    print(f"  Added {len(dashcards)} cards to dashboard")

    return dashboard_id


# ---------------------------------------------------------------------------
# Step 3: Create a comparison dashboard from SQL files
# ---------------------------------------------------------------------------

# Define comparison cards: each entry maps a SQL file to a card name and chart type.
# Customize this list for your project.
COMPARISON_CARDS: list[dict] = [
    # Example:
    # {
    #     "file": "summary_kpis.sql",
    #     "name": "Summary KPIs: Model A vs Model B",
    #     "display": "table",  # bar, line, table, pie, scalar
    #     "size_x": 18,
    #     "size_y": 6,
    # },
]


def create_comparison_card(
    card_def: dict,
    collection_id: int | None = None,
) -> int:
    """Create a native SQL comparison card."""
    sql = (COMPARISON_QUERIES_DIR / card_def["file"]).read_text()

    payload = {
        "name": card_def["name"],
        "dataset_query": {
            "type": "native",
            "native": {"query": sql},
            "database": DATABASE_ID,
        },
        "display": card_def["display"],
        "visualization_settings": {},
    }
    if collection_id:
        payload["collection_id"] = collection_id

    result = api_post("/card", payload)
    card_id = result["id"]
    print(f"  Created comparison card '{card_def['name']}' → card {card_id}")
    return card_id


def create_comparison_dashboard(
    dashboard_name: str,
    collection_id: int | None = None,
) -> int:
    """Create a comparison dashboard with native SQL cards laid out vertically."""

    if not COMPARISON_CARDS:
        print("  No comparison cards defined — skipping comparison dashboard")
        return -1

    dashboard_payload = {"name": dashboard_name}
    if collection_id:
        dashboard_payload["collection_id"] = collection_id

    dashboard = api_post("/dashboard", dashboard_payload)
    dashboard_id = dashboard["id"]
    print(f"\nCreated comparison dashboard: ID {dashboard_id}")

    dashcards = []
    current_row = 0

    for card_def in COMPARISON_CARDS:
        card_id = create_comparison_card(card_def, collection_id)

        dashcards.append(
            {
                "id": -(len(dashcards) + 1),  # negative ID = new dashcard
                "card_id": card_id,
                "row": current_row,
                "col": 0,
                "size_x": card_def["size_x"],
                "size_y": card_def["size_y"],
                "visualization_settings": {},
                "parameter_mappings": [],
            }
        )
        current_row += card_def["size_y"]

    api_put(f"/dashboard/{dashboard_id}", {"dashcards": dashcards})
    print(f"  Added {len(dashcards)} cards to comparison dashboard")

    return dashboard_id


# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------


def main() -> None:
    if not METABASE_URL or not METABASE_API_KEY:
        print(
            "ERROR: METABASE_URL and METABASE_API_KEY must be set in .env",
            file=sys.stderr,
        )
        sys.exit(1)

    print(f"Metabase: {METABASE_URL}")
    print(f"Source Dashboard ID: {SOURCE_DASHBOARD_ID}")
    print()

    # Fetch source dashboard
    print("Fetching source dashboard...")
    source_dashboard = api_get(f"/dashboard/{SOURCE_DASHBOARD_ID}")
    print(f"  Found {len(source_dashboard.get('dashcards', []))} cards")

    # Step 1: Create new base question
    if BASE_QUERY_SQL_PATH.exists():
        print("\nStep 1: Creating new base question...")
        base_card_id = create_base_question(
            name="New Base Question",
            sql_path=BASE_QUERY_SQL_PATH,
            collection_id=TARGET_COLLECTION_ID,
        )
    else:
        print(f"\nSkipping base question creation ({BASE_QUERY_SQL_PATH} not found)")
        print("Set SOURCE_BASE_CARD_ID to an existing base card and re-run.")
        sys.exit(1)

    # Step 2: Create replica dashboard
    print("\nStep 2: Creating replica dashboard...")
    replica_id = create_replica_dashboard(
        source_dashboard,
        base_card_id,
        dashboard_name=f"{source_dashboard['name']} (Replica)",
        collection_id=TARGET_COLLECTION_ID,
    )

    # Step 3: Create comparison dashboard (if cards are defined)
    comparison_id = None
    if COMPARISON_CARDS and COMPARISON_QUERIES_DIR.exists():
        print("\nStep 3: Creating comparison dashboard...")
        comparison_id = create_comparison_dashboard(
            dashboard_name="Model Comparison",
            collection_id=TARGET_COLLECTION_ID,
        )

    # Summary
    print("\n" + "=" * 60)
    print("DONE!")
    print(f"  Base question:         {METABASE_URL}/question/{base_card_id}")
    print(f"  Replica dashboard:     {METABASE_URL}/dashboard/{replica_id}")
    if comparison_id and comparison_id > 0:
        print(f"  Comparison dashboard:  {METABASE_URL}/dashboard/{comparison_id}")
    print("=" * 60)

    # Save IDs for the screenshot script
    ids_file = PROJECT_ROOT / "metabase" / ".dashboard_ids.json"
    ids = {
        "base_card_id": base_card_id,
        "source_dashboard_id": SOURCE_DASHBOARD_ID,
        "replica_dashboard_id": replica_id,
    }
    if comparison_id and comparison_id > 0:
        ids["comparison_dashboard_id"] = comparison_id

    ids_file.write_text(json.dumps(ids, indent=2))
    print(f"\n  Dashboard IDs saved to {ids_file}")


if __name__ == "__main__":
    main()