import http.server
import socketserver
import sqlite3
import json
import os
import urllib.parse

PORT = 8000
DB_PATH = "cv.db"
STATIC_DIR = os.path.join(os.path.dirname(__file__), "static")

# ── Spanish date parser ──────────────────────────────────────
_MONTHS_ES = {
    'enero':1,'febrero':2,'marzo':3,'abril':4,'mayo':5,'junio':6,
    'julio':7,'agosto':8,'septiembre':9,'setiembre':9,'octubre':10,
    'noviembre':11,'diciembre':12
}

def parse_date_es(s):
    """Return a (year, month) tuple for sorting. 'Actualidad' → (9999, 12)."""
    if not s:
        return (0, 0)
    s = s.strip()
    # New ISO month format: YYYY-MM
    import re
    if re.match(r'^\d{4}-\d{2}$', s):
        y, m = s.split('-')
        return (int(y), int(m))
    s = s.lower()
    if s in ('actualidad', 'presente', 'actual'):
        return (9999, 12)
    parts = s.split()
    if len(parts) >= 2:
        month = _MONTHS_ES.get(parts[0], 0)
        try:
            year = int(parts[-1])
            return (year, month)
        except ValueError:
            pass
    try:
        return (int(parts[0]), 0)
    except (ValueError, IndexError):
        return (0, 0)

def sort_experiences(exps):
    """Sort by end_date desc (is_current = máx), then start_date desc."""
    def sort_key(e):
        is_current = int(e.get('is_current', 0))
        end   = (9999, 12) if is_current else parse_date_es(e.get('end_date', ''))
        start = parse_date_es(e.get('start_date', ''))
        return (end, start)
    return sorted(exps, key=sort_key, reverse=True)


class CVRequestHandler(http.server.SimpleHTTPRequestHandler):
    def address_string(self):
        # Prevent reverse DNS lookup to fix latency/delay on Windows
        return self.client_address[0]

    def translate_path(self, path):
        # Serve static files from the 'static' directory by default
        parsed_url = urllib.parse.urlparse(path)
        clean_path = parsed_url.path
        
        # Route root path to index.html
        if clean_path == "/" or clean_path == "":
            return os.path.join(STATIC_DIR, "index.html")
            
        # If it starts with /static/, strip it and map to STATIC_DIR
        if clean_path.startswith("/static/"):
            relative_path = clean_path[len("/static/"):]
            return os.path.join(STATIC_DIR, relative_path)
            
        # Otherwise, serve from STATIC_DIR
        return os.path.join(STATIC_DIR, clean_path.lstrip("/"))

    def end_headers(self):
        # Add CORS headers
        self.send_header('Access-Control-Allow-Origin', '*')
        self.send_header('Access-Control-Allow-Methods', 'GET, POST, OPTIONS')
        self.send_header('Access-Control-Allow-Headers', 'Content-Type')
        super().end_headers()

    def do_OPTIONS(self):
        self.send_response(200, "ok")
        self.end_headers()

    def do_GET(self):
        parsed_url = urllib.parse.urlparse(self.path)
        if parsed_url.path == "/api/cv":
            self.handle_get_cv()
        else:
            # Fallback to serving static files
            super().do_GET()

    def do_POST(self):
        parsed_url = urllib.parse.urlparse(self.path)
        if parsed_url.path == "/api/cv":
            self.handle_post_cv()
        else:
            self.send_error(404, "Not Found")

    def handle_get_cv(self):
        if not os.path.exists(DB_PATH):
            self.send_error(500, "Database file not found. Did you run seed_db.py?")
            return

        try:
            conn = sqlite3.connect(DB_PATH)
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()

            # 1. Fetch Profile
            cursor.execute("SELECT * FROM profile LIMIT 1")
            profile_row = cursor.fetchone()
            profile = {}
            if profile_row:
                profile = dict(profile_row)
                # Decode JSON strings
                try:
                    profile['phones'] = json.loads(profile['phones'])
                except Exception:
                    profile['phones'] = []
                try:
                    profile['emails'] = json.loads(profile['emails'])
                except Exception:
                    profile['emails'] = []
                try:
                    profile['metadata'] = json.loads(profile['metadata'])
                except Exception:
                    profile['metadata'] = {}

            # 2. Fetch Experiences — sorted in Python (Spanish dates)
            cursor.execute("SELECT * FROM experiences")
            experiences = sort_experiences([dict(row) for row in cursor.fetchall()])

            # 3. Fetch Education
            cursor.execute("SELECT * FROM education ORDER BY display_order ASC, end_date DESC")
            education = [dict(row) for row in cursor.fetchall()]

            # 4. Fetch Lectures
            cursor.execute("SELECT * FROM lectures ORDER BY display_order ASC, lecture_date DESC")
            lectures = [dict(row) for row in cursor.fetchall()]

            # 5. Fetch Skills
            cursor.execute("SELECT * FROM skills")
            skills = [dict(row) for row in cursor.fetchall()]

            conn.close()

            # Pack response
            data = {
                "profile": profile,
                "experiences": experiences,
                "education": education,
                "lectures": lectures,
                "skills": skills
            }

            self.send_response(200)
            self.send_header("Content-Type", "application/json; charset=utf-8")
            self.end_headers()
            self.wfile.write(json.dumps(data, ensure_ascii=False).encode('utf-8'))

        except Exception as e:
            self.send_error(500, f"Error reading database: {str(e)}")

    def handle_post_cv(self):
        content_length = int(self.headers.get('Content-Length', 0))
        post_data = self.rfile.read(content_length)
        
        try:
            data = json.loads(post_data.decode('utf-8'))
        except Exception as e:
            self.send_response(400)
            self.send_header("Content-Type", "application/json")
            self.end_headers()
            self.wfile.write(json.dumps({"success": False, "error": f"Invalid JSON: {str(e)}"}).encode('utf-8'))
            return

        try:
            conn = sqlite3.connect(DB_PATH)
            cursor = conn.cursor()

            # Enable transactions
            cursor.execute("BEGIN TRANSACTION")

            # 1. Update Profile
            profile = data.get("profile", {})
            cursor.execute("""
                UPDATE profile SET 
                    first_name = ?, last_name = ?, title = ?, birth_date = ?, birth_place = ?,
                    identity_document = ?, cuit = ?, nationality = ?, civil_status = ?, address = ?,
                    phones = ?, emails = ?, linkedin = ?, website = ?, association_member = ?, metadata = ?
                WHERE id = 1
            """, (
                profile.get("first_name"), profile.get("last_name"), profile.get("title"),
                profile.get("birth_date"), profile.get("birth_place"), profile.get("identity_document"),
                profile.get("cuit"), profile.get("nationality"), profile.get("civil_status"),
                profile.get("address"), json.dumps(profile.get("phones", [])), json.dumps(profile.get("emails", [])),
                profile.get("linkedin"), profile.get("website"), profile.get("association_member"),
                json.dumps(profile.get("metadata", {}))
            ))

            # 2. Sync Experiences (Clear & Re-insert)
            cursor.execute("DELETE FROM experiences")
            for idx, exp in enumerate(data.get("experiences", [])):
                cursor.execute("""
                    INSERT INTO experiences (
                        organization, organization_url, role, experience_type, start_date, end_date, is_current, description, reference_name, reference_contact, display_order
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    exp.get("organization"), exp.get("organization_url"), exp.get("role"),
                    exp.get("experience_type"), exp.get("start_date"), exp.get("end_date"),
                    int(exp.get("is_current", 0)), exp.get("description"), exp.get("reference_name"),
                    exp.get("reference_contact"), idx + 1
                ))

            # 3. Sync Education
            cursor.execute("DELETE FROM education")
            for idx, edu in enumerate(data.get("education", [])):
                cursor.execute("""
                    INSERT INTO education (
                        degree, institution, end_date, status, gpa, thesis_title, thesis_abstract, display_order
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    edu.get("degree"), edu.get("institution"), edu.get("end_date"),
                    edu.get("status"), edu.get("gpa"), edu.get("thesis_title"),
                    edu.get("thesis_abstract"), idx + 1
                ))

            # 4. Sync Lectures
            cursor.execute("DELETE FROM lectures")
            for idx, lec in enumerate(data.get("lectures", [])):
                cursor.execute("""
                    INSERT INTO lectures (
                        title, event_name, lecture_date, scope, description, display_order
                    ) VALUES (?, ?, ?, ?, ?, ?)
                """, (
                    lec.get("title"), lec.get("event_name"), lec.get("lecture_date"),
                    lec.get("scope"), lec.get("description"), idx + 1
                ))

            # 5. Sync Skills
            cursor.execute("DELETE FROM skills")
            for sk in data.get("skills", []):
                cursor.execute("""
                    INSERT INTO skills (name, category, level) VALUES (?, ?, ?)
                """, (
                    sk.get("name"), sk.get("category"), sk.get("level")
                ))

            conn.commit()
            conn.close()

            # Re-fetch and return the updated CV so the frontend can re-render
            self.handle_get_cv()

        except Exception as e:
            self.send_response(500)
            self.send_header("Content-Type", "application/json")
            self.end_headers()
            self.wfile.write(json.dumps({"success": False, "error": str(e)}).encode('utf-8'))

# Ensure static directory exists
os.makedirs(STATIC_DIR, exist_ok=True)

class ThreadingTCPServer(socketserver.ThreadingTCPServer):
    allow_reuse_address = True

if __name__ == "__main__":
    print(f"Starting server locally at http://localhost:{PORT}")
    with ThreadingTCPServer(("", PORT), CVRequestHandler) as httpd:
        try:
            httpd.serve_forever()
        except KeyboardInterrupt:
            print("\nShutting down server.")