import os
import time
import json
import uuid
from typing import Any, Dict, Optional
from .db import get_connection
import logging
from datetime import datetime

logger = logging.getLogger(__name__)

# Path optimization helper functions
def get_project_root() -> str:
    """Get the project root path from environment variable."""
    root_path = os.getenv('PROJECT_ROOT_PATH', os.getcwd())
    return os.path.abspath(root_path)

def get_generated_folder_path() -> str:
    """Get the path to the generated folder."""
    return os.path.join(get_project_root(), 'templates', 'generated')

def get_relative_folder_name(full_path: str) -> str:
    """Extract folder name from full path."""
    if not full_path:
        return ""
    # Get the last part of the path (folder name)
    return os.path.basename(os.path.normpath(full_path))

def get_relative_zip_name(full_zip_path: str) -> str:
    """Extract zip name from full zip path."""
    if not full_zip_path:
        return ""
    # Get the last part of the path (zip filename)
    return os.path.basename(full_zip_path)

def reconstruct_full_path(folder_name: str) -> str:
    """Reconstruct full path from folder name."""
    if not folder_name:
        return ""
    return os.path.join(get_generated_folder_path(), folder_name)

def reconstruct_full_zip_path(zip_name: str) -> str:
    """Reconstruct full zip path from zip name."""
    if not zip_name:
        return ""
    return os.path.join(get_project_root(), 'downloads', 'zips', zip_name)

# Timestamp formatting helper functions
def format_timestamp(timestamp: Optional[float]) -> Optional[str]:
    """Convert Unix timestamp to human-readable format."""
    if timestamp is None:
        return None
    try:
        return datetime.fromtimestamp(timestamp).strftime('%Y-%m-%d %H:%M:%S')
    except (ValueError, OSError):
        return None

def format_timestamp_iso(timestamp: Optional[float]) -> Optional[str]:
    """Convert Unix timestamp to ISO format."""
    if timestamp is None:
        return None
    try:
        return datetime.fromtimestamp(timestamp).isoformat()
    except (ValueError, OSError):
        return None

def add_formatted_timestamps(data: Dict[str, Any]) -> Dict[str, Any]:
    """Add human-readable timestamp fields to data dictionary."""
    if not data:
        return data
    
    # List of timestamp fields to format
    timestamp_fields = [
        'created_at', 'generated_at', 'last_click_at', 'last_download_at'
    ]
    
    for field in timestamp_fields:
        if field in data:
            # Add formatted version
            data[f'{field}_formatted'] = format_timestamp(data[field])
            data[f'{field}_iso'] = format_timestamp_iso(data[field])
    
    return data

def init_db() -> None:
    """Create required MySQL tables if they don't exist."""
    conn = get_connection()
    try:
        cur = conn.cursor()
        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS email_events (
                id INT PRIMARY KEY AUTO_INCREMENT,
                token VARCHAR(64) NOT NULL UNIQUE,
                email VARCHAR(320) NOT NULL,
                site_name VARCHAR(255) NOT NULL,
                zip_path TEXT NOT NULL,
                created_at DOUBLE NOT NULL,
                generated_at DOUBLE,
                sent_status VARCHAR(50),
                sent_error TEXT,
                download_count INT NOT NULL DEFAULT 0,
                click_count INT NOT NULL DEFAULT 0,
                last_click_at DOUBLE,
                last_download_at DOUBLE,
                INDEX idx_token (token)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            """
        )
        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS email_event_logs (
                id INT PRIMARY KEY AUTO_INCREMENT,
                log_id VARCHAR(64) NOT NULL UNIQUE,
                token VARCHAR(64),
                event_type VARCHAR(100) NOT NULL,
                created_at DOUBLE NOT NULL,
                details TEXT,
                INDEX idx_log_id (log_id),
                INDEX idx_token (token)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            """
        )
        conn.commit()
    finally:
        try:
            cur.close()
        except Exception:
            pass

def _connect():
    # wrapper for compatibility; callers expect a connection-like object
    return get_connection()

def _row_to_dict(row: Optional[Any]) -> Optional[Dict[str, Any]]:
    if row is None:
        return None
    return {key: row[key] for key in row.keys()}

def log_event(token: str, event_type: str, details: Optional[Dict[str, Any]] = None) -> None:
    """Insert a detailed event log row. Best-effort; do not crash on errors."""
    try:
        conn = _connect()
        cur = conn.cursor()
        cur.execute(
            "INSERT INTO email_event_logs (log_id, token, event_type, created_at, details) VALUES (%s, %s, %s, %s, %s)",
            (
                uuid.uuid4().hex,
                token,
                event_type,
                time.time(),
                json.dumps(details or {})
            ),
        )
        conn.commit()
    except Exception as e:
        logger.warning(f"Failed to write event log: {e}")
    finally:
        try:
            cur.close()
        except Exception:
            pass

def create_email_event(
    *,
    email: str,
    site_name: str,
    zip_path: str,
    generated_at: float,
    # max_downloads: int = 3,  -- DISABLED: Max download limit removed
) -> str:
    """Create a new email event row and return its unique token."""
    token = uuid.uuid4().hex
    try:
        conn = _connect()
        cur = conn.cursor()
        # Store only zip name instead of full path
        zip_name = get_relative_zip_name(zip_path)
        
        cur.execute(
            (
                "INSERT INTO email_events (token, email, site_name, zip_path, created_at, generated_at, sent_status) "
                "VALUES (%s, %s, %s, %s, %s, %s, %s)"
            ),
            (
                token,
                email,
                site_name,
                zip_name,     # Store only zip name
                time.time(),
                generated_at,
                'pending',
            ),
        )
        conn.commit()
    finally:
        try:
            cur.close()
        except Exception:
            pass
    return token

def mark_email_sent(token: str, success: bool, error: Optional[str] = None) -> None:
    try:
        conn = _connect()
        cur = conn.cursor()
        cur.execute(
            "UPDATE email_events SET sent_status = %s, sent_error = %s WHERE token = %s",
            ("success" if success else "failure", error, token),
        )
        conn.commit()
    finally:
        try:
            cur.close()
        except Exception:
            pass

def increment_click(token: str) -> Optional[Dict[str, Any]]:
    row = None
    try:
        conn = _connect()
        cur = conn.cursor()
        cur.execute(
            "UPDATE email_events SET click_count = click_count + 1, last_click_at = %s WHERE token = %s",
            (time.time(), token),
        )
        cur.execute("SELECT * FROM email_events WHERE token = %s", (token,))
        row = cur.fetchone()
        conn.commit()
    finally:
        try:
            cur.close()
        except Exception:
            pass
    
    result = _row_to_dict(row)
    if result:
        # Reconstruct full zip path from stored zip name (same as get_event function)
        result['zip_path'] = reconstruct_full_zip_path(result.get('zip_path', ''))
        # Add formatted timestamps for better readability
        result = add_formatted_timestamps(result)
    return result

def get_event(token: str) -> Optional[Dict[str, Any]]:
    try:
        conn = _connect()
        cur = conn.cursor()
        cur.execute("SELECT * FROM email_events WHERE token = %s", (token,))
        row = cur.fetchone()
        if row:
            result = _row_to_dict(row)
            # Reconstruct full zip path from stored zip name
            if result:
                result['zip_path'] = reconstruct_full_zip_path(result.get('zip_path', ''))
                # Add formatted timestamps for better readability
                result = add_formatted_timestamps(result)
            return result
        return None
    finally:
        try:
            cur.close()
        except Exception:
            pass

# def has_reached_limit(event: Dict[str, Any]) -> bool:
#     """DISABLED: Max download limit functionality removed"""
#     try:
#         return int(event.get("download_count", 0)) >= int(event.get("max_downloads", 3))
#     except Exception:
#         return True

def record_download(token: str) -> None:
    try:
        conn = _connect()
        cur = conn.cursor()
        cur.execute(
            "UPDATE email_events SET download_count = download_count + 1, last_download_at = %s WHERE token = %s",
            (time.time(), token),
        )
        conn.commit()
    finally:
        try:
            cur.close()
        except Exception:
            pass

def build_download_link(host: str, token: str) -> str:
    return f"{host}/dl/{token}"