import os
import re

import dataiku
from backend.sqlite_compat import sqlite3

_WEBAPP_TYPE_PREFIX = "webapp_agent-hub"
_WEBAPP_TYPE_LIGHT_SUFFIX = "light"
_PREFIX_RE = re.compile(r"^[a-zA-Z][a-zA-Z0-9_]*$")


def is_agent_chat_webapp_type(webapp_type):
    return webapp_type.startswith(_WEBAPP_TYPE_PREFIX) and webapp_type.endswith(_WEBAPP_TYPE_LIGHT_SUFFIX)


def normalize_tables_prefix(prefix):
    prefix = (prefix or "").strip().lower()
    if not prefix:
        return ""
    if not _PREFIX_RE.match(prefix):
        raise ValueError(f"Invalid tables_prefix '{prefix}': only alphanumeric characters and underscores are allowed")
    if not prefix.endswith("_"):
        prefix = f"{prefix}_"
    return prefix


def get_effective_tables_prefix(prefix):
    return f"{normalize_tables_prefix(prefix)}asw"


def get_physical_table_name(logical_table_name, effective_tables_prefix):
    return f"{effective_tables_prefix}{logical_table_name}"


def extract_webapp_params(settings):
    raw = settings.get_raw() if hasattr(settings, "get_raw") else {}
    if isinstance(raw, dict):
        params = raw.get("params")
        if isinstance(params, dict):
            return params
        nested_settings = raw.get("settings")
        if isinstance(nested_settings, dict) and isinstance(nested_settings.get("params"), dict):
            return nested_settings["params"]
        return raw
    params = getattr(settings, "params", None)
    if isinstance(params, dict):
        return params
    return {}


def resolve_agent_chat_context(project_key, webapp_id):
    client = dataiku.api_client()
    project = client.get_project(project_key)
    webapps = {webapp["id"]: webapp for webapp in project.list_webapps()}
    webapp_metadata = webapps.get(webapp_id)

    if not webapp_metadata:
        raise ValueError(f"Webapp '{webapp_id}' was not found in project '{project_key}'")

    webapp_type = webapp_metadata.get("type", "")
    if not is_agent_chat_webapp_type(webapp_type):
        raise ValueError(f"Selected webapp '{webapp_metadata.get('name', webapp_id)}' is not an Agent Chat webapp.")

    webapp = project.get_webapp(webapp_id)
    params = extract_webapp_params(webapp.get_settings())
    storage_type = str(params.get("storage_type", "LOCAL")).upper()

    if storage_type != "LOCAL":
        raise ValueError("Export Agent Chat Data only supports webapps using Built-in (SQLite) storage.")

    configured_tables_prefix = params.get("tables_prefix", "") or ""
    effective_tables_prefix = get_effective_tables_prefix(configured_tables_prefix)

    dip_home = os.environ.get("DIP_HOME")
    if not dip_home:
        raise ValueError("DIP_HOME environment variable not found")

    workload_folder = os.path.join(dip_home, "workload-folders", "webapps", project_key, webapp_id)
    if not os.path.exists(workload_folder):
        raise ValueError(f"Webapp workload folder not found: {workload_folder}")

    db_path = os.path.join(workload_folder, "data_store.db")
    if not os.path.exists(db_path):
        raise ValueError(f"Database file not found: {db_path}")

    return {
        "project_key": project_key,
        "webapp_id": webapp_id,
        "webapp_name": webapp_metadata.get("name", webapp_id),
        "workload_folder": workload_folder,
        "db_path": db_path,
        "effective_tables_prefix": effective_tables_prefix,
    }


def validate_physical_tables_exist(db_path, logical_tables, effective_tables_prefix):
    physical_tables = {
        get_physical_table_name(table_name, effective_tables_prefix): table_name for table_name in logical_tables
    }

    with sqlite3.connect(db_path) as connection:
        cursor = connection.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
        existing_tables = {row[0] for row in cursor.fetchall()}

    missing_tables = [
        f"{logical_name} -> {physical_name}"
        for physical_name, logical_name in physical_tables.items()
        if physical_name not in existing_tables
    ]
    if missing_tables:
        raise ValueError(
            "Selected Agent Chat table(s) not found in database:\n"
            + "\n".join(f"  - {mapping}" for mapping in missing_tables)
        )
