import asyncio
import json
import os
import sys
from datetime import date, datetime
from decimal import Decimal
import uuid

sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

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

OUTPUT_SQL_PATHS = [
    os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..', 'ingest_qa_checksheets.sql')),
    os.path.abspath(os.path.join(os.path.dirname(__file__), '..', 'migrations', '003_ingest_all_qa_and_checklists.sql')),
]

EXPORT_TABLES = [
    ("station_master", "station_id"),
    ("qa_checksheet_template", "template_id"),
    ("qa_checksheet_section", "section_id"),
    ("qa_checkpoint_item", "item_id"),
    ("qa_station_template_map", "map_id"),
    ("yard_checkpoint_master", "checkpoint_id"),
    ("yard_qa_checklist_item_master", "item_id"),
    ("movement_checklist_item_master", "item_id"),
    ("thermal_check_item_master", "id"),
    ("maintenance_checklist_item", "id"),
]

def sql_quote(val):
    if val is None:
        return "NULL"
    if isinstance(val, bool):
        return "TRUE" if val else "FALSE"
    if isinstance(val, (int, float, Decimal)):
        return str(val)
    if isinstance(val, (datetime, date)):
        return f"'{val.isoformat()}'"
    if isinstance(val, uuid.UUID):
        return f"'{str(val)}'"
    if isinstance(val, (dict, list)):
        dumped = json.dumps(val, ensure_ascii=False).replace("'", "''")
        return f"'{dumped}'::jsonb"
    s = str(val).replace("'", "''")
    return f"'{s}'"

async def export_sql():
    async with AsyncSessionLocal() as db:
        # Load station map for filling station_code if needed
        st_rows = await db.execute(text("SELECT station_id, station_code FROM station_master"))
        station_code_map = {str(r[0]): r[1] for r in st_rows.fetchall()}

        sql_lines = []
        sql_lines.append("-- ============================================================================")
        sql_lines.append("-- Audi Automobile ERP - QA & Plant Checksheets Ingestion Master Script")
        sql_lines.append(f"-- Generated At: {datetime.now().isoformat()}")
        sql_lines.append("-- Self-healing: Dynamically adds missing columns and relaxes legacy NOT NULLs")
        sql_lines.append("-- Dual-compatible: Mirrors legacy column aliases and sets non-null booleans")
        sql_lines.append("-- Idempotent: Uses ON CONFLICT DO UPDATE so it never fails on re-runs")
        sql_lines.append("-- ============================================================================\n")
        sql_lines.append("BEGIN;\n")

        sql_lines.append("-- ----------------------------------------------------------------------------")
        sql_lines.append("-- 1. DDL: ENSURE ALL TABLES & COLUMNS EXIST (SELF-HEALING)")
        sql_lines.append("-- ----------------------------------------------------------------------------\n")

        # Step 1: Base table creations if they do not exist at all
        sql_lines.append("""
CREATE TABLE IF NOT EXISTS station_master (
    station_id UUID PRIMARY KEY,
    station_code VARCHAR(50) UNIQUE NOT NULL,
    station_name VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS qa_checksheet_template (
    template_id UUID PRIMARY KEY,
    template_code VARCHAR(100) UNIQUE NOT NULL,
    template_name VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS qa_checksheet_section (
    section_id UUID PRIMARY KEY,
    template_id UUID NOT NULL REFERENCES qa_checksheet_template(template_id) ON DELETE CASCADE,
    section_name VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS qa_checkpoint_item (
    item_id UUID PRIMARY KEY,
    section_id UUID NOT NULL REFERENCES qa_checksheet_section(section_id) ON DELETE CASCADE,
    checkpoint_desc TEXT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS qa_station_template_map (
    map_id UUID PRIMARY KEY,
    station_id UUID NOT NULL REFERENCES station_master(station_id) ON DELETE CASCADE,
    template_id UUID NOT NULL REFERENCES qa_checksheet_template(template_id) ON DELETE CASCADE,
    sort_order INTEGER DEFAULT 1,
    is_mandatory BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS yard_checkpoint_master (
    checkpoint_id UUID PRIMARY KEY,
    checkpoint_code VARCHAR(100) UNIQUE NOT NULL,
    checkpoint_name VARCHAR(255) NOT NULL,
    sequence_no INTEGER DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS yard_qa_checklist_item_master (
    item_id UUID PRIMARY KEY,
    zone_name VARCHAR(100) NOT NULL,
    checkpoint_text TEXT NOT NULL,
    sequence_no INTEGER DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS movement_checklist_item_master (
    item_id UUID PRIMARY KEY,
    item_code VARCHAR(50) UNIQUE NOT NULL,
    item_label VARCHAR(255) NOT NULL,
    sequence_no INTEGER DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS thermal_check_item_master (
    id UUID PRIMARY KEY,
    code VARCHAR(100) UNIQUE NOT NULL,
    label VARCHAR(255) NOT NULL,
    sort_order INTEGER DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS maintenance_checklist_item (
    id UUID PRIMARY KEY,
    machine_type VARCHAR(100) NOT NULL,
    sr_no INTEGER NOT NULL,
    check_point TEXT NOT NULL,
    specification TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
""")

        # Step 2: Query database schema and generate ALTER TABLE ... ADD COLUMN IF NOT EXISTS for EVERY column
        for table_name, pk_col in EXPORT_TABLES:
            sql_lines.append(f"-- Schema alignment for {table_name}")
            col_info_res = await db.execute(text(f"""
                SELECT 
                    column_name, 
                    data_type, 
                    character_maximum_length,
                    numeric_precision,
                    numeric_scale,
                    column_default
                FROM information_schema.columns 
                WHERE table_name = '{table_name}'
                ORDER BY ordinal_position
            """))
            for r in col_info_res.fetchall():
                col, dt, char_len, num_p, num_s, col_def = r
                if col == pk_col:
                    continue  # PK is created with table

                type_str = dt.upper()
                if dt == 'character varying':
                    type_str = f"VARCHAR({char_len})" if char_len else "VARCHAR(255)"
                elif dt == 'numeric' and num_p:
                    type_str = f"NUMERIC({num_p}, {num_s or 0})"
                elif dt == 'timestamp with time zone':
                    type_str = "TIMESTAMP WITH TIME ZONE"
                elif dt == 'jsonb':
                    type_str = "JSONB"
                elif dt == 'uuid':
                    type_str = "UUID"
                elif dt == 'boolean':
                    type_str = "BOOLEAN"
                elif dt == 'text':
                    type_str = "TEXT"
                elif dt == 'integer':
                    type_str = "INTEGER"
                elif dt == 'date':
                    type_str = "DATE"

                def_clause = f" DEFAULT {col_def}" if col_def else ""
                sql_lines.append(
                    f"ALTER TABLE {table_name} ADD COLUMN IF NOT EXISTS {col} {type_str}{def_clause};"
                )
            sql_lines.append("")

        # Step 3: Relax ALL legacy schema constraints that might exist on older database migrations
        sql_lines.append("""
-- Relax legacy schema constraints that might exist on older database migrations
ALTER TABLE qa_checksheet_section ALTER COLUMN section_number DROP NOT NULL;
ALTER TABLE qa_checksheet_section ALTER COLUMN section_no DROP NOT NULL;
ALTER TABLE qa_checksheet_section ALTER COLUMN section_code DROP NOT NULL;
ALTER TABLE qa_checksheet_section ALTER COLUMN phase_id DROP NOT NULL;
ALTER TABLE qa_checksheet_section ALTER COLUMN description DROP NOT NULL;
ALTER TABLE qa_checksheet_section ALTER COLUMN sort_order DROP NOT NULL;

ALTER TABLE qa_checkpoint_item ALTER COLUMN item_number DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN item_no DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN description DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN checkpoint_desc DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN sequence_no DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN sort_order DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN is_special_feature_slot DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN special_feature_flag DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN severity_id DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN method_id DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN checkpoint_class DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN verification_method DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN standard_specification DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN requires_measurement DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN has_measurement DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN min_value DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN max_value DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN unit_of_measure DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN measurement_unit DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN frequency DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN area_desc DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN model_specs DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN sub_points DROP NOT NULL;
ALTER TABLE qa_checkpoint_item ALTER COLUMN reference_diagram_url DROP NOT NULL;

ALTER TABLE qa_checksheet_template ALTER COLUMN station_code DROP NOT NULL;
ALTER TABLE qa_checksheet_template ALTER COLUMN station_id DROP NOT NULL;
ALTER TABLE qa_checksheet_template ALTER COLUMN format_no DROP NOT NULL;
ALTER TABLE qa_checksheet_template ALTER COLUMN revision DROP NOT NULL;
ALTER TABLE qa_checksheet_template ALTER COLUMN revision_date DROP NOT NULL;
ALTER TABLE qa_checksheet_template ALTER COLUMN description DROP NOT NULL;
ALTER TABLE qa_checksheet_template ALTER COLUMN has_paint_history DROP NOT NULL;
ALTER TABLE qa_checksheet_template ALTER COLUMN has_frequency_col DROP NOT NULL;
ALTER TABLE qa_checksheet_template ALTER COLUMN updated_at DROP NOT NULL;
ALTER TABLE qa_checksheet_template ALTER COLUMN updated_by DROP NOT NULL;

ALTER TABLE qa_station_template_map ALTER COLUMN body_type_id DROP NOT NULL;
ALTER TABLE qa_station_template_map ALTER COLUMN model_id DROP NOT NULL;
ALTER TABLE qa_station_template_map ALTER COLUMN model_ids DROP NOT NULL;
ALTER TABLE qa_station_template_map ALTER COLUMN body_type_ids DROP NOT NULL;
ALTER TABLE qa_station_template_map ALTER COLUMN sort_order DROP NOT NULL;
ALTER TABLE qa_station_template_map ALTER COLUMN is_mandatory DROP NOT NULL;

ALTER TABLE maintenance_checklist_item ALTER COLUMN specification DROP NOT NULL;
ALTER TABLE maintenance_checklist_item ALTER COLUMN is_active DROP NOT NULL;
""")

        # Step 4: Indexes
        sql_lines.append("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_qa_station_template_null_body 
ON qa_station_template_map (station_id, template_id) 
WHERE body_type_id IS NULL;
""")

        # Clean existing station template map before re-populating to prevent partial unique index conflicts
        sql_lines.append("-- Reset qa_station_template_map for clean sync")
        sql_lines.append("DELETE FROM qa_station_template_map;\n")

        # Step 5: Data Export
        sql_lines.append("-- ----------------------------------------------------------------------------")
        sql_lines.append("-- 2. DATA INGESTION (INSERT ... ON CONFLICT DO UPDATE)")
        sql_lines.append("-- ----------------------------------------------------------------------------\n")

        for table_name, pk_col in EXPORT_TABLES:
            col_res = await db.execute(text(f"""
                SELECT column_name 
                FROM information_schema.columns 
                WHERE table_name = '{table_name}'
                ORDER BY ordinal_position
            """))
            cols = [r[0] for r in col_res.fetchall()]

            rows_res = await db.execute(text(f"SELECT {', '.join(cols)} FROM {table_name}"))
            rows = rows_res.fetchall()

            sql_lines.append(f"-- ----------------------------------------------------------------------------")
            sql_lines.append(f"-- DATA: {table_name} ({len(rows)} rows)")
            sql_lines.append(f"-- ----------------------------------------------------------------------------")

            for r in rows:
                col_val_map = dict(zip(cols, r))

                # Normalize / mirror legacy and new column names so BOTH are non-null and fully populated
                if table_name == 'qa_checksheet_section':
                    s_no = col_val_map.get('section_no') if col_val_map.get('section_no') is not None else col_val_map.get('section_number')
                    if s_no is None:
                        s_no = 1
                    col_val_map['section_no'] = s_no
                    col_val_map['section_number'] = s_no
                    col_val_map['is_active'] = bool(col_val_map.get('is_active', True))

                elif table_name == 'qa_checkpoint_item':
                    i_no = col_val_map.get('item_no') if col_val_map.get('item_no') is not None else col_val_map.get('item_number')
                    if i_no is None:
                        i_no = 1
                    col_val_map['item_no'] = i_no
                    col_val_map['item_number'] = i_no

                    desc = col_val_map.get('checkpoint_desc') or col_val_map.get('description') or ''
                    col_val_map['checkpoint_desc'] = desc
                    col_val_map['description'] = desc

                    seq = col_val_map.get('sort_order') if col_val_map.get('sort_order') is not None else col_val_map.get('sequence_no')
                    if seq is None:
                        seq = 1
                    col_val_map['sort_order'] = seq
                    col_val_map['sequence_no'] = seq

                    has_m = bool(col_val_map.get('requires_measurement') or col_val_map.get('has_measurement'))
                    col_val_map['requires_measurement'] = has_m
                    col_val_map['has_measurement'] = has_m

                    unit = col_val_map.get('unit_of_measure') or col_val_map.get('measurement_unit')
                    col_val_map['unit_of_measure'] = unit
                    col_val_map['measurement_unit'] = unit

                    is_spec = bool(col_val_map.get('is_special_feature_slot') or col_val_map.get('special_feature_flag'))
                    col_val_map['is_special_feature_slot'] = is_spec
                    col_val_map['is_active'] = bool(col_val_map.get('is_active', True))

                elif table_name == 'qa_checksheet_template':
                    if not col_val_map.get('station_code') and col_val_map.get('station_id'):
                        col_val_map['station_code'] = station_code_map.get(str(col_val_map['station_id']))
                    col_val_map['has_paint_history'] = bool(col_val_map.get('has_paint_history'))
                    col_val_map['has_frequency_col'] = bool(col_val_map.get('has_frequency_col'))
                    col_val_map['is_active'] = bool(col_val_map.get('is_active', True))

                elif table_name == 'station_master':
                    col_val_map['requires_qa'] = bool(col_val_map.get('requires_qa'))
                    col_val_map['is_rework_station'] = bool(col_val_map.get('is_rework_station'))
                    col_val_map['backflush_enabled'] = bool(col_val_map.get('backflush_enabled', True))
                    col_val_map['is_active'] = bool(col_val_map.get('is_active', True))

                elif table_name == 'qa_station_template_map':
                    col_val_map['is_mandatory'] = bool(col_val_map.get('is_mandatory', True))

                col_names = list(col_val_map.keys())
                val_strs = [sql_quote(col_val_map[c]) for c in col_names]

                conflict_target = pk_col
                update_cols = [c for c in col_names if c != pk_col]
                set_clauses = [f"{c} = EXCLUDED.{c}" for c in update_cols]

                stmt = (
                    f"INSERT INTO {table_name} ({', '.join(col_names)})\n"
                    f"VALUES ({', '.join(val_strs)})\n"
                    f"ON CONFLICT ({conflict_target}) DO UPDATE SET\n  "
                    + ",\n  ".join(set_clauses)
                    + ";"
                )
                sql_lines.append(stmt)
            sql_lines.append("")

        # Step 6: Post-Ingestion permissions
        sql_lines.append("-- ----------------------------------------------------------------------------")
        sql_lines.append("-- 3. POST-INGESTION ACTIVATION & PERMISSIONS")
        sql_lines.append("-- ----------------------------------------------------------------------------")
        sql_lines.append("""
UPDATE station_master
SET requires_qa = TRUE
WHERE station_id IN (SELECT DISTINCT station_id FROM qa_station_template_map);

INSERT INTO module_master (module_id, module_code, module_name, description, is_active, created_at)
VALUES 
  (gen_random_uuid(), 'PRODUCTION', 'Production', 'Production dashboard, WIP tracking, station pipeline, timer alerts, holds', true, NOW()),
  (gen_random_uuid(), 'QA', 'Quality Assurance', 'QA checksheet templates, station inspection gating, shortages, signoffs', true, NOW())
ON CONFLICT (module_code) DO NOTHING;

INSERT INTO role_module_permission (id, role_code, module_code, can_view, can_create, can_edit, can_delete, can_approve, updated_at)
SELECT gen_random_uuid(), r.role_code, 'QA', r.can_view, r.can_create, r.can_edit, r.can_delete, r.can_approve, NOW()
FROM (VALUES
    ('ADMIN', true, true, true, true, true),
    ('PRODUCTION_MGR', true, true, true, true, true),
    ('PRODUCTION_OPERATOR', true, true, true, false, false),
    ('FVI_OPERATOR', true, true, true, false, true),
    ('ENGINEERING_HEAD', true, true, true, false, false),
    ('SALES_MGR', true, false, false, false, false),
    ('STORE_MGR', true, false, false, false, false),
    ('VIEWER', true, false, false, false, false),
    ('YARD_SUPERVISOR', true, false, false, false, false)
) AS r(role_code, can_view, can_create, can_edit, can_delete, can_approve)
WHERE NOT EXISTS (
    SELECT 1 FROM role_module_permission 
    WHERE role_module_permission.role_code = r.role_code AND role_module_permission.module_code = 'QA'
);

COMMIT;
""")

        full_sql = "\n".join(sql_lines) + "\n"
        for out_path in OUTPUT_SQL_PATHS:
            os.makedirs(os.path.dirname(out_path), exist_ok=True)
            with open(out_path, 'w', encoding='utf-8') as f:
                f.write(full_sql)
            print(f"Exported {len(sql_lines)} lines to: {out_path} ({os.path.getsize(out_path):,} bytes)")

if __name__ == '__main__':
    asyncio.run(export_sql())