"""
Migration: Add item_type system for purchase_items

This migration will:
- Create item_type_definitions table if missing
- Add item_type_id column to purchase_items
- Add FK constraint and index for item_type_id
"""

import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent.parent))

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


def run_migration() -> None:
    print("=" * 80)
    print("ADDING ITEM TYPE SYSTEM TO PURCHASE ITEMS")
    print("=" * 80)

    with engine.connect() as conn:
        print("\n[1/4] Creating item_type_definitions table (if missing)...")
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS item_type_definitions (
                id VARCHAR PRIMARY KEY,
                created_at TIMESTAMP NULL,
                updated_at TIMESTAMP NULL,
                item_type VARCHAR NOT NULL UNIQUE,
                description VARCHAR NULL,
                is_asset BOOLEAN NOT NULL DEFAULT FALSE,
                is_taxable BOOLEAN NOT NULL DEFAULT TRUE,
                requires_serial_number BOOLEAN NOT NULL DEFAULT FALSE,
                is_active BOOLEAN NOT NULL DEFAULT TRUE,
                useful_life_years INTEGER NULL,
                category VARCHAR NULL,
                debit_account_id VARCHAR(36) NULL REFERENCES accounting_codes(id),
                credit_account_id VARCHAR(36) NULL REFERENCES accounting_codes(id),
                accumulated_depreciation_account_id VARCHAR(36) NULL REFERENCES accounting_codes(id),
                depreciation_expense_account_id VARCHAR(36) NULL REFERENCES accounting_codes(id),
                accumulated_amortization_account_id VARCHAR(36) NULL REFERENCES accounting_codes(id),
                amortization_expense_account_id VARCHAR(36) NULL REFERENCES accounting_codes(id),
                gl_code VARCHAR NULL,
                notes TEXT NULL
            );
        """))

        print("[1/4] Creating item_type indexes (if missing)...")
        conn.execute(text("""
            CREATE INDEX IF NOT EXISTS idx_item_type ON item_type_definitions (item_type);
        """))
        conn.execute(text("""
            CREATE INDEX IF NOT EXISTS idx_item_type_active ON item_type_definitions (is_active);
        """))

        print("\n[2/4] Adding item_type_id to purchase_items (if missing)...")
        conn.execute(text("""
            ALTER TABLE purchase_items
            ADD COLUMN IF NOT EXISTS item_type_id VARCHAR;
        """))

        print("[3/4] Adding FK constraint (if missing)...")
        conn.execute(text("""
            DO $$
            BEGIN
                IF NOT EXISTS (
                    SELECT 1 FROM information_schema.table_constraints
                    WHERE constraint_name = 'fk_purchase_items_item_type_id'
                ) THEN
                    ALTER TABLE purchase_items
                    ADD CONSTRAINT fk_purchase_items_item_type_id
                    FOREIGN KEY (item_type_id)
                    REFERENCES item_type_definitions(id)
                    ON DELETE SET NULL;
                END IF;
            END $$;
        """))

        print("[4/4] Creating index on purchase_items.item_type_id (if missing)...")
        conn.execute(text("""
            CREATE INDEX IF NOT EXISTS idx_purchase_items_item_type_id
            ON purchase_items (item_type_id);
        """))

        conn.commit()

    print("\n" + "=" * 80)
    print("[SUCCESS] Item type system migration completed!")
    print("New/verified objects:")
    print("  - item_type_definitions table")
    print("  - purchase_items.item_type_id column")
    print("  - fk_purchase_items_item_type_id constraint")
    print("  - idx_purchase_items_item_type_id index")
    print("=" * 80)


if __name__ == "__main__":
    run_migration()
