"""
Licensing tables + company license columns.

Run: python migrations/add_licensing_tables.py
"""
import os
import sys

sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))

from sqlalchemy import text
from app.core.database import engine
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


def column_exists(conn, table: str, column: str) -> bool:
    bind = engine.url.drivername
    if "sqlite" in bind:
        rows = conn.execute(text(f"PRAGMA table_info({table})")).fetchall()
        return any(r[1] == column for r in rows)
    result = conn.execute(
        text(
            "SELECT 1 FROM information_schema.columns "
            "WHERE table_name = :t AND column_name = :c"
        ),
        {"t": table, "c": column},
    )
    return result.fetchone() is not None


def run_migration():
    with engine.begin() as conn:
        logger.info("Creating license_keys table…")
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS license_keys (
                id VARCHAR(36) PRIMARY KEY,
                key_prefix VARCHAR(32) NOT NULL,
                key_hash VARCHAR(64) NOT NULL UNIQUE,
                plan VARCHAR(50) DEFAULT 'standard',
                deployment_mode VARCHAR(20) DEFAULT 'both',
                status VARCHAR(20) DEFAULT 'pending',
                valid_from TIMESTAMP NOT NULL,
                valid_until TIMESTAMP NOT NULL,
                max_users INTEGER DEFAULT 25,
                max_branches INTEGER DEFAULT 10,
                grace_days INTEGER DEFAULT 7,
                company_id VARCHAR(36) REFERENCES companies(id),
                activated_at TIMESTAMP,
                activated_by_email VARCHAR(255),
                customer_name VARCHAR(255),
                customer_email VARCHAR(255),
                notes TEXT,
                renewals_count INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT NOW(),
                updated_at TIMESTAMP DEFAULT NOW()
            )
        """))

        logger.info("Creating deployment_nodes table…")
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS deployment_nodes (
                id VARCHAR(36) PRIMARY KEY,
                license_key_id VARCHAR(36) NOT NULL REFERENCES license_keys(id),
                company_id VARCHAR(36) REFERENCES companies(id),
                node_name VARCHAR(255) NOT NULL,
                deployment_type VARCHAR(20) DEFAULT 'local',
                public_base_url VARCHAR(500),
                sync_token_hash VARCHAR(64),
                is_active BOOLEAN DEFAULT TRUE,
                last_heartbeat_at TIMESTAMP,
                last_ip VARCHAR(64),
                app_version VARCHAR(50),
                machine_fingerprint VARCHAR(128),
                created_at TIMESTAMP DEFAULT NOW(),
                updated_at TIMESTAMP DEFAULT NOW()
            )
        """))

        for col, ddl in [
            ("license_key_id", "VARCHAR(36) REFERENCES license_keys(id)"),
            ("license_expires_at", "TIMESTAMP"),
            ("deployment_mode", "VARCHAR(20) DEFAULT 'cloud'"),
        ]:
            if not column_exists(conn, "companies", col):
                logger.info("Adding companies.%s", col)
                conn.execute(text(f"ALTER TABLE companies ADD COLUMN {col} {ddl}"))

    logger.info("Licensing migration complete.")


if __name__ == "__main__":
    run_migration()
