#!/usr/bin/env python3
"""
Fix shlokas_export CSV where source_verse is being converted to dates.
Extract verse from shloka_id (e.g., CH_SU_1/4-5 -> "4-5") and fix source_chapter too.
"""

import csv
import re

def extract_from_shloka_id(shloka_id):
    """Extract chapter and verse from shloka_id like CH_SU_1/1 or CH_SU_1/4-5"""
    match = re.match(r'CH_[A-Z]+_(\d+)/(.*)', shloka_id)
    if match:
        return match.group(1), match.group(2)
    return "", ""

def fix_csv():
    input_file = "shlokas_export_2026-05-14.csv"
    output_file = "shlokas_export_2026-05-14_FIXED.csv"

    print(f"Reading {input_file}...")

    with open(input_file, 'r', encoding='utf-8-sig') as f:
        reader = csv.DictReader(f)
        headers = reader.fieldnames

        print(f"Columns: {len(headers)}")
        print(f"Headers: {headers}")

        rows = []
        fixed_count = 0

        for i, row in enumerate(reader):
            shloka_id = row.get('shloka_id', '')

            if not shloka_id:
                continue

            # Extract chapter and verse from shloka_id
            chapter, verse = extract_from_shloka_id(shloka_id)

            if chapter and verse:
                old_chapter = row.get('source_chapter', '')
                old_verse = row.get('source_verse', '')

                # Check if current values are wrong
                is_wrong = False

                # Check if source_verse looks like a date (contains month names or date patterns)
                if old_verse and re.match(r'\d{4}-\d{2}-\d{2}', old_verse):
                    is_wrong = True

                # Also fix if chapter/verse are swapped or contain wrong data
                if old_chapter and old_chapter not in [chapter, '']:
                    if not old_chapter.isdigit() or old_chapter != chapter:
                        is_wrong = True

                if is_wrong:
                    fixed_count += 1
                    row['source_chapter'] = chapter
                    row['source_verse'] = verse

            rows.append(row)

            if i % 1000 == 0:
                print(f"Processed {i} rows, fixed {fixed_count}...")

    print(f"\nTotal rows: {len(rows)}")
    print(f"Fixed rows: {fixed_count}")

    # Write fixed CSV
    with open(output_file, 'w', encoding='utf-8', newline='') as f:
        writer = csv.DictWriter(f, fieldnames=headers)
        writer.writeheader()
        writer.writerows(rows)

    print(f"\nSaved fixed CSV to: {output_file}")

    # Show sample of fixed data
    print("\n--- Sample verification ---")
    for i in range(min(5, len(rows))):
        row = rows[i]
        print(f"shloka_id={row['shloka_id']} | chapter={row['source_chapter']} | verse={row['source_verse']}")

if __name__ == "__main__":
    fix_csv()