from __future__ import annotations

import sqlite3
from abc import ABC, abstractmethod
from pathlib import Path
from typing import Any, Optional, Tuple

from app.models import SqlExecutionResult


class DataSource(ABC):
    """Pluggable backend: BigQuery, Redshift, Snowflake implement the same contract."""

    dialect: str = "generic"

    @abstractmethod
    def execute(self, sql: str, params: Optional[Tuple[Any, ...]] = None) -> SqlExecutionResult:
        raise NotImplementedError

    def qualify_table(self, physical_name: str) -> str:
        return physical_name


class SqliteDataSource(DataSource):
    dialect = "sqlite"

    def __init__(self, db_path: Path, max_rows: int = 500):
        self.db_path = db_path
        self.max_rows = max_rows

    def execute(self, sql: str, params: Optional[Tuple[Any, ...]] = None) -> SqlExecutionResult:
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cur = conn.cursor()
        cur.execute(sql, params or ())
        columns = [d[0] for d in cur.description] if cur.description else []
        rows_raw = cur.fetchmany(self.max_rows + 1)
        truncated = len(rows_raw) > self.max_rows
        rows = [list(r) for r in rows_raw[: self.max_rows]]
        conn.close()
        return SqlExecutionResult(
            sql=sql,
            columns=columns,
            rows=rows,
            row_count=len(rows),
            truncated=truncated,
        )


class BigQueryStub(DataSource):
    """Placeholder showing how a cloud adapter would plug in."""

    dialect = "bigquery"

    def qualify_table(self, physical_name: str) -> str:
        return f"`project.dataset.{physical_name}`"

    def execute(self, sql: str, params: Optional[Tuple[Any, ...]] = None) -> SqlExecutionResult:
        raise NotImplementedError("Wire google-cloud-bigquery and job config here.")


class SnowflakeStub(DataSource):
    dialect = "snowflake"

    def qualify_table(self, physical_name: str) -> str:
        return f"analytics.public.{physical_name.upper()}"

    def execute(self, sql: str, params: Optional[Tuple[Any, ...]] = None) -> SqlExecutionResult:
        raise NotImplementedError("Wire snowflake-connector-python here.")