import os
import json
import sqlite3


# --- Configuration ---
# Ensures the DB is created inside the 'backend' folder
DATABASE_FILE = os.path.join("backend", "portfolio.sqlite")
DATA_DIR = "data"


def create_connection():
    # Ensure the backend directory exists
    os.makedirs(os.path.dirname(DATABASE_FILE), exist_ok=True)
    return sqlite3.connect(DATABASE_FILE)


def setup_database():
    conn = create_connection()
    cursor = conn.cursor()

    print("🛠️  Building Modular Database...")

    # --- 1. Create Tables ---

    # Projects
    cursor.execute('''CREATE TABLE IF NOT EXISTS projects (
        id TEXT PRIMARY KEY, 
        title TEXT, 
        type TEXT, 
        description TEXT, 
        technologies TEXT, 
        github_url TEXT, 
        demo_url TEXT, 
        image_path TEXT
    )''')

    # Articles
    cursor.execute('''CREATE TABLE IF NOT EXISTS articles (
        title TEXT, description TEXT, url TEXT, type TEXT
    )''')

    # Videos
    cursor.execute('''CREATE TABLE IF NOT EXISTS videos (
        id TEXT, title TEXT, description TEXT, thumbnail_url TEXT, url TEXT
    )''')

    # Research
    cursor.execute('''CREATE TABLE IF NOT EXISTS research (
        title TEXT, description TEXT, url TEXT, type TEXT
    )''')

    # Skills (Updated for modular JSON with 3 columns)
    cursor.execute('''CREATE TABLE IF NOT EXISTS skills (
        id TEXT PRIMARY KEY, 
        category TEXT, 
        skill_list TEXT 
    )''')

    # Experience (New Table)
    cursor.execute('''CREATE TABLE IF NOT EXISTS experience (
        id TEXT PRIMARY KEY, 
        role TEXT, 
        company TEXT, 
        duration TEXT, 
        description TEXT
    )''')

    # Education (New Table)
    cursor.execute('''CREATE TABLE IF NOT EXISTS education (
        id TEXT PRIMARY KEY, 
        degree TEXT, 
        institution TEXT, 
        details TEXT
    )''')

    # Certifications
    cursor.execute('''CREATE TABLE IF NOT EXISTS certifications (
        name TEXT PRIMARY KEY
    )''')

    # --- 2. Generic Insert Function ---
    def insert_from_json(filename, table, columns):
        filepath = os.path.join(DATA_DIR, filename)
        if not os.path.exists(filepath):
            print(f"   ⚠️  Warning: {filename} not found. Skipping.")
            return

        with open(filepath, 'r', encoding='utf-8') as f:
            data = json.load(f)

        count = 0
        for item in data:
            # --- FIX: Pre-process lists into strings ---

            # 1. Handle 'technologies' list (for projects)
            if 'technologies' in item and isinstance(item['technologies'], list):
                item['technologies'] = json.dumps(item['technologies'])

            # 2. Handle 'list' -> 'skill_list' mapping (for skills)
            # We look for the key 'list' (from JSON) and convert it to 'skill_list' (for DB)
            if 'list' in item and isinstance(item['list'], list):
                item['skill_list'] = json.dumps(item['list'])

            # --- FIX: Collect values ---
            values = []
            for col in columns:
                # If the column is 'skill_list', we expect the data might still be in 'list'
                # if we didn't map it above, but we did map it, so we just grab 'skill_list'.
                # We use .get() to avoid errors if a field is missing.
                values.append(item.get(col))

            placeholders = ",".join(["?"] * len(columns))
            cursor.execute(
                f"INSERT OR REPLACE INTO {table} VALUES ({placeholders})", values)
            count += 1
        print(f"   ✅ Loaded {count} items into '{table}'")

    # --- 3. Run Inserts ---
    print("📥 Inserting modular data...")

    # Projects
    insert_from_json("projects.json", "projects", [
                     "id", "title", "type", "description", "technologies", "github_url", "demo_url", "image_path"])

    # Standard Content
    insert_from_json("articles.json", "articles", [
                     "title", "description", "url", "type"])
    insert_from_json("videos.json", "videos", [
                     "id", "title", "description", "thumbnail_url", "url"])
    insert_from_json("research.json", "research", [
                     "title", "description", "url", "type"])

    # New Modular Files
    # Note: 'skill_list' maps to the 'list' key in the json via the fix above
    insert_from_json("skills.json", "skills", ["id", "category", "skill_list"])
    insert_from_json("experience.json", "experience", [
                     "id", "role", "company", "duration", "description"])
    insert_from_json("education.json", "education", [
                     "id", "degree", "institution", "details"])
    insert_from_json("certifications.json", "certifications", ["name"])

    conn.commit()
    conn.close()
    print(f"🎉 Database updated at {DATABASE_FILE}")


if __name__ == "__main__":
    setup_database()