#!/usr/bin/env python3
"""
Convert CSV to JSON with fixed source_verse from shloka_id.
Avoids Excel date conversion issues.
"""

import csv
import json
import re

def extract_verse_from_shloka_id(shloka_id):
    """Extract verse from CH_SU_1/4-5 -> '4-5'"""
    match = re.match(r'CH_[A-Z]+_\d+/(.*)', shloka_id)
    return match.group(1) if match else ""

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

    print(f"Converting {input_file} to JSON...")

    shlokas = []
    corrupted = 0
    total = 0

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

        for row in reader:
            total += 1
            shloka_id = row.get('shloka_id', '')
            correct_verse = extract_verse_from_shloka_id(shloka_id)
            current_verse = row.get('source_verse', '')

            # Check if corrupted
            is_date = bool(re.match(r'^(\d{2}-)?(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)(-\d{2})?$',
                          current_verse, re.IGNORECASE))

            if is_date or current_verse != correct_verse:
                corrupted += 1
                row['source_verse'] = correct_verse

            # Build clean JSON object
            shloka = {
                "id": row.get('id', ''),
                "shloka_id": shloka_id,
                "sanskrit": row.get('sanskrit', ''),
                "unicode": row.get('unicode', ''),
                "transliteration": row.get('transliteration', ''),
                "translation_hindi": row.get('translations_hindi', ''),
                "translation_english": row.get('english', ''),
                "source": {
                    "text_name": row.get('source_text_name', ''),
                    "section": row.get('source_section', ''),
                    "chapter": row.get('source_chapter', ''),
                    "verse": row.get('source_verse', '')
                },
                "metadata": {
                    "created_by": row.get('created_by_name', ''),
                    "status": row.get('status', ''),
                    "created_at": row.get('created_at', ''),
                    "tags": row.get('tags', '')
                }
            }

            # Add QnA pairs if present
            questions = []
            for i in range(1, 11):
                q_en = row.get(f'q_en_{i}', '')
                a_en = row.get(f'a_en_{i}', '')
                q_hi = row.get(f'q_hi_{i}', '')
                a_hi = row.get(f'a_hi_{i}', '')
                q_sa = row.get(f'q_sa_{i}', '')
                a_sa = row.get(f'a_sa_{i}', '')

                if q_en or q_hi or q_sa:
                    questions.append({
                        "en": {"q": q_en, "a": a_en},
                        "hi": {"q": q_hi, "a": a_hi},
                        "sa": {"q": q_sa, "a": a_sa}
                    })

            if questions:
                shloka["questions"] = questions

            shlokas.append(shloka)

            if total % 500 == 0:
                print(f"Processed {total} rows...")

    # Write JSON
    with open(output_file, 'w', encoding='utf-8') as f:
        json.dump(shlokas, f, ensure_ascii=False, indent=2)

    print(f"\nTotal: {total}")
    print(f"Fixed verse corruption: {corrupted}")
    print(f"Saved: {output_file}")

    # Show sample
    print("\n--- Sample entries ---")
    for s in shlokas[:5]:
        print(f"{s['shloka_id']} | verse={s['source']['verse']} | sanskrit={s['sanskrit'][:30]}...")

if __name__ == "__main__":
    csv_to_json()