from django.db import connections


CLUSTER_BOTS_EXTRA_COLUMNS = (
    # Columns expected by the bot API / legacy schemas; added if missing on older `{bid}_bots` tables.
    ("default_language", "VARCHAR(32) NULL"),
    ("stt_provider", "VARCHAR(50) NOT NULL DEFAULT 'deepgram'"),
    ("tts_provider", "VARCHAR(50) NOT NULL DEFAULT 'elevenlabs'"),
    ("llm_provider", "VARCHAR(255) NULL"),
    ("mcube_exenumber", "VARCHAR(64) NOT NULL DEFAULT ''"),
    ("mcube_gid", "VARCHAR(64) NOT NULL DEFAULT '1'"),
    ("message_inbound", "LONGTEXT NULL"),
    ("message_outbound", "LONGTEXT NULL"),
    ("guardrails", "JSON NULL"),
    ("system_tools", "JSON NULL"),
    ("filler_keywords", "LONGTEXT NULL"),
    ("conversation_behavior", "JSON NULL"),
    # Flattened from `platform_settings.features` (no longer stored inside platform_settings JSON)
    ("transfer_enabled", "TINYINT(1) NOT NULL DEFAULT 0"),
    ("skip_turn_prompt", "LONGTEXT NULL"),
    ("detect_language_prompt", "LONGTEXT NULL"),
    ("end_conversation_prompt", "LONGTEXT NULL"),
    ("transfer_to_number_prompt", "LONGTEXT NULL"),
    ("voicemail_detection_prompt", "LONGTEXT NULL"),
    ("skip_turn_description", "TEXT NULL"),
    ("detect_language_description", "TEXT NULL"),
    ("end_conversation_description", "TEXT NULL"),
    ("transfer_number_description", "TEXT NULL"),
    ("voicemail_description", "TEXT NULL"),
    ("end_conversation_timeout", "INT UNSIGNED NULL"),
    ("transfer_destination_type", "VARCHAR(32) NULL"),
    ("transfer_destinations", "JSON NULL"),
    ("skip_turn_disable_interruptions", "TINYINT(1) NOT NULL DEFAULT 0"),
    ("detect_language_disable_interruptions", "TINYINT(1) NOT NULL DEFAULT 0"),
    ("end_conversation_disable_interruptions", "TINYINT(1) NOT NULL DEFAULT 0"),
    ("transfer_to_number_disable_interruptions", "TINYINT(1) NOT NULL DEFAULT 0"),
    ("voicemail_detection_disable_interruptions", "TINYINT(1) NOT NULL DEFAULT 0"),
    # LiveKit Cloud deployment mapping (explicit, for easy identification)
    ("agent_name", "VARCHAR(255) NULL"),
)


def _q_ident(name: str) -> str:
    # MySQL identifier quoting with minimal safety.
    return f"`{name.replace('`', '``')}`"

def _table_names(bid: int) -> tuple[str, str, str, str]:
    """
    Returns:
      agents_table, bots_table, call_history_table, legacy_callhistory_table
    """
    agents = f"{bid}_agents"
    bots = f"{bid}_bots"
    call_history = f"{bid}_call_history"
    legacy_callhistory = f"{bid}_callhistory"
    return agents, bots, call_history, legacy_callhistory


def drop_business_cluster_tables(business_id: int) -> None:
    """
    Drop per-business tables in the cluster DB (if they exist).
    Also drops the legacy `{bid}_callhistory` table name.
    """
    if business_id is None:
        return
    bid = int(business_id)
    if bid <= 0:
        return

    agents, bots, call_history, legacy_callhistory = _table_names(bid)
    conn = connections["cluster"]
    with conn.cursor() as cursor:
        cursor.execute(f"DROP TABLE IF EXISTS {_q_ident(agents)};")
        cursor.execute(f"DROP TABLE IF EXISTS {_q_ident(bots)};")
        cursor.execute(f"DROP TABLE IF EXISTS {_q_ident(call_history)};")
        cursor.execute(f"DROP TABLE IF EXISTS {_q_ident(legacy_callhistory)};")


def ensure_business_cluster_tables(business_id: int) -> None:
    """
    Create per-business tables in the `cluster` database:
      {business_id}_agents
      {business_id}_bots
      {business_id}_call_history
      {business_id}_knowledgebase
      {business_id}_audit_logs
      {business_id}_campaigns
      {business_id}_campaign_contacts

    These tables are created on demand when a Business is created in master DB.
    """
    if business_id is None:
        return
    bid = int(business_id)
    if bid <= 0:
        return

    agents, bots, calls, _legacy_callhistory = _table_names(bid)
    knowledgebase = f"{bid}_knowledgebase"
    audit_logs = f"{bid}_audit_logs"
    campaigns = f"{bid}_campaigns"
    campaign_contacts = f"{bid}_campaign_contacts"

    # Note: table names start with digits; MySQL requires quoting, so we always use backticks.
    ddl_agents = f"""
    CREATE TABLE IF NOT EXISTS {_q_ident(agents)} (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      name VARCHAR(255) NOT NULL DEFAULT '',
      email VARCHAR(255) NOT NULL DEFAULT '',
      email_verified_at DATETIME NULL,
      password VARCHAR(255) NOT NULL DEFAULT '',
      role VARCHAR(50) NOT NULL DEFAULT 'agent',
      phone VARCHAR(64) NOT NULL DEFAULT '',
      department VARCHAR(255) NOT NULL DEFAULT '',
      status VARCHAR(50) NOT NULL DEFAULT '',
      reports_to BIGINT UNSIGNED NULL,
      last_login DATETIME NULL,
      remember_token VARCHAR(255) NULL,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      business_id BIGINT UNSIGNED NOT NULL,
      permissions JSON NULL,
      PRIMARY KEY (id),
      KEY idx_business_id (business_id),
      KEY idx_email (email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """

    ddl_bots = f"""
    CREATE TABLE IF NOT EXISTS {_q_ident(bots)} (
      bot_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      -- LiveKit Cloud agent IDs look like "sb_..." (string), so store as varchar.
      agent_id VARCHAR(128) NULL,
      agent_name VARCHAR(255) NULL,
      is_active TINYINT(1) NOT NULL DEFAULT 1,
      voice_name VARCHAR(255) NOT NULL DEFAULT '',
      llm_model VARCHAR(255) NOT NULL DEFAULT '',
      prompt LONGTEXT NULL,
      business_id BIGINT UNSIGNED NOT NULL,
      bot_name VARCHAR(255) NOT NULL DEFAULT '',
      default_language VARCHAR(32) NULL,
      platform_settings JSON NULL,
      concurrent_calls INT UNSIGNED NULL,
      voice JSON NULL,
      advanced_settings JSON NULL,
      conversation_behavior JSON NULL,
      extraction JSON NULL,
      detect_language TINYINT(1) NOT NULL DEFAULT 1,
      skip_turn TINYINT(1) NOT NULL DEFAULT 0,
      voicemail_detection TINYINT(1) NOT NULL DEFAULT 0,
      transfer_to_number VARCHAR(64) NULL,
      end_conversation TINYINT(1) NOT NULL DEFAULT 0,
      filler_keywords LONGTEXT NULL,
      transfer_enabled TINYINT(1) NOT NULL DEFAULT 0,
      skip_turn_prompt LONGTEXT NULL,
      detect_language_prompt LONGTEXT NULL,
      end_conversation_prompt LONGTEXT NULL,
      transfer_to_number_prompt LONGTEXT NULL,
      voicemail_detection_prompt LONGTEXT NULL,
      skip_turn_description TEXT NULL,
      detect_language_description TEXT NULL,
      end_conversation_description TEXT NULL,
      transfer_number_description TEXT NULL,
      voicemail_description TEXT NULL,
      end_conversation_timeout INT UNSIGNED NULL,
      transfer_destination_type VARCHAR(32) NULL,
      transfer_destinations JSON NULL,
      skip_turn_disable_interruptions TINYINT(1) NOT NULL DEFAULT 0,
      detect_language_disable_interruptions TINYINT(1) NOT NULL DEFAULT 0,
      end_conversation_disable_interruptions TINYINT(1) NOT NULL DEFAULT 0,
      transfer_to_number_disable_interruptions TINYINT(1) NOT NULL DEFAULT 0,
      voicemail_detection_disable_interruptions TINYINT(1) NOT NULL DEFAULT 0,
      stt_provider VARCHAR(50) NOT NULL DEFAULT 'deepgram',
      tts_provider VARCHAR(50) NOT NULL DEFAULT 'elevenlabs',
      llm_provider VARCHAR(255) NULL,
      mcube_exenumber VARCHAR(64) NOT NULL DEFAULT '',
      mcube_gid VARCHAR(64) NOT NULL DEFAULT '1',
      message_inbound LONGTEXT NULL,
      message_outbound LONGTEXT NULL,
      guardrails JSON NULL,
      system_tools JSON NULL,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (bot_id),
      KEY idx_business_id (business_id),
      KEY idx_agent_id (agent_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """

    ddl_calls = f"""
    CREATE TABLE IF NOT EXISTS {_q_ident(calls)} (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      conversation_id VARCHAR(255) NULL,
      agent_id VARCHAR(128) NULL,
      transcript LONGTEXT NULL,
      conversation_data JSON NULL,
      phone_number VARCHAR(64) NULL,
      agent_phone_number VARCHAR(64) NULL,
      customer_name VARCHAR(255) NULL,
      agent_name VARCHAR(255) NULL,
      call_start_time DATETIME NULL,
      call_end_time DATETIME NULL,
      call_duration_secs INT UNSIGNED NULL,
      call_successful TINYINT(1) NULL,
      call_status VARCHAR(50) NULL,
      ongoing TINYINT(1) NOT NULL DEFAULT 0,
      transferred TINYINT(1) NOT NULL DEFAULT 0,
      status VARCHAR(50) NOT NULL DEFAULT '',
      sentiment VARCHAR(50) NULL,
      summary LONGTEXT NULL,
      recording_url LONGTEXT NULL,
      dynamic_variables JSON NULL,
      campaign_id BIGINT UNSIGNED NULL,
      business_id BIGINT UNSIGNED NOT NULL,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_business_id (business_id),
      KEY idx_conversation_id (conversation_id),
      KEY idx_agent_id (agent_id),
      KEY idx_campaign_id (campaign_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """

    ddl_knowledgebase = f"""
    CREATE TABLE IF NOT EXISTS {_q_ident(knowledgebase)} (
      id INT NOT NULL AUTO_INCREMENT,
      doc_id VARCHAR(255) NULL,
      doc_name VARCHAR(255) NULL,
      doc_url LONGTEXT NOT NULL,
      bot_id INT NULL,
      business_id INT NULL,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_business_id (business_id),
      KEY idx_bot_id (bot_id),
      KEY idx_doc_id (doc_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """

    ddl_audit_logs = f"""
    CREATE TABLE IF NOT EXISTS {_q_ident(audit_logs)} (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      timestamp DATETIME NULL,
      user_id VARCHAR(64) NOT NULL DEFAULT '',
      user_name VARCHAR(255) NOT NULL DEFAULT '',
      user_role VARCHAR(64) NOT NULL DEFAULT '',
      action VARCHAR(64) NOT NULL DEFAULT '',
      module VARCHAR(64) NOT NULL DEFAULT '',
      resource_type VARCHAR(128) NOT NULL DEFAULT '',
      resource_name VARCHAR(255) NULL,
      description TEXT NULL,
      severity VARCHAR(16) NOT NULL DEFAULT 'low',
      status VARCHAR(16) NOT NULL DEFAULT 'success',
      ip_address VARCHAR(64) NULL,
      user_agent TEXT NULL,
      business_id BIGINT UNSIGNED NOT NULL,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_business_id (business_id),
      KEY idx_timestamp (timestamp)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """

    ddl_campaigns = f"""
    CREATE TABLE IF NOT EXISTS {_q_ident(campaigns)} (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      name VARCHAR(255) NOT NULL DEFAULT '',
      description TEXT NULL,
      autodailer_id BIGINT UNSIGNED NULL,
      business_id BIGINT UNSIGNED NOT NULL,
      status VARCHAR(32) NOT NULL DEFAULT 'processing',
      call_status VARCHAR(32) NOT NULL DEFAULT 'pending',
      listname VARCHAR(255) NOT NULL DEFAULT '',
      total_count INT UNSIGNED NOT NULL DEFAULT 0,
      total_calls INT UNSIGNED NOT NULL DEFAULT 0,
      successful_calls INT UNSIGNED NOT NULL DEFAULT 0,
      failed_calls INT UNSIGNED NOT NULL DEFAULT 0,
      processing_count INT UNSIGNED NOT NULL DEFAULT 0,
      pending_count INT UNSIGNED NOT NULL DEFAULT 0,
      waiting_time INT UNSIGNED NOT NULL DEFAULT 0,
      waiting_time_count INT UNSIGNED NOT NULL DEFAULT 0,
      createdon DATETIME NULL,
      start_time DATETIME NULL,
      end_time DATETIME NULL,
      did VARCHAR(64) NOT NULL DEFAULT '',
      start_date DATE NULL,
      end_date DATE NULL,
      autodailer_type VARCHAR(64) NOT NULL DEFAULT '',
      retry_attempt INT UNSIGNED NOT NULL DEFAULT 0,
      retry_time INT UNSIGNED NOT NULL DEFAULT 0,
      bot_name VARCHAR(255) NOT NULL DEFAULT '',
      exeuctive_number VARCHAR(64) NOT NULL DEFAULT '',
      refurl LONGTEXT NULL,
      run_now TINYINT(1) NOT NULL DEFAULT 0,
      batch_size INT UNSIGNED NULL,
      batch_interval INT UNSIGNED NULL,
      no_of_attempt INT UNSIGNED NULL,
      total_attempts INT UNSIGNED NULL,
      interval_time INT UNSIGNED NULL,
      automated_callbacks_enabled TINYINT(1) NOT NULL DEFAULT 0,
      pending_callbacks_count INT UNSIGNED NOT NULL DEFAULT 0,
      callback_requested TINYINT(1) NOT NULL DEFAULT 0,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_business_id (business_id),
      KEY idx_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """

    ddl_campaign_contacts = f"""
    CREATE TABLE IF NOT EXISTS {_q_ident(campaign_contacts)} (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      business_id BIGINT UNSIGNED NOT NULL,
      campaign_id BIGINT UNSIGNED NOT NULL,
      status VARCHAR(32) NOT NULL DEFAULT 'pending',
      call_status VARCHAR(32) NOT NULL DEFAULT 'pending',
      contact_name VARCHAR(255) NOT NULL DEFAULT '',
      number VARCHAR(64) NOT NULL DEFAULT '',
      source VARCHAR(255) NOT NULL DEFAULT '',
      retry TINYINT(1) NOT NULL DEFAULT 0,
      retry_attempt INT UNSIGNED NOT NULL DEFAULT 0,
      callback_requested TINYINT(1) NOT NULL DEFAULT 0,
      callback_time DATETIME NULL,
      callback_status TINYINT(1) NOT NULL DEFAULT 0,
      callback_retry_attempts INT UNSIGNED NOT NULL DEFAULT 0,
      callback_last_attempt_time DATETIME NULL,
      callback_failure_reason TEXT NULL,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (id),
      KEY idx_business_id (business_id),
      KEY idx_campaign_id (campaign_id),
      KEY idx_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """

    conn = connections["cluster"]
    with conn.cursor() as cursor:
        cursor.execute(ddl_agents)
        cursor.execute(ddl_bots)
        cursor.execute(ddl_calls)
        cursor.execute(ddl_knowledgebase)
        cursor.execute(ddl_audit_logs)
        cursor.execute(ddl_campaigns)
        _ensure_campaigns_table_columns(cursor, campaigns)
        cursor.execute(ddl_campaign_contacts)
        _ensure_bots_table_columns(cursor, bots)
        _ensure_agent_id_varchar(cursor, bots)
        _ensure_agent_id_varchar(cursor, calls)
        _ensure_knowledgebase_timestamps(cursor, knowledgebase)


def _ensure_campaigns_table_columns(cursor, campaigns: str) -> None:
    """
    Legacy `{bid}_campaigns` tables (e.g. Laravel) often lack `bot_id`.
    Add it so the UI-selected cluster bot PK persists for MCube `/outbound-call/{bot_id}`.
    """
    cursor.execute("SELECT DATABASE()")
    row = cursor.fetchone()
    if not row or not row[0]:
        return
    db_name = row[0]
    cursor.execute(
        """
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'bot_id'
        """,
        [db_name, campaigns],
    )
    if (cursor.fetchone() or [0])[0]:
        return
    cursor.execute(
        """
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
        """,
        [db_name, campaigns],
    )
    if not (cursor.fetchone() or [0])[0]:
        return
    try:
        cursor.execute(
            f"ALTER TABLE {_q_ident(campaigns)} "
            f"ADD COLUMN {_q_ident('bot_id')} BIGINT UNSIGNED NULL"
        )
    except Exception:
        pass


def _ensure_knowledgebase_timestamps(cursor, table: str) -> None:
    """Legacy `{bid}_knowledgebase` tables may define created_at/updated_at without DEFAULT."""
    cursor.execute("SELECT DATABASE()")
    row = cursor.fetchone()
    if not row or not row[0]:
        return
    db_name = row[0]
    fixes = (
        ("created_at", "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"),
        (
            "updated_at",
            "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
        ),
    )
    for col_name, col_def in fixes:
        cursor.execute(
            """
            SELECT COLUMN_DEFAULT
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s
            """,
            [db_name, table, col_name],
        )
        r = cursor.fetchone()
        if not r:
            continue
        if r[0] is not None:
            continue
        try:
            cursor.execute(
                f"ALTER TABLE {_q_ident(table)} MODIFY COLUMN {_q_ident(col_name)} {col_def}"
            )
        except Exception:
            pass


def _ensure_bots_table_columns(cursor, table: str) -> None:
    """ALTER `{bid}_bots` to add API/legacy columns missing on older deployments."""
    cursor.execute("SELECT DATABASE()")
    row = cursor.fetchone()
    if not row or not row[0]:
        return
    db_name = row[0]
    cursor.execute(
        """
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
        """,
        [db_name, table],
    )
    existing = {r[0] for r in cursor.fetchall()}
    for col_name, col_def in CLUSTER_BOTS_EXTRA_COLUMNS:
        if col_name in existing:
            continue
        try:
            cursor.execute(
                f"ALTER TABLE {_q_ident(table)} ADD COLUMN {_q_ident(col_name)} {col_def}"
            )
        except Exception:
            # Duplicate / permission / incompatible engine: skip so requests can still proceed.
            pass


def _ensure_agent_id_varchar(cursor, table: str) -> None:
    """
    Older deployments created agent_id as BIGINT; migrate to VARCHAR(128) so we can store
    LiveKit Cloud agent IDs like "sb_...".
    """
    cursor.execute("SELECT DATABASE()")
    row = cursor.fetchone()
    if not row or not row[0]:
        return
    db_name = row[0]
    cursor.execute(
        """
        SELECT DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'agent_id'
        LIMIT 1
        """,
        [db_name, table],
    )
    r = cursor.fetchone()
    if not r:
        return
    data_type = (r[0] or "").lower()
    if data_type in ("varchar", "char", "text", "longtext"):
        return
    try:
        cursor.execute(
            f"ALTER TABLE {_q_ident(table)} MODIFY COLUMN agent_id VARCHAR(128) NULL"
        )
    except Exception:
        return
    try:
        cursor.execute(f"SHOW INDEX FROM {_q_ident(table)} WHERE Key_name = 'idx_agent_id'")
        idx = cursor.fetchone()
        if not idx:
            cursor.execute(f"CREATE INDEX idx_agent_id ON {_q_ident(table)} (agent_id)")
    except Exception:
        pass