"""
Consolidate similar project name variations into single canonical names
"""
from sqlalchemy import create_engine, text
import os

DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://timesheet_user:Tm7kL9pX3vN2qR8wE4@localhost:5432/timesheet")
engine = create_engine(DATABASE_URL)

# Define consolidation mappings
# Format: old_name -> new_canonical_name
CONSOLIDATION_MAP = {
    # Mahindra variations
    'Mahindra Manulife': 'mahindraManulifeDistributor',
    'Mahindra Manulife CMS': 'mahindraCMS',
    'mahindra-cms': 'mahindraCMS',
    'mahindra-cms-copy': 'mahindraCMS',
    'Mahindramanulife': 'mahindraManulifeDistributor',
    'Mahindramanulife_uat_code': 'mahindraManulifeDistributor',
    'mahindra-manulife-distributor': 'mahindraManulifeDistributor',
    'mahindra-manulife-retail': 'mahindraManulifeRetail',
    'mahindra': 'mahindraCMS',
    'MahindraService': 'mahindraCMS',

    # Other consolidations
    'timesheet': 'timesheet_new',
    'Copy': 'temp-copy',
    'laravel-starter': 'laravel_vue_starter',
}

print("\n" + "="*80)
print("CONSOLIDATING PROJECT NAME VARIATIONS")
print("="*80 + "\n")

with engine.connect() as conn:
    print("Planned consolidations:")
    print("-" * 80)

    total_affected = 0
    for old_name, new_name in CONSOLIDATION_MAP.items():
        # Check how many records will be affected
        result = conn.execute(text("""
            SELECT COUNT(*) as count, SUM(duration) / 3600.0 as hours
            FROM activity_records
            WHERE project_name = :old_name
        """), {"old_name": old_name})

        row = result.fetchone()
        count, hours = row[0], row[1] or 0

        if count > 0:
            print(f"{old_name:40} -> {new_name:40} ({count:,} records, {hours:.2f}h)")
            total_affected += count

    print(f"\nTotal records to consolidate: {total_affected:,}")

    # Perform consolidation
    print("\n" + "="*80)
    print("Consolidating...")
    print("-" * 80)

    updated = 0
    for old_name, new_name in CONSOLIDATION_MAP.items():
        result = conn.execute(text("""
            UPDATE activity_records
            SET project_name = :new_name
            WHERE project_name = :old_name
        """), {"old_name": old_name, "new_name": new_name})

        if result.rowcount > 0:
            print(f"Updated {result.rowcount:,} records: {old_name} -> {new_name}")
            updated += result.rowcount

    conn.commit()

    print(f"\n[OK] Total records updated: {updated:,}")

    # Show consolidated Mahindra projects
    print("\n" + "="*80)
    print("Mahindra projects after consolidation:")
    print("-" * 80)

    result = conn.execute(text("""
        SELECT
            project_name,
            COUNT(*) as count,
            SUM(duration) / 3600.0 as hours
        FROM activity_records
        WHERE LOWER(project_name) LIKE '%mahindra%'
        GROUP BY project_name
        ORDER BY hours DESC
    """))

    total_mahindra_hours = 0
    for row in result:
        pname, count, hours = row
        total_mahindra_hours += hours
        print(f"{pname:50} {count:>6,} records  {hours:>8.2f}h")

    print("-" * 80)
    print(f"{'TOTAL MAHINDRA':50} {' ':>6}  {total_mahindra_hours:>8.2f}h")

print("\n" + "="*80)
print("COMPLETE!")
print("="*80 + "\n")