"""Phase 2 Migration: Holidays + Leave Records
- Create holidays + leave_records tables
- Import HK public holidays 2025-2027 (51 records)
- Insert demo leave records

Run: docker exec aars-aars-1 python3 /app/migrate_phase2.py
"""
import sys
sys.path.insert(0, "/app")

from database import SessionLocal, engine, Base
from models import Holiday, LeaveRecord
from parsers import get_all_holidays
from datetime import date, datetime

print("=" * 60)
print("Phase 2 Migration: Holidays + Leave Records")
print("=" * 60)

# Step 1: Create tables (idempotent — Base.metadata.create_all skips existing)
print("\n[1/3] Creating tables...")
Base.metadata.create_all(engine)
print("  ✓ holidays + leave_records tables ready")

# Step 2: Import holidays
print("\n[2/3] Importing HK public holidays 2025-2027...")
db = SessionLocal()
added = 0
updated = 0
for d, name_zh, name_en, is_mandatory in get_all_holidays():
    existing = db.query(Holiday).filter(Holiday.date == d).first()
    if existing:
        # Update if changed
        if (existing.name != name_zh or existing.name_en != name_en
                or existing.is_mandatory != is_mandatory):
            existing.name = name_zh
            existing.name_en = name_en
            existing.is_mandatory = is_mandatory
            existing.source = "gov_hk"
            existing.updated_at = datetime.utcnow()
            updated += 1
    else:
        db.add(Holiday(
            date=d,
            name=name_zh,
            name_en=name_en,
            region="HK",
            is_mandatory=is_mandatory,
            source="gov_hk",
        ))
        added += 1

db.commit()
print(f"  ✓ Added {added} new holidays, updated {updated}")
print(f"  ✓ Total holidays in DB: {db.query(Holiday).count()}")

# Step 3: Demo leave records
print("\n[3/3] Inserting demo leave records...")
demo_leaves = [
    # Jay Lam 2026-07-08: SL 2h (有返工但中間請病假)
    ("Jay Lam", "2026-07-08", "SL", 2.0, "睇醫生"),
    # Jay Lam 2026-07-10: CL 4h (上午補鐘)
    ("Jay Lam", "2026-07-10", "CL", 4.0, "補鐘"),
    # Cindy Cheung 2026-07-09: AL 8h (全日年假冇打卡)
    ("Cindy Cheung", "2026-07-09", "AL", 8.0, "Family trip"),
    # May Chan 2026-07-07: SL 4h (下午走咗)
    ("May Chan", "2026-07-07", "SL", 4.0, "感冒"),
]

added_leave = 0
for name, d_str, lt, hrs, reason in demo_leaves:
    d = datetime.strptime(d_str, "%Y-%m-%d").date()
    existing = db.query(LeaveRecord).filter(
        LeaveRecord.employee_name == name,
        LeaveRecord.date == d,
        LeaveRecord.leave_type == lt,
    ).first()
    if not existing:
        db.add(LeaveRecord(
            employee_name=name,
            date=d,
            leave_type=lt,
            hours=hrs,
            reason=reason,
            source="manual",
        ))
        added_leave += 1

db.commit()
print(f"  ✓ Added {added_leave} demo leave records")
print(f"  ✓ Total leave records in DB: {db.query(LeaveRecord).count()}")

db.close()
print("\n" + "=" * 60)
print("Migration complete ✓")
print("=" * 60)