import psycopg2
import json
import os
from dotenv import load_dotenv

load_dotenv()

# Load curriculum data from JSON file
def load_curriculum_from_json(json_file_path):
    """Load curriculum data from the provided JSON file"""
    with open(json_file_path, 'r', encoding='utf-8') as f:
        return json.load(f)

# Convert JSON structure to database format
def convert_json_to_db_format(json_data):
    """Convert the JSON curriculum data to the database format"""
    curriculum_data = {}
    
    for curriculum_name, curriculum_content in json_data.items():
        # Parse curriculum name to extract grade, language, and subject
        if "Grade 4 Arabic" in curriculum_name:
            grade, is_arabic, subject = 4, True, "Science"
        elif "Grade 5 English" in curriculum_name:
            grade, is_arabic, subject = 5, False, "Science"
        elif "Grade 6 Arabic" in curriculum_name:
            grade, is_arabic, subject = 6, True, "Science"
        elif "Grade 6 English" in curriculum_name:
            grade, is_arabic, subject = 6, False, "Science"
        else:
            continue  # Skip unknown curriculum formats
            
        # Create curriculum structure
        language = "Arabic" if is_arabic else "English"
        title = f"منهج العلوم للصف {grade}" if is_arabic else f"Science Curriculum for Grade {grade}"
        
        units = []
        unit_number = 1
        
        for unit_name, unit_content in curriculum_content.items():
            # Skip non-unit sections (like introduction, projects, assessments)
            if any(keyword in unit_name.lower() for keyword in ['مقدمة', 'get started', 'مشروع', 'project', 'قيم', 'assess', 'تقييم', 'سلامة', 'safety']):
                continue
                
            unit = {
                "number": unit_number,
                "name": unit_name,
                "description": f"وحدة {unit_number}" if is_arabic else f"Unit {unit_number}",
                "concepts": []
            }
            
            if "المفاهيم" in unit_content:
                concept_number = 1
                for concept_name in unit_content["المفاهيم"].keys():
                    concept = {
                        "number": f"{unit_number}.{concept_number}",
                        "name": concept_name,
                        "description": concept_name,
                        "lessons": []  # We don't have lesson details in the JSON
                    }
                    unit["concepts"].append(concept)
                    concept_number += 1
                    
            elif "Concepts" in unit_content:
                concept_number = 1
                for concept_name in unit_content["Concepts"].keys():
                    concept = {
                        "number": f"{unit_number}.{concept_number}",
                        "name": concept_name,
                        "description": concept_name,
                        "lessons": []  # We don't have lesson details in the JSON
                    }
                    unit["concepts"].append(concept)
                    concept_number += 1
            
            units.append(unit)
            unit_number += 1
        
        curriculum_data[(grade, is_arabic, subject)] = {
            "grade": grade,
            "subject": subject,
            "language": language,
            "title": title,
            "units": units
        }
    
    return curriculum_data


def setup_curriculum_database(json_file_path: str, drop_existing_table: bool = False):
    """
    Sets up the curriculum structure table and populates it with data from JSON file.
    Args:
        json_file_path: Path to the JSON file containing curriculum data
        drop_existing_table: If True, drops the existing table before creating it.
    """
    try:
        # Load and convert JSON data
        json_data = load_curriculum_from_json(json_file_path)
        curriculum_data = convert_json_to_db_format(json_data)
        
        conn = psycopg2.connect(
            host=os.getenv("POSTGRES_HOST", "localhost"),
            port=os.getenv("POSTGRES_PORT", "5432"),
            user=os.getenv("POSTGRES_USER"),
            password=os.getenv("POSTGRES_PASSWORD"),
            dbname=os.getenv("POSTGRES_DB")
        )
        conn.autocommit = True
        
        with conn.cursor() as cur:
            if drop_existing_table:
                print("Dropping existing curriculum_structure table...")
                cur.execute("DROP TABLE IF EXISTS curriculum_structure CASCADE;")
                print("Table dropped successfully.")
            
            print("Creating curriculum_structure table...")
            cur.execute("""
                CREATE TABLE IF NOT EXISTS curriculum_structure (
                    id SERIAL PRIMARY KEY,
                    grade INTEGER NOT NULL,
                    is_arabic BOOLEAN NOT NULL,
                    subject VARCHAR(100) NOT NULL DEFAULT 'Science',
                    curriculum_data JSONB NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(grade, is_arabic, subject)
                );
            """)
            
            # Create indexes for better performance
            cur.execute("CREATE INDEX IF NOT EXISTS idx_curriculum_grade_lang ON curriculum_structure(grade, is_arabic);")
            cur.execute("CREATE INDEX IF NOT EXISTS idx_curriculum_subject ON curriculum_structure(subject);")
            cur.execute("CREATE INDEX IF NOT EXISTS idx_curriculum_grade_subject ON curriculum_structure(grade, subject);")
            
            print("Table and indexes created successfully.")
            
            print("Inserting curriculum data from JSON...")
            
            # Insert curriculum data for all combinations
            for (grade, is_arabic, subject), curriculum in curriculum_data.items():
                try:
                    cur.execute(
                        """
                        INSERT INTO curriculum_structure (grade, is_arabic, subject, curriculum_data)
                        VALUES (%s, %s, %s, %s)
                        ON CONFLICT (grade, is_arabic, subject) 
                        DO UPDATE SET 
                            curriculum_data = EXCLUDED.curriculum_data,
                            created_at = CURRENT_TIMESTAMP;
                        """,
                        (grade, is_arabic, subject, json.dumps(curriculum))
                    )
                    
                    language_desc = "Arabic" if is_arabic else "English"
                    print(f"✅ Inserted Grade {grade} {language_desc} {subject} curriculum")
                    
                except Exception as e:
                    language_desc = "Arabic" if is_arabic else "English"
                    print(f"❌ Error inserting Grade {grade} {language_desc} {subject}: {e}")
                    continue
            
            print("\nVerifying inserted data...")
            cur.execute("SELECT grade, is_arabic, subject, created_at FROM curriculum_structure ORDER BY grade, is_arabic;")
            results = cur.fetchall()
            
            print(f"\n📊 Summary: {len(results)} curriculum records inserted")
            for row in results:
                grade, is_arabic, subject, created_at = row
                language = "Arabic" if is_arabic else "English"
                print(f"   • Grade {grade} - {language} - {subject} (Created: {created_at.strftime('%Y-%m-%d %H:%M')})")
            
            # Test curriculum retrieval
            print("\n🧪 Testing curriculum retrieval...")
            cur.execute("SELECT curriculum_data FROM curriculum_structure WHERE grade = 4 AND is_arabic = true LIMIT 1;")
            test_result = cur.fetchone()
            
            if test_result:
                test_curriculum = test_result[0]
                unit_count = len(test_curriculum.get('units', []))
                print(f"✅ Test successful: Grade 4 Arabic curriculum has {unit_count} units")
                
                # Show first unit as example
                if unit_count > 0:
                    first_unit = test_curriculum['units'][0]
                    print(f"   First unit: {first_unit['name']} with {len(first_unit.get('concepts', []))} concepts")
            else:
                print("❌ Test failed: Could not retrieve test curriculum")
                
    except psycopg2.OperationalError as e:
        print(f"❌ Database connection failed: {e}")
    except FileNotFoundError as e:
        print(f"❌ JSON file not found: {e}")
    except json.JSONDecodeError as e:
        print(f"❌ Invalid JSON format: {e}")
    except Exception as e:
        print(f"❌ An error occurred: {e}")
    finally:
        if 'conn' in locals() and conn:
            conn.close()
            print("\n🔐 Database connection closed.")


def verify_curriculum_structure():
    """Verify the curriculum structure and show sample data"""
    try:
        conn = psycopg2.connect(
            host=os.getenv("POSTGRES_HOST", "localhost"),
            port=os.getenv("POSTGRES_PORT", "5432"),
            user=os.getenv("POSTGRES_USER"),
            password=os.getenv("POSTGRES_PASSWORD"),
            dbname=os.getenv("POSTGRES_DB")
        )
        
        with conn.cursor() as cur:
            print("📋 Curriculum Structure Verification")
            print("=" * 50)
            
            # Get all curriculum records
            cur.execute("""
                SELECT grade, is_arabic, subject, curriculum_data, created_at 
                FROM curriculum_structure 
                ORDER BY grade, is_arabic;
            """)
            
            results = cur.fetchall()
            
            for row in results:
                grade, is_arabic, subject, curriculum_data, created_at = row
                language = "العربية" if is_arabic else "English"
                
                print(f"\n📚 Grade {grade} - {language} - {subject}")
                print(f"   Created: {created_at.strftime('%Y-%m-%d %H:%M')}")
                print(f"   Title: {curriculum_data.get('title', 'N/A')}")
                
                units = curriculum_data.get('units', [])
                print(f"   Units ({len(units)}):")
                
                for unit in units:
                    unit_name = unit.get('name', 'N/A')
                    concepts = unit.get('concepts', [])
                    print(f"     • Unit {unit.get('number', '?')}: {unit_name} ({len(concepts)} concepts)")
                    
                    for concept in concepts[:2]:  # Show first 2 concepts
                        concept_name = concept.get('name', 'N/A')
                        lessons = concept.get('lessons', [])
                        print(f"       - Concept {concept.get('number', '?')}: {concept_name} ({len(lessons)} lessons)")
                    
                    if len(concepts) > 2:
                        print(f"       ... and {len(concepts) - 2} more concepts")
                        
    except Exception as e:
        print(f"❌ Verification failed: {e}")
    finally:
        if 'conn' in locals() and conn:
            conn.close()


if __name__ == "__main__":
    print("🚀 Setting up Curriculum Structure Database from JSON")
    print("=" * 60)
    
    # Path to the JSON file
    json_file_path = "All_Curriculums_grouped.json"

    # Read DROP_CURRICULUM_STRUCTURE from env and set drop_existing_table accordingly
    _drop_val = os.getenv("DROP_CURRICULUM_STRUCTURE", "").strip().lower()
    drop_existing_table = _drop_val in ("1", "true", "yes", "y")
    print("**************************************************")
    print(f"Drop existing table: {drop_existing_table}")
    print("**************************************************")
    # Setup curriculum database with JSON data
    setup_curriculum_database(json_file_path, drop_existing_table=drop_existing_table)
    
    print("\n" + "=" * 60)
    print("🔍 Verifying Setup")
    
    # Verify the setup
    verify_curriculum_structure()
