src/data/processor.py

"""
Data Processor for Kalim Chatbot
Converts Excel files to structured JSON for the knowledge base
"""
import json
import re
from pathlib import Path
from typing import Dict, List, Optional, Any
from openpyxl import load_workbook

import sys
sys.path.insert(0, str(Path(__file__).parent.parent.parent))

from config.settings import (
    MAIN_EXCEL_FILE,
    SUPPLEMENTARY_EXCEL_FILE,
    MAJORS_JSON_FILE,
    LOCATIONS_JSON_FILE,
    HOLLAND_MAPPING_FILE,
    PROCESSED_DATA_DIR,
    HOLLAND_CODES,
    SECONDARY_TRACKS,
    GOVERNORATES
)


VALID_HOLLAND_CODES = frozenset("RIASEC")


def clean_text(text: Optional[str]) -> str:
    """Clean and normalize Arabic text."""
    if text is None:
        return ""
    text = str(text).strip()
    # Remove extra whitespace
    text = re.sub(r'\s+', ' ', text)
    return text


def parse_languages(row_data: Dict[str, Any]) -> List[str]:
    """Extract teaching languages from row data."""
    languages = []
    if row_data.get('arabic') and clean_text(row_data['arabic']):
        languages.append("العربية")
    if row_data.get('english') and clean_text(row_data['english']):
        languages.append("الانكليزية")
    if row_data.get('french') and clean_text(row_data['french']):
        languages.append("الفرنسية")
    if row_data.get('other_lang') and clean_text(row_data['other_lang']):
        other = clean_text(row_data['other_lang'])
        if other and other not in languages:
            languages.append(other)
    return languages


def parse_secondary_tracks(track_text: str) -> List[str]:
    """Parse allowed secondary tracks from text."""
    if not track_text:
        return []

    tracks = []
    track_text = clean_text(track_text).lower()

    # Map common variations to standard names
    track_mapping = {
        "علوم عامة": "علوم عامة",
        "علوم حياة": "علوم حياة",
        "اقتصاد واجتماع": "اقتصاد واجتماع",
        "اقتصاد": "اقتصاد واجتماع",
        "فلسفة وانسانيات": "فلسفة وانسانيات",
        "فلسفة": "فلسفة وانسانيات",
        "انسانيات": "فلسفة وانسانيات",
        "آداب": "فلسفة وانسانيات",
    }

    for key, value in track_mapping.items():
        if key in track_text and value not in tracks:
            tracks.append(value)

    return tracks


def parse_holland_codes(
    primary: Any, secondary: Any, tertiary: Any, triplet: Any
) -> Dict[str, str]:
    """Build the Holland code block for one major.

    The spreadsheet holds the three codes in separate columns (17-19) *and* a
    combined "الرمز الثلاثي" column (20). The two disagree on seven rows, and
    the combined column is not reliable — for 'هندسة زراعية – هندسة الحدائق' it
    repeats the neighbouring row's value. The individual columns are the ones
    the client validated their expected results against, so they win.

    The one case the individual columns cannot express is a malformed triplet:
    الطب is recorded as S/I/S, which repeats a letter and is not a valid RIASEC
    code, leaving one slot unmatchable. Only there do we fall back to column 20.

    Args:
        primary, secondary, tertiary: The individual code columns (17-19).
        triplet: The combined code column (20), used only as a repair.

    Returns:
        Dict with primary, secondary, tertiary and triplet keys.
    """
    parts = [clean_text(c).upper() for c in (primary, secondary, tertiary)]

    def well_formed(codes):
        return len(set(codes)) == 3 and all(c in VALID_HOLLAND_CODES for c in codes)

    if not well_formed(parts):
        combined = clean_text(triplet).upper().replace(" ", "")
        # Repair ONLY the documented shape: three valid letters with a repeat,
        # as in الطب (S/I/S). Any other malformed row — most importantly a
        # blank tertiary cell — must keep columns 17-19, because column 20
        # disagrees with them on seven rows and is not authoritative.
        all_filled = all(parts) and all(c in VALID_HOLLAND_CODES for c in parts)
        has_repeat = len(set(parts)) < 3
        if all_filled and has_repeat and well_formed(list(combined)):
            print(f"  Repaired malformed Holland code {''.join(parts)!r} -> {combined!r}")
            parts = list(combined)
        else:
            print(f"  WARNING: Holland codes {parts!r} are not well-formed and do not "
                  f"match the repairable shape (column 20 = {combined!r}); "
                  f"keeping columns 17-19.")

    return {
        "primary": parts[0],
        "secondary": parts[1],
        "tertiary": parts[2],
        "triplet": "".join(parts),
    }


def parse_locations(row_data: Dict[str, Any], start_col: int = 22) -> List[Dict[str, str]]:
    """Parse geographic locations from row data."""
    locations = []

    # The Excel has repeating pattern: المحافظة, القضاء, الفرع
    # Starting from column 22 (0-indexed: 21)
    # Pattern repeats up to 10 times

    # Check first location set (columns 22-24 in 1-indexed = 21-23 in 0-indexed)
    gov_col = row_data.get('gov_1', '')
    district_col = row_data.get('district_1', '')
    branch_col = row_data.get('branch_1', '')

    if gov_col or branch_col:
        location = {
            "governorate": clean_text(gov_col) if gov_col else "",
            "district": clean_text(district_col) if district_col else "",
            "branch": clean_text(branch_col) if branch_col else ""
        }
        if location["governorate"] or location["branch"]:
            locations.append(location)

    # Check additional location sets
    for i in range(2, 11):
        gov = row_data.get(f'gov_{i}', '')
        district = row_data.get(f'district_{i}', '')
        branch = row_data.get(f'branch_{i}', '')

        if gov or branch:
            location = {
                "governorate": clean_text(gov) if gov else "",
                "district": clean_text(district) if district else "",
                "branch": clean_text(branch) if branch else ""
            }
            if location["governorate"] or location["branch"]:
                locations.append(location)

    return locations


# Localities that appear in branch addresses but are not district names, so
# they cannot be derived from GOVERNORATES.
_EXTRA_BRANCH_KEYWORDS = {
    "الحدث": "جبل لبنان",
    "البوشرية": "جبل لبنان",
}


def _branch_keyword_map() -> Dict[str, str]:
    """Branch-address keyword -> governorate, derived from GOVERNORATES.

    This used to be a hand-copied dict that omitted nine districts present in
    the config map (بشري، جزين، حاصبيا، مرجعيون، بنت جبيل، راشيا،
    البقاع الغربي، الهرمل، الضنية), so a branch in any of them got no
    governorate and became invisible to filter_by_location.
    """
    keywords = {}
    for gov, districts in GOVERNORATES.items():
        if gov == "الكل":
            continue
        keywords[gov] = gov
        for district in districts:
            keywords[district] = gov
    keywords.update(_EXTRA_BRANCH_KEYWORDS)
    return keywords


def extract_governorate_from_branch(branch_text: str) -> Optional[str]:
    """Infer a governorate from a free-text branch address.

    Matching is most-specific-first: a district or locality name beats a bare
    governorate name, and a longer keyword beats a shorter one. First-match
    ordering used to resolve "الحدث – بيروت" to بيروت, which is the same class
    of bug that put Mount Lebanon campuses into Beirut results at query time.
    """
    if not branch_text:
        return None

    branch_lower = branch_text.lower()
    matches = [(kw, gov) for kw, gov in _branch_keyword_map().items()
               if kw in branch_lower]
    if not matches:
        return None

    # Sort key: districts/localities (not governorate names) first, then the
    # longest keyword.
    matches.sort(key=lambda kv: (kv[0] in GOVERNORATES, -len(kv[0])))
    return matches[0][1]


def process_main_excel() -> List[Dict[str, Any]]:
    """Process the main Excel file and extract majors data."""
    print(f"Processing main Excel file: {MAIN_EXCEL_FILE}")

    wb = load_workbook(MAIN_EXCEL_FILE)
    ws = wb.active

    majors = []

    # Get headers from first row
    headers = []
    for col in range(1, ws.max_column + 1):
        headers.append(ws.cell(row=1, column=col).value or '')

    print(f"Found {ws.max_row - 1} rows of data")

    # Ids must be unique: get_major_by_id returns the first hit, and the
    # locations index is keyed by id, so a collision silently drops a major.
    used_ids = set()

    # Process each row
    for row_num in range(2, ws.max_row + 1):
        # Extract all cell values for this row
        row_values = {}
        for col in range(1, ws.max_column + 1):
            row_values[col] = ws.cell(row=row_num, column=col).value

        # Skip empty rows
        if not row_values.get(5):  # Major name column
            continue

        # Build structured major object
        # Parse ID safely (handle non-numeric values)
        raw_id = row_values.get(1)
        try:
            major_id = int(raw_id) if raw_id else row_num - 1
        except (ValueError, TypeError):
            major_id = row_num - 1

        if major_id in used_ids:
            original = major_id
            major_id = max(used_ids) + 1
            print(f"  Duplicate id {original} on row {row_num}; reassigned to {major_id}.")
            print(f"  WARNING: the supplementary sheet is keyed by the sheet's own id, "
                  f"so any record for id {original} is applied to the FIRST row that "
                  f"claimed it and not to this one. Two majors sharing an id in the "
                  f"source spreadsheet is a data-entry error worth fixing at source.")
        used_ids.add(major_id)

        major = {
            "id": major_id,
            "name_ar": clean_text(row_values.get(5)),
            "name_en": clean_text(row_values.get(6)),
            "name_fr": clean_text(row_values.get(7)),
            "faculty_category": clean_text(row_values.get(2)),
            "faculty_ar": clean_text(row_values.get(3)),
            "degree_level": clean_text(row_values.get(4)),
            "admission_requirements": clean_text(row_values.get(8)),
            "description": clean_text(row_values.get(9)),
            "teaching_languages": parse_languages({
                'arabic': row_values.get(10),
                'english': row_values.get(11),
                'french': row_values.get(12),
                'other_lang': row_values.get(13)
            }),
            "career_opportunities": clean_text(row_values.get(14)),
            "entry_mechanism": clean_text(row_values.get(15)),
            "allowed_tracks": parse_secondary_tracks(clean_text(row_values.get(16))),
            "holland_codes": parse_holland_codes(
                row_values.get(17), row_values.get(18), row_values.get(19), row_values.get(20)
            ),
            "holland_interpretation": clean_text(row_values.get(21)),
            "locations": []
        }

        # Process locations (columns 22 onwards have repeating pattern)
        # Pattern: المحافظة (gov), القضاء (district), الفرع (branch)
        # First set starts at column 22
        location_data = {
            'gov_1': row_values.get(22),
            'district_1': row_values.get(23),
            'branch_1': row_values.get(24),
        }

        # Additional location sets (pattern repeats)
        col_offset = 25
        for i in range(2, 11):
            if col_offset + 2 <= ws.max_column:
                location_data[f'gov_{i}'] = row_values.get(col_offset)
                location_data[f'district_{i}'] = row_values.get(col_offset + 1)
                location_data[f'branch_{i}'] = row_values.get(col_offset + 2)
                col_offset += 3

        major['locations'] = parse_locations(location_data)

        # If no governorate found, try to extract from branch address
        for loc in major['locations']:
            if not loc['governorate'] and loc['branch']:
                extracted_gov = extract_governorate_from_branch(loc['branch'])
                if extracted_gov:
                    loc['governorate'] = extracted_gov

        majors.append(major)

    print(f"Processed {len(majors)} majors")
    return majors


def process_supplementary_excel() -> Dict[int, Dict[str, Any]]:
    """Process supplementary Excel for additional/corrected data."""
    print(f"Processing supplementary Excel file: {SUPPLEMENTARY_EXCEL_FILE}")

    wb = load_workbook(SUPPLEMENTARY_EXCEL_FILE)
    ws = wb.active

    supplements = {}

    for row_num in range(2, ws.max_row + 1):
        row_id = ws.cell(row=row_num, column=1).value
        if not row_id:
            continue

        try:
            row_id = int(row_id)
        except (ValueError, TypeError):
            # The main sheet's id parse is guarded the same way; without this
            # one bad cell aborts the whole regeneration with a bare ValueError.
            print(f"  WARNING: non-numeric supplementary id {row_id!r} on row "
                  f"{row_num}; skipped.")
            continue

        # Extract Holland codes from supplementary file (more reliable)
        supplements[row_id] = {
            "holland_primary": clean_text(ws.cell(row=row_num, column=5).value).upper(),
            "holland_secondary": clean_text(ws.cell(row=row_num, column=6).value).upper(),
            "holland_tertiary": clean_text(ws.cell(row=row_num, column=7).value).upper(),
            "holland_triplet": clean_text(ws.cell(row=row_num, column=8).value).upper(),
            "teaching_lang_code": clean_text(ws.cell(row=row_num, column=11).value),
        }

        # Extract branch locations from supplementary (columns 12-17)
        branches = []
        for col in range(12, 18):
            branch = ws.cell(row=row_num, column=col).value
            if branch:
                branches.append(clean_text(branch))
        supplements[row_id]["branches"] = branches

    print(f"Processed {len(supplements)} supplementary records")
    return supplements


def merge_data(majors: List[Dict], supplements: Dict[int, Dict]) -> List[Dict]:
    """Merge main data with supplementary corrections.

    NOTE: Holland codes from MAIN Excel are authoritative and should NOT be
    overwritten by supplementary data, as the main Excel has verified codes.
    """
    for major in majors:
        major_id = major['id']
        if major_id in supplements:
            supp = supplements[major_id]

            # DO NOT overwrite Holland codes from supplementary file
            # The main Excel has the correct/verified Holland codes
            # Supplementary data may have incorrect codes that don't match

            # Parse teaching language code
            lang_code = supp.get('teaching_lang_code', '')
            if lang_code:
                folded = lang_code.lower()
                languages = []
                if 'ara' in folded:
                    languages.append("العربية")
                if 'eng' in folded:
                    languages.append("الانكليزية")
                if 'fre' in folded or 'fra' in folded:
                    languages.append("الفرنسية")
                if languages:
                    # Replace, not merge: the supplementary sheet exists to
                    # correct the main one, so "Eng" there means English only
                    # even when the main sheet listed three languages. (A
                    # free-text "other" language from column 13 is lost in that
                    # case; that is the documented trade-off, not an oversight.)
                    major['teaching_languages'] = languages
                else:
                    print(f"  WARNING: unrecognised language code {lang_code!r} for "
                          f"id {major_id}; keeping the main sheet's languages.")

            # Add branch information if missing
            if supp.get('branches') and not major['locations']:
                for branch in supp['branches']:
                    if branch:
                        gov = extract_governorate_from_branch(branch)
                        major['locations'].append({
                            "governorate": gov or "",
                            "district": "",
                            "branch": branch
                        })

    return majors


def generate_locations_data(majors: List[Dict]) -> Dict[str, Any]:
    """Generate locations reference data."""
    locations = {
        "governorates": GOVERNORATES,
        "major_locations": {}
    }

    # Map each major to its available locations. Sorted because set iteration
    # order varies between runs, which would otherwise make every regeneration
    # produce a large spurious diff in a version-controlled data file.
    for major in majors:
        govs = {loc['governorate'] for loc in major['locations'] if loc['governorate']}
        locations["major_locations"][major['id']] = sorted(govs)

    return locations


def generate_holland_mapping() -> Dict[str, Any]:
    """Generate Holland RIASEC mapping data."""
    return {
        "codes": HOLLAND_CODES,
        "code_list": ["R", "I", "A", "S", "E", "C"],
        "dropdown_options": [
            {"code": "R", "label": "R - الواقعي (Realistic)"},
            {"code": "I", "label": "I - الباحث (Investigative)"},
            {"code": "A", "label": "A - الفني (Artistic)"},
            {"code": "S", "label": "S - الاجتماعي (Social)"},
            {"code": "E", "label": "E - المغامر (Enterprising)"},
            {"code": "C", "label": "C - التقليدي (Conventional)"},
        ]
    }


def save_json(data: Any, filepath: Path) -> None:
    """Save data to JSON file with proper Arabic encoding."""
    filepath.parent.mkdir(parents=True, exist_ok=True)
    with open(filepath, 'w', encoding='utf-8') as f:
        json.dump(data, f, ensure_ascii=False, indent=2)
    print(f"Saved: {filepath}")


def main():
    """Main processing function."""
    print("=" * 60)
    print("Kalim Data Processor")
    print("=" * 60)

    # Ensure output directory exists
    PROCESSED_DATA_DIR.mkdir(parents=True, exist_ok=True)

    # Process Excel files
    majors = process_main_excel()
    supplements = process_supplementary_excel()

    # Merge data
    majors = merge_data(majors, supplements)

    # Generate additional data files
    locations = generate_locations_data(majors)
    holland_mapping = generate_holland_mapping()

    # Save to JSON files
    save_json({"majors": majors}, MAJORS_JSON_FILE)
    save_json(locations, LOCATIONS_JSON_FILE)
    save_json(holland_mapping, HOLLAND_MAPPING_FILE)

    print("=" * 60)
    print("Processing complete!")
    print(f"Total majors: {len(majors)}")
    print(f"Output files:")
    print(f"  - {MAJORS_JSON_FILE}")
    print(f"  - {LOCATIONS_JSON_FILE}")
    print(f"  - {HOLLAND_MAPPING_FILE}")
    print("=" * 60)

    return majors


if __name__ == "__main__":
    main()