import uuid from datetime import datetime, timezone from sqlalchemy import select, text from sqlalchemy.dialects.postgresql import insert as pg_insert from app.core.database import AsyncSessionLocal from app.core.logging import app_logger from app.models.access_models import RoleMaster, ModuleMaster, RoleModulePermission DEFAULT_ROLES = [ ("YARD_SUPERVISOR", "Yard Supervisor", "Oversees Yard operations, Chassis Inward, Station Scanning & Reports"), ("PRODUCTION_SUPERVISOR", "Production Supervisor", "Shopfloor station scanning, timers & chassis flow control"), ("QA_SUPERVISOR", "QA Supervisor", "Plant-wide Quality Assurance, checksheet configuration, and station QA staffing"), ("QA_INSPECTOR", "QA Inspector", "Station-level checksheet inspection, defect observation, and photo evidence logging"), ] DEFAULT_MODULES = [ ("PRODUCTION", "Production", "Production dashboard, WIP tracking, station pipeline, timer alerts, holds"), ("QA", "Quality Assurance", "QA checksheet templates, station inspection gating, shortages, signoffs"), ("CHASSIS_TRACKER", "Chassis Tracker", "Global Chassis Lifecycle Tracker with visual timeline milestones, build specs & customer updates"), ] async def run_startup_seeds(): """ Automatically runs on backend startup (Lifespan). Idempotent: Only inserts roles, modules, and default permissions if they don't already exist. Eliminates the need to manually run seeding scripts on production servers after git pull. """ print("[STARTUP SEEDER] Checking system roles and module permissions...") try: async with AsyncSessionLocal() as db: # 0. Ensure schema compatibility for QA tables try: # qa_checkpoint_response await db.execute(text(""" ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(); """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS sub_point_values JSONB DEFAULT '{}'::jsonb; """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS na_reason TEXT; """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS is_flagged BOOLEAN DEFAULT FALSE; """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS flagged_by UUID REFERENCES login_users(user_id); """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS flagged_at TIMESTAMP WITH TIME ZONE; """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS rework_status VARCHAR(20); """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS after_rework_observation TEXT; """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS after_rework_verified_by UUID REFERENCES login_users(user_id); """)) # qa_checkpoint_item await db.execute(text(""" ALTER TABLE qa_checkpoint_item ADD COLUMN IF NOT EXISTS area_desc VARCHAR(255); """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_item ADD COLUMN IF NOT EXISTS model_specs JSONB DEFAULT '{}'::jsonb; """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_item ADD COLUMN IF NOT EXISTS sub_points JSONB DEFAULT '[]'::jsonb; """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_item ADD COLUMN IF NOT EXISTS reference_diagram_url VARCHAR(500); """)) await db.execute(text(""" ALTER TABLE qa_checkpoint_item ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(); """)) # qa_station_template_map await db.execute(text(""" ALTER TABLE qa_station_template_map ADD COLUMN IF NOT EXISTS model_id UUID REFERENCES vehicle_model_master(model_id) ON DELETE SET NULL; """)) await db.execute(text(""" ALTER TABLE qa_station_template_map ADD COLUMN IF NOT EXISTS body_type_id UUID REFERENCES body_type_master(body_type_id) ON DELETE SET NULL; """)) await db.execute(text(""" ALTER TABLE qa_station_template_map ADD COLUMN IF NOT EXISTS model_ids JSONB DEFAULT '[]'::jsonb; """)) await db.execute(text(""" ALTER TABLE qa_station_template_map ADD COLUMN IF NOT EXISTS body_type_ids JSONB DEFAULT '[]'::jsonb; """)) # qa_checksheet_instance await db.execute(text(""" ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS station_qa_status VARCHAR(20) DEFAULT 'PENDING'; """)) await db.execute(text(""" ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS overall_result VARCHAR(20) DEFAULT 'PENDING'; """)) await db.execute(text(""" ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS shift VARCHAR(20); """)) await db.execute(text(""" ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS remarks TEXT; """)) await db.execute(text(""" ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS rework_notes TEXT; """)) await db.execute(text(""" ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS completed_at TIMESTAMP WITH TIME ZONE; """)) await db.execute(text(""" ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS passed_by UUID REFERENCES login_users(user_id); """)) await db.commit() print("[STARTUP SEEDER] QA schema compatibility check passed successfully.") except Exception as e: await db.rollback() print(f"[STARTUP SEEDER] Notice: QA schema check skipped: {e}") # 0b. Ensure notifications table exists try: await db.execute(text(""" CREATE TABLE IF NOT EXISTS notifications ( notification_id UUID PRIMARY KEY, recipient_type VARCHAR(20) NOT NULL DEFAULT 'STAFF', user_id UUID REFERENCES login_users(user_id) ON DELETE CASCADE, dealer_id VARCHAR(50) REFERENCES customer_master(customer_id) ON DELETE CASCADE, category VARCHAR(50) NOT NULL DEFAULT 'ORDER', event_type VARCHAR(50) NOT NULL, title VARCHAR(255) NOT NULL, message TEXT NOT NULL, reference_type VARCHAR(50), reference_id VARCHAR(100), action_url VARCHAR(255), is_read BOOLEAN NOT NULL DEFAULT FALSE, read_at TIMESTAMP WITH TIME ZONE, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ) """)) await db.execute(text("CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id, is_read, created_at DESC)")) await db.execute(text("CREATE INDEX IF NOT EXISTS idx_notifications_dealer ON notifications(dealer_id, is_read, created_at DESC)")) except Exception as e: print(f"[STARTUP SEEDER] Notice: notifications schema check skipped: {e}") # 1. Ensure Essential Modules exist in module_master for mod_code, mod_name, mod_desc in DEFAULT_MODULES: stmt = pg_insert(ModuleMaster).values( module_id=uuid.uuid4(), module_code=mod_code, module_name=mod_name, description=mod_desc, is_active=True, created_at=datetime.now(timezone.utc), ).on_conflict_do_nothing(index_elements=['module_code']) await db.execute(stmt) # 2. Ensure Roles exist in role_master for code, name, desc in DEFAULT_ROLES: stmt = pg_insert(RoleMaster).values( role_id=uuid.uuid4(), role_code=code, role_name=name, description=desc, is_system_role=False, is_active=True, created_at=datetime.now(timezone.utc), ).on_conflict_do_nothing(index_elements=['role_code']) await db.execute(stmt) # Default module assignments per role if code == "YARD_SUPERVISOR": target_modules = [ ("YARD", True, True, True, False, True), ("CHASSIS_TRACKER", True, False, False, False, False), ] elif code == "PRODUCTION_SUPERVISOR": target_modules = [ ("PRODUCTION", True, True, True, False, False), ("STATION", True, True, True, False, False), ("YARD", True, True, True, False, False), ("CHASSIS_TRACKER", True, False, False, False, False), ] elif code == "QA_SUPERVISOR": target_modules = [ ("QA", True, True, True, True, True), ("LOCATIONS", True, False, True, False, False), ("CHASSIS_TRACKER", True, False, False, False, False), ] elif code == "QA_INSPECTOR": target_modules = [ ("QA", True, True, True, False, False), ("CHASSIS_TRACKER", True, False, False, False, False), ] else: target_modules = [] for mod, can_v, can_c, can_e, can_d, can_a in target_modules: stmt = pg_insert(RoleModulePermission).values( id=uuid.uuid4(), role_code=code, module_code=mod, can_view=can_v, can_create=can_c, can_edit=can_e, can_delete=can_d, can_approve=can_a, ).on_conflict_do_nothing(index_elements=['role_code', 'module_code']) await db.execute(stmt) # 3. Ensure ADMIN, SALES, and key managers have access to baseline modules including CHASSIS_TRACKER core_admin_perms = [ ("ADMIN", "PRODUCTION", True, True, True, True, True), ("ADMIN", "QA", True, True, True, True, True), ("ADMIN", "CHASSIS_TRACKER", True, True, True, True, True), ("SALES_MGR", "CHASSIS_TRACKER", True, True, True, False, True), ("SALES_EXEC", "CHASSIS_TRACKER", True, True, True, False, False), ("PRODUCTION_MGR", "PRODUCTION", True, True, True, False, True), ("PRODUCTION_MGR", "QA", True, True, True, True, True), ("PRODUCTION_MGR", "CHASSIS_TRACKER", True, False, True, False, False), ("ENGINEERING_HEAD", "CHASSIS_TRACKER", True, False, False, False, False), ("CLIENT_ADMIN", "CHASSIS_TRACKER", True, False, False, False, False), ("VIEWER", "CHASSIS_TRACKER", True, False, False, False, False), ] for role_code, mod_code, v, c, e, d, a in core_admin_perms: stmt = pg_insert(RoleModulePermission).values( id=uuid.uuid4(), role_code=role_code, module_code=mod_code, can_view=v, can_create=c, can_edit=e, can_delete=d, can_approve=a, ).on_conflict_do_nothing(index_elements=['role_code', 'module_code']) await db.execute(stmt) await db.commit() print("[STARTUP SEEDER] All system baseline seeds verified successfully.") except Exception as e: app_logger.error(f"[STARTUP SEEDER] Non-fatal error during startup database seeding: {e}") print(f"[STARTUP SEEDER] Warning: Startup seeding encountered an error: {e}")