"""
Multi-tenancy Migration: Company Table
======================================
Adds the `companies` table as the top-level tenant entity and links
existing branches and users to a default company so the system remains
fully operational after migration.

Changes:
  1. Create `companies` table
  2. Add `company_id` (nullable FK) to `branches`
  3. Add `company_id` (nullable FK) to `users`
  4. Insert a default company seeded from the first AppSetting row (if any)
  5. Assign all existing branches and users to the default company

Safe to run multiple times (all DDL uses IF NOT EXISTS / column-exists guards).

Run with:
    python migrations/add_company_multitenancy.py
"""
import sys
import os
import uuid
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:
    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:

        # ── 1. Create companies table ─────────────────────────────────────
        logger.info("Creating companies table...")
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS companies (
                id                          VARCHAR(36)     PRIMARY KEY,
                name                        VARCHAR(255)    NOT NULL,
                legal_name                  VARCHAR(255),
                company_code                VARCHAR(20)     UNIQUE,
                registration_number         VARCHAR(100),
                tax_identification_number   VARCHAR(100),
                vat_registration_number     VARCHAR(100),
                industry                    VARCHAR(100),
                address_line1               VARCHAR(255),
                address_line2               VARCHAR(255),
                city                        VARCHAR(100),
                state                       VARCHAR(100),
                country                     VARCHAR(100)    DEFAULT 'Botswana',
                postal_code                 VARCHAR(20),
                phone                       VARCHAR(50),
                phone2                      VARCHAR(50),
                fax                         VARCHAR(50),
                email                       VARCHAR(255),
                website                     VARCHAR(255),
                logo_url                    VARCHAR(500),
                primary_color               VARCHAR(10)     DEFAULT '#0d6efd',
                currency                    VARCHAR(10)     DEFAULT 'BWP',
                default_vat_rate            NUMERIC(5,2)    DEFAULT 14.0,
                fiscal_year_start_month     INTEGER         DEFAULT 1,
                timezone                    VARCHAR(50)     DEFAULT 'Africa/Gaborone',
                invoice_footer              TEXT,
                receipt_footer              TEXT,
                po_terms                    TEXT,
                is_active                   BOOLEAN         DEFAULT TRUE,
                is_setup_complete           BOOLEAN         DEFAULT TRUE,
                setup_completed_at          TIMESTAMP,
                subscription_plan           VARCHAR(50)     DEFAULT 'standard',
                created_at                  TIMESTAMP       DEFAULT NOW(),
                updated_at                  TIMESTAMP       DEFAULT NOW()
            )
        """))

        # ── 2. Add company_id to branches ─────────────────────────────────
        if not column_exists(conn, "branches", "company_id"):
            logger.info("Adding company_id to branches...")
            conn.execute(text("""
                ALTER TABLE branches
                ADD COLUMN company_id VARCHAR(36) REFERENCES companies(id) ON DELETE SET NULL
            """))
            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS ix_branches_company_id ON branches (company_id)
            """))
        else:
            logger.info("branches.company_id already exists — skipping")

        # ── 3. Add company_id to users ────────────────────────────────────
        if not column_exists(conn, "users", "company_id"):
            logger.info("Adding company_id to users...")
            conn.execute(text("""
                ALTER TABLE users
                ADD COLUMN company_id VARCHAR(36) REFERENCES companies(id) ON DELETE SET NULL
            """))
            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS ix_users_company_id ON users (company_id)
            """))
        else:
            logger.info("users.company_id already exists — skipping")

        # ── 4. Seed default company from existing data ────────────────────
        existing_company = conn.execute(text("SELECT id FROM companies LIMIT 1")).fetchone()
        if existing_company:
            default_company_id = existing_company[0]
            logger.info(f"Default company already exists: {default_company_id}")
        else:
            default_company_id = str(uuid.uuid4())
            logger.info(f"Seeding default company (id={default_company_id})...")

            # Try to pull details from AppSetting if it exists
            company_name = "Default Company"
            vat_reg      = ""
            logo_url     = ""
            currency     = "BWP"
            try:
                row = conn.execute(text(
                    "SELECT company_name, vat_registration_number, company_logo_url, currency "
                    "FROM app_settings LIMIT 1"
                )).fetchone()
                if row:
                    company_name = row[0] or company_name
                    vat_reg      = row[1] or ""
                    logo_url     = row[2] or ""
                    currency     = row[3] or "BWP"
            except Exception:
                pass  # app_settings may not exist yet

            # Generate a safe company_code from the name
            import re
            code_base = re.sub(r"[^A-Z0-9]", "", company_name.upper())[:6] or "DFLT"
            company_code = code_base

            conn.execute(text("""
                INSERT INTO companies
                    (id, name, company_code, vat_registration_number,
                     logo_url, currency, is_active, is_setup_complete,
                     setup_completed_at, created_at, updated_at)
                VALUES
                    (:id, :name, :code, :vat,
                     :logo, :currency, TRUE, TRUE,
                     NOW(), NOW(), NOW())
            """), {
                "id":       default_company_id,
                "name":     company_name,
                "code":     company_code,
                "vat":      vat_reg,
                "logo":     logo_url,
                "currency": currency,
            })

        # ── 5. Assign all branches to the default company ─────────────────
        logger.info("Linking existing branches to default company...")
        conn.execute(text("""
            UPDATE branches
            SET company_id = :cid
            WHERE company_id IS NULL
        """), {"cid": default_company_id})

        # ── 6. Assign all users to the default company ────────────────────
        logger.info("Linking existing users to default company...")
        conn.execute(text("""
            UPDATE users
            SET company_id = :cid
            WHERE company_id IS NULL
        """), {"cid": default_company_id})

        logger.info("Migration complete: multi-tenancy scaffolding is in place.")
        logger.info(f"Default company ID: {default_company_id}")
        logger.info("All existing branches and users are now linked to the default company.")


if __name__ == "__main__":
    run_migration()
