import requests
import sqlite3
import json
import os
from dotenv import load_dotenv

load_dotenv()

BASE_LOGIN_URL = os.getenv("BASE_LOGIN_URL")
PROJECTS_URL = os.getenv("PROJECTS_URL")

USERNAME = os.getenv("USEREMAIL")
PASSWORD = os.getenv("PASSWORD")

# -----------------------------------------
# STEP 1: Login and get token
# -----------------------------------------
def get_token():
    payload = {
        "username": USERNAME,
        "password": PASSWORD
    }

    response = requests.post(BASE_LOGIN_URL, json=payload)
    data = response.json()

    if data.get("status") == 200:
        print("✔ Login successful")
        return data["token"]
    else:
        raise Exception("❌ Login failed: " + str(data))


# -----------------------------------------
# STEP 2: Fetch all projects using token
# -----------------------------------------
def fetch_all_projects(token):
    headers = {
        "Authorization": f"Bearer {token}"
    }
    response = requests.get(PROJECTS_URL, headers=headers)
    data = response.json()

    if data.get("status") == 200:
        return data["data"]
    else:
        raise Exception("❌ Failed to fetch project list")


# -----------------------------------------
# STEP 3: Create SQLite DB + Table
# -----------------------------------------
def init_db():
    conn = sqlite3.connect("pcm_projects.db")
    cursor = conn.cursor()

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS projects (
            id TEXT PRIMARY KEY,
            description TEXT,
            updatedAt TEXT,
            isActive INTEGER,
            sub_descriptions TEXT
        )
    """)

    conn.commit()
    conn.close()


# -----------------------------------------
# STEP 4: Insert project + childProject data
# -----------------------------------------
def save_projects_to_db(projects):
    conn = sqlite3.connect("pcm_projects.db")
    cursor = conn.cursor()

    incoming_ids = []  # <-- collect all IDs coming from API

    for p in projects:
        project_id = p.get("id")
        incoming_ids.append(project_id)

        description = p.get("description")
        updatedAt = p.get("updatedAt")
        isActive = 1 if p.get("isActive") else 0

        # Collect child project sub descriptions
        child_projects = p.get("childProjects", [])
        sub_descriptions = [child.get("description") for child in child_projects if child.get("description")]
        sub_desc_str = json.dumps(sub_descriptions)

        # -----------------------------
        # Check if this project exists
        # -----------------------------
        cursor.execute("SELECT updatedAt FROM projects WHERE id = ?", (project_id,))
        existing_row = cursor.fetchone()

        if existing_row:
            db_updatedAt = existing_row[0]

            # Update only if date is newer
            if updatedAt and updatedAt > db_updatedAt:
                print(f"🔄 Updating project {project_id} (newer updatedAt detected)")
                cursor.execute("""
                    UPDATE projects
                    SET description = ?, updatedAt = ?, isActive = ?, sub_descriptions = ?
                    WHERE id = ?
                """, (description, updatedAt, isActive, sub_desc_str, project_id))

            else:
                print(f"⏩ Skipping project {project_id} (no changes)")
                continue

        else:
            print(f"➕ Inserting new project {project_id}")
            cursor.execute("""
                INSERT INTO projects (id, description, updatedAt, isActive, sub_descriptions)
                VALUES (?, ?, ?, ?, ?)
            """, (project_id, description, updatedAt, isActive, sub_desc_str))

    # -----------------------------------------
    # STEP 5: DELETE missing IDs (NEW FEATURE)
    # -----------------------------------------
    cursor.execute("SELECT id FROM projects")
    db_ids = [row[0] for row in cursor.fetchall()]

    ids_to_delete = set(db_ids) - set(incoming_ids)

    for deleted_id in ids_to_delete:
        print(f"🗑 Removing deleted project {deleted_id}")
        cursor.execute("DELETE FROM projects WHERE id = ?", (deleted_id,))

    conn.commit()
    conn.close()
    print("✔ Smart save completed with delete sync!")


# -----------------------------------------
# MAIN EXECUTION
# -----------------------------------------
if __name__ == "__main__":
    print("🔹 Logging in to API...")
    token = get_token()

    print("🔹 Fetching all projects...")
    projects = fetch_all_projects(token)

    print("🔹 Initializing SQLite database...")
    init_db()

    print("🔹 Saving data into SQLite...")
    save_projects_to_db(projects)

    print("🎉 Done! Extracted, updated, and synced project data.")