"""
Link job cards to quotations and support non-stock job lines (labour, job-purchased hardware, salvage).

Run: python migrations/add_job_card_quotation_workflow.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 run_migration():
    with engine.begin() as conn:
        logger.info("Adding job_cards.quotation_id...")
        conn.execute(text("""
            ALTER TABLE job_cards
            ADD COLUMN IF NOT EXISTS quotation_id VARCHAR(36)
            REFERENCES quotations(id) ON DELETE SET NULL
        """))
        conn.execute(text("""
            CREATE INDEX IF NOT EXISTS ix_job_cards_quotation_id
            ON job_cards (quotation_id)
        """))

        logger.info("Extending job_card_materials for non-stock lines...")
        conn.execute(text("""
            ALTER TABLE job_card_materials
            ALTER COLUMN product_id DROP NOT NULL
        """))
        conn.execute(text("""
            ALTER TABLE job_card_materials
            ADD COLUMN IF NOT EXISTS description TEXT
        """))
        conn.execute(text("""
            ALTER TABLE job_card_materials
            ADD COLUMN IF NOT EXISTS is_non_stock BOOLEAN DEFAULT FALSE
        """))
        conn.execute(text("""
            ALTER TABLE job_card_materials
            ADD COLUMN IF NOT EXISTS quotation_item_id VARCHAR(36)
            REFERENCES quotation_items(id) ON DELETE SET NULL
        """))
        conn.execute(text("""
            ALTER TABLE job_card_materials
            ADD COLUMN IF NOT EXISTS unit_of_measure_id VARCHAR(36)
            REFERENCES unit_of_measures(id) ON DELETE SET NULL
        """))

    logger.info("Job card / quotation workflow migration complete.")


if __name__ == "__main__":
    run_migration()
