import csv
import sys
import os
import sqlite3

def evaluate(agent_file, gold_file, db_file):
    # 1. Validate Database Artifact strictly
    if not os.path.exists(db_file):
        print(f"Error: Required database artifact {db_file} not found.")
        sys.exit(1)
        
    try:
        conn = sqlite3.connect(db_file)
        cursor = conn.cursor()
        
        # Check tables
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cursor.fetchall()
        if not tables:
            print("Error: Database exists but contains no tables.")
            sys.exit(1)
            
        # Ensure at least two tables exist (requires normalization)
        if len(tables) < 2:
            print(f"Error: Database schema lacks normalization. Expected multiple relational tables, found only {len(tables)}: {[t[0] for t in tables]}")
            sys.exit(1)
            
        # Ensure tables actually contain data
        valid_data = False
        for table in tables:
            cursor.execute(f"SELECT COUNT(*) FROM {table[0]}")
            count = cursor.fetchone()[0]
            if count > 0:
                valid_data = True
                
        if not valid_data:
            print("Error: Database tables are initialized but empty.")
            sys.exit(1)
            
        conn.close()
    except Exception as e:
        print(f"Error validating database schema: {e}")
        sys.exit(1)

    # 2. Validate CSV Headers exactly
    required_headers = ['Character', 'Domain', 'Zone', 'Action', 'Scene Number', 'Reasoning/Citation']
    try:
        with open(agent_file, 'r', encoding='utf-8') as f:
            reader = csv.reader(f)
            headers = next(reader)
            if [h.strip() for h in headers] != required_headers:
                print(f"Error: Headers do not exactly match the required schema. Found: {headers}")
                sys.exit(1)
    except Exception as e:
        print(f"Error reading agent CSV headers: {e}")
        sys.exit(1)

    # 3. Load Tuples and Strictly Enforce Row Counts
    def load_rows(filepath):
        rows = []
        with open(filepath, 'r', encoding='utf-8') as f:
            # We use reader to count total physical lines (minus header)
            # This ensures we don't let them silently skip malformed rows
            reader = csv.DictReader(f)
            for row in reader:
                # Basic fields
                char = row.get('Character', '').strip().lower()
                domain = row.get('Domain', '').strip().lower()
                zone = row.get('Zone', '').strip().lower()
                scene = row.get('Scene Number', '').strip().lower()
                action = row.get('Action', '').strip()
                reason = row.get('Reasoning/Citation', '').strip()
                
                # Treat malformed/short rows as invalid, but append them anyway
                # so they are counted against the precision denominator as penalty rows!
                if not char or not domain or not scene or len(action) < 4 or len(reason) < 4:
                    rows.append(("MALFORMED_OR_SHORT_ROW", "", "", ""))
                else:
                    rows.append((char, domain, zone, scene))
        return rows

    agent_rows = load_rows(agent_file)
    gold_rows = load_rows(gold_file)
    
    if not gold_rows:
        print("Error: Gold standard file is empty.")
        sys.exit(1)
    if not agent_rows:
        print("Error: Agent output is empty.")
        sys.exit(1)

    # Validate domains
    permitted_domains = {'antioch', 'tyre', 'tarsus', 'pentapolis', 'ephesus', 'mytilene', 'sea'}
    for r in agent_rows:
        domain = r[1]
        if domain and domain not in permitted_domains:
            print(f"Error: Illegal domain found in output: {domain}")
            sys.exit(1)

    # Validate scene coverage
    agent_scenes = set(r[3] for r in agent_rows if r[3])
    gold_scenes = set(r[3] for r in gold_rows)
    if not gold_scenes.issubset(agent_scenes):
        print(f"Error: Output does not cover all required scenes. Missing: {gold_scenes - agent_scenes}")
        sys.exit(1)

    # Match exact tuples
    gold_tuples = set(gold_rows)
    agent_tuples = set(agent_rows)
    correct_matches = agent_tuples.intersection(gold_tuples)
    
    # Recall: Fraction of Gold Tuples Found
    recall = len(correct_matches) / len(gold_tuples)
    
    # Precision: Correct Matches / Total PHYSICAL Rows Generated (penalizes duplicate spam and malformed rows)
    precision = len(correct_matches) / len(agent_rows)
    
    print(f"Agent matched {len(correct_matches)} out of {len(gold_tuples)} unique gold-standard tuples.")
    print(f"Total physical agent rows generated: {len(agent_rows)}")
    print(f"Recall (Accuracy): {recall:.2%}")
    print(f"Precision (Efficiency): {precision:.2%}")
    
    if recall >= 0.80 and precision >= 0.80:
        print("Result: PASS (Recall & Precision >= 80%)")
        sys.exit(0)
    else:
        print("Result: FAIL (Must maintain 80% in both Recall and Precision to prevent spamming rows)")
        sys.exit(1)

if __name__ == "__main__":
    if len(sys.argv) != 3:
        print("Usage: python grader_script.py <agent_output_csv> <gold_standard_csv>")
        sys.exit(1)
    
    evaluate(sys.argv[1], sys.argv[2], "pericles_spatial.db")