"""
Add production_order_id to manufacturing_costs and relax COGS allocation revenue FK.
Run: python migrations/add_manufacturing_enterprise_columns.py
"""
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():
    with engine.connect() as conn:
        conn.execute(text("""
            ALTER TABLE manufacturing_costs
            ADD COLUMN IF NOT EXISTS production_order_id VARCHAR(36)
        """))
        conn.execute(text("""
            CREATE INDEX IF NOT EXISTS ix_manufacturing_costs_production_order_id
            ON manufacturing_costs(production_order_id)
        """))
        conn.execute(text("""
            DO $$
            BEGIN
                IF NOT EXISTS (
                    SELECT 1 FROM information_schema.table_constraints
                    WHERE constraint_name = 'fk_manufacturing_costs_production_order_id'
                ) THEN
                    ALTER TABLE manufacturing_costs
                    ADD CONSTRAINT fk_manufacturing_costs_production_order_id
                    FOREIGN KEY (production_order_id) REFERENCES production_orders(id)
                    ON DELETE SET NULL;
                END IF;
            END $$;
        """))
        conn.execute(text("""
            ALTER TABLE cogs_allocations
            ALTER COLUMN revenue_gl_entry_id DROP NOT NULL
        """))
        conn.commit()
    print("Manufacturing enterprise columns migration complete.")


if __name__ == "__main__":
    run()
