"""Migration script for billing/stripe integration.

Adds:
- companies.stripe_customer_id column
- subscriptions table

Usage:
    cd /home/vincent/projects/command-sovereignty
    source .venv/bin/activate
    python scripts/migrate_billing.py
"""
import sys
import os

# Add project root to path
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app import create_app
from app.models import db

app = create_app()

with app.app_context():
    # Check if stripe_customer_id already exists
    inspector = db.inspect(db.engine)
    columns = [col['name'] for col in inspector.get_columns('companies')]
    
    if 'stripe_customer_id' not in columns:
        print("Adding companies.stripe_customer_id column...")
        with db.engine.connect() as conn:
            conn.execute(db.text("ALTER TABLE companies ADD COLUMN stripe_customer_id VARCHAR(255) DEFAULT ''"))
            conn.commit()
        print("  ✓ Added")
    else:
        print("  ✓ companies.stripe_customer_id already exists")

    # Create subscriptions table if it doesn't exist
    tables = inspector.get_table_names()
    if 'subscriptions' not in tables:
        print("Creating subscriptions table...")
        with db.engine.connect() as conn:
            conn.execute(db.text("""
                CREATE TABLE subscriptions (
                    id VARCHAR(36) PRIMARY KEY,
                    company_id VARCHAR(36) NOT NULL,
                    stripe_subscription_id VARCHAR(255) DEFAULT '',
                    stripe_price_id VARCHAR(255) DEFAULT '',
                    stripe_status VARCHAR(50) DEFAULT 'none',
                    current_period_start DATETIME,
                    current_period_end DATETIME,
                    cancel_at_period_end BOOLEAN DEFAULT 0,
                    trial_end DATETIME,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (company_id) REFERENCES companies(id)
                )
            """))
            conn.execute(db.text("CREATE INDEX ix_subscriptions_company_id ON subscriptions(company_id)"))
            conn.execute(db.text("CREATE INDEX ix_subscriptions_stripe_subscription_id ON subscriptions(stripe_subscription_id)"))
            conn.commit()
        print("  ✓ Created")
    else:
        print("  ✓ subscriptions table already exists")

    print("\nMigration complete!")