"""
SQLAlchemy database models and engine configuration.

Defines all ORM models (tables) used by the Workstatus desktop agent and
configures the SQLite async engine with WAL mode for concurrent access.

Key exports:
    engine:       Async SQLAlchemy engine (sqlite+aiosqlite).
    async_session: Session factory (async_sessionmaker).
    Base:         Declarative base for all ORM models.
    UserDetail, Screenshots, OrgConfigurations, Appmonitor, AppConfig,
    ActivityInterval, Notes, FixedMode, ActivityPercentage: ORM model classes.
    create_all_tables(): Async coroutine to create/verify all tables.

Thread-safety: The engine and session factory are safe to share across threads.
    Each coroutine should open its own session via async_session().
"""

import os
import logging as _stdlib_logging
from datetime import datetime
from typing import Optional
import asyncio, sys

from sqlalchemy.ext.asyncio import AsyncAttrs
from sqlalchemy.ext.asyncio import async_sessionmaker
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped

from sqlalchemy import Column
from sqlalchemy.sql import sqltypes
from sqlalchemy import event, UniqueConstraint
from sqlalchemy.pool import StaticPool
from ws_package.utils.encryption import get_encryption_util
from ws_package.log.writer import LogWriter

import platform
from pathlib import Path
import sqlite3

logger = LogWriter.file(__name__)

current_dir = os.path.dirname(os.path.abspath(__file__))
parent_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))


if platform.system() == "Darwin":
    # macOS: Use Library/Application Support/Workstatus/
    db_path = Path.home() / "Library/Application Support/Workstatus/ws_monitor.db"
elif platform.system() == "Linux":
    # Linux: Use home/Workstatus/
    db_path = Path.home() / "Workstatus/ws_monitor.db"
else:
    # Windows: Use AppData/Roaming/Workstatus
    db_path = Path.home() / "AppData/Roaming/Workstatus/ws_monitor.db"

engine = create_async_engine(
    f"sqlite+aiosqlite:///{db_path}",
    connect_args={
        "timeout": 30,
        "check_same_thread": False,
    },
    echo=False,
)

# Enable WAL mode and SQLite optimizations for better concurrency
# This is applied to all new database connections automatically
@event.listens_for(engine.sync_engine, "connect")
def set_sqlite_pragmas(dbapi_conn, connection_record):
    """
    Set SQLite pragmas for better concurrency and performance.
    WAL (Write-Ahead Logging) mode allows multiple readers and one writer simultaneously.
    This eliminates most database lock issues while maintaining data integrity.
    """
    cursor = dbapi_conn.cursor()
    try:
        # Enable WAL mode - CRITICAL for concurrent access
        # WAL allows readers to continue while writer is active
        cursor.execute("PRAGMA journal_mode=WAL")
        
        # Set synchronous mode to NORMAL (balance between safety and performance)
        # NORMAL is safe because WAL mode provides durability
        cursor.execute("PRAGMA synchronous=NORMAL")
        
        # Set cache size to 32MB for better performance (negative = KB)
        cursor.execute("PRAGMA cache_size=-32000")
        
        # Use memory for temporary tables (faster than disk)
        cursor.execute("PRAGMA temp_store=memory")
        
        # Enable memory-mapped I/O (256MB) for better performance on large databases
        cursor.execute("PRAGMA mmap_size=268435456")
        
        # Increase busy timeout to 30 seconds (handles concurrent access better)
        cursor.execute("PRAGMA busy_timeout=30000")
        
        # Enable foreign keys (good practice, doesn't affect locks)
        cursor.execute("PRAGMA foreign_keys=ON")
        
        cursor.close()
    except Exception as e:
        # If pragmas can't be set, log warning but don't fail.
        # This ensures backward compatibility if the database is in use.
        _stdlib_logging.warning("Failed to set SQLite pragmas (database may be in use): %s", e)
        try:
            cursor.close()
        except Exception:
            # Fallback if logging isn't available - use built-in logging
            import logging
            logging.warning(f"Failed to set SQLite pragmas: {e}")
        finally:
            try:
                cursor.close()
            except Exception:
                pass


async_session = async_sessionmaker(
    engine, expire_on_commit=False
)

class Base(AsyncAttrs, DeclarativeBase):
    pass


class UserDetail(Base):
    """
    User detail storage (table renamed from userdetail to user_details)
    """
    __tablename__ = "user_details"

    id: Mapped[int] = Column(sqltypes.Integer, primary_key=True)
    username: Mapped[str] = Column(sqltypes.String)
    name: Mapped[str] = Column(sqltypes.String)
    user_id: Mapped[int] = Column(sqltypes.Integer)
    timezone: Mapped[str] = Column(sqltypes.String)
    token: Mapped[str] = Column(sqltypes.String)
    org_id: Mapped[int] = Column(sqltypes.Integer)
    sd_token: Mapped[int] = Column(sqltypes.Integer)
    bucket: Mapped[str] = Column(sqltypes.String)
    timezone_preference: Mapped[int] = Column(sqltypes.Integer, default=1)
    timestamp: Mapped[datetime] = Column(sqltypes.DateTime, default=datetime.now)

class Screenshots(Base):
    __tablename__ = 'screenshots'

    id: Mapped[int] = Column(sqltypes.Integer, primary_key=True)
    file_name: Mapped[str] = Column(sqltypes.String)
    file_location: Mapped[str] = Column(sqltypes.String)
    file_size: Mapped[str] = Column(sqltypes.String)
    s3_upload_link: Mapped[str] = Column(sqltypes.String)
    is_uploaded: Mapped[bool] = Column(sqltypes.Boolean, default=False)
    blur: Mapped[bool] = Column(sqltypes.Boolean, default=False)
    timestamp: Mapped[datetime] = Column(sqltypes.DateTime, default=datetime.now)
    is_s3_uploaded: Mapped[bool] = Column(sqltypes.Boolean, default=False)
    counter: Mapped[int] = Column(sqltypes.Integer)

class OrgConfigurations(Base):
    """
    Organization configuration storage (table renamed from orgconfiguration to org_configuration)
    """
    __tablename__ = 'org_configuration'

    id: Mapped[int] = Column(sqltypes.Integer, primary_key=True)
    org_id: Mapped[int] = Column(sqltypes.Integer)
    user_id: Mapped[int] = Column(sqltypes.Integer)
    screenshot_interval: Mapped[int] = Column(sqltypes.Integer)
    date: Mapped[str] = Column(sqltypes.String)
    appandurlstatus: Mapped[int] = Column(sqltypes.Integer)
    ssfrequencystatus: Mapped[int] = Column(sqltypes.Integer)
    screenshotblurstatus: Mapped[int] = Column(sqltypes.Integer)
    livescreenshotstatus: Mapped[int] = Column(sqltypes.Integer)
    idlenotes_status: Mapped[int] = Column(sqltypes.Integer)
    break_limit: Mapped[str] = Column(sqltypes.String)
    break_status: Mapped[int] = Column(sqltypes.Integer)
    notes_char_limit: Mapped[str] = Column(sqltypes.String)
    idle_limit: Mapped[str] = Column(sqltypes.String)
    can_delete_scrnshots: Mapped[int] = Column(sqltypes.Integer)
    restrict_timer_to_jobsite: Mapped[int] = Column(sqltypes.Integer)
    todos_files_upload_limit: Mapped[int] = Column(sqltypes.Integer)
    todo_file_size_limit: Mapped[int] = Column(sqltypes.Integer)
    org_timezone: Mapped[str] = Column(sqltypes.String)
    user_timezone: Mapped[str] = Column(sqltypes.String)
    user_timezone_gmt: Mapped[str] = Column(sqltypes.String)
    user_timezone_gmt_dst: Mapped[str] = Column(sqltypes.String)
    user_timezone_gmt_shortcode: Mapped[str] = Column(sqltypes.String)
    bulkInvite: Mapped[bool] = Column(sqltypes.Boolean, default=False)
    timeInterval: Mapped[str] = Column(sqltypes.String)
    blur_intensity: Mapped[int] = Column(sqltypes.Integer, default=5)
    img_quality: Mapped[int] = Column(sqltypes.Integer, default=50)
    notification_status: Mapped[int] = Column(sqltypes.Integer, default=1)
    # Weekly limit data - store full object as JSON and individual fields for quick access
    weekly_limit: Mapped[Optional[dict]] = Column(sqltypes.JSON, nullable=True)
    org_week_start_day: Mapped[Optional[str]] = Column(sqltypes.String, nullable=True)
    worked_total_hours_week: Mapped[Optional[str]] = Column(sqltypes.String, nullable=True)
    worked_total_hours_week_leaving_today: Mapped[Optional[str]] = Column(sqltypes.String, nullable=True)
    daily_limit: Mapped[Optional[str]] = Column(sqltypes.String, nullable=True)
    weekly_limit_hours: Mapped[Optional[str]] = Column(sqltypes.String, nullable=True)
    worked_total_hours_today: Mapped[Optional[str]] = Column(sqltypes.String, nullable=True)
    limit_action: Mapped[Optional[str]] = Column(sqltypes.String, nullable=True)  # "just_notify" or "stop_timer"
    idle_time_limit_in_seconds: Mapped[Optional[int]] = Column(sqltypes.Integer, nullable=True, default=3600)
    # 0 = timezone/time checks ON, 1 = checks OFF (from organization settings API)
    timezone_preference: Mapped[int] = Column(sqltypes.Integer, default=1)
    timestamp: Mapped[datetime] = Column(sqltypes.DateTime, default=datetime.now)

class Appmonitor(Base):
    """
    Application monitoring data (table renamed from Appmonitor to app_monitor)
    """
    __tablename__ = "app_monitor"

    id: Mapped[int] = Column(sqltypes.Integer, primary_key=True)
    spendTime: Mapped[int] = Column(sqltypes.Integer, nullable=False)  # Changed to match "spendTime"
    active_count: Mapped[int] = Column(sqltypes.Integer, nullable=False)  # Added field to match "active_count"
    name: Mapped[str] = Column(sqltypes.String, nullable=False)  # Changed "app_name" to "name"
    url: Mapped[str] = Column(sqltypes.String, nullable=True)
    isApp: Mapped[bool] = Column(sqltypes.Boolean, default=False)  
    timestamp: Mapped[datetime] = Column(sqltypes.DateTime, default=datetime.now)
    isUploaded: Mapped[bool] = Column(sqltypes.Boolean, default=False)
    counter: Mapped[int] = Column(sqltypes.Integer, nullable=False)  # Added field to match "counter"
    timezone: Mapped[bool] = Column(sqltypes.Boolean, default=False)

class AppConfig(Base):
    """
    Application configuration storage (renamed from S3details)
    Stores encrypted sensitive configuration data like S3 credentials
    """
    __tablename__ = "app_config"

    id: Mapped[int] = Column(sqltypes.Integer, primary_key=True)
    org_id: Mapped[int] = Column(sqltypes.Integer)
    access_key: Mapped[str] = Column(sqltypes.String)  # Encrypted
    secret_access_key: Mapped[str] = Column(sqltypes.String)  # Encrypted
    bucket: Mapped[str] = Column(sqltypes.String)  # Encrypted
    region: Mapped[str] = Column(sqltypes.String)  # Encrypted
    session_token: Mapped[str] = Column(sqltypes.String, nullable=True)  # Encrypted STS token
    expire_time: Mapped[str] = Column(sqltypes.String, nullable=True)  # Plaintext expiry for refresh checks
    timestamp: Mapped[datetime] = Column(sqltypes.DateTime, default=datetime.now)
    
    # Backward compatibility alias
    @classmethod
    def __class_getitem__(cls, item):
        """Allow S3details to still work as alias"""
        return cls


# Backward compatibility alias
S3details = AppConfig


# Encryption/Decryption event handlers for AppConfig
# Must be defined after AppConfig class definition
@event.listens_for(AppConfig, "before_insert", propagate=True)
@event.listens_for(AppConfig, "before_update", propagate=True)
def encrypt_app_config_data(mapper, connection, target):
    """
    Encrypt sensitive fields before inserting/updating AppConfig records
    """
    try:
        encryption_util = get_encryption_util()
        
        # Encrypt sensitive fields if they're not already encrypted
        # Check if already encrypted by looking for salt delimiter
        if target.access_key and '$' not in target.access_key:
            target.access_key = encryption_util.encrypt(target.access_key)
        
        if target.secret_access_key and '$' not in target.secret_access_key:
            target.secret_access_key = encryption_util.encrypt(target.secret_access_key)
        
        if target.bucket and '$' not in target.bucket:
            target.bucket = encryption_util.encrypt(target.bucket)
        
        if target.region and '$' not in target.region:
            target.region = encryption_util.encrypt(target.region)

        if target.session_token and '$' not in str(target.session_token):
            target.session_token = encryption_util.encrypt(str(target.session_token))
            
    except Exception as e:
        logger.error("Failed to encrypt AppConfig data before insert/update: %s", e, exc_info=True)


@event.listens_for(AppConfig, "load", propagate=True)
def _decrypt_app_config_fields(target, context=None):
    """
    Registered as SQLAlchemy load event — fires on SELECT results.
    Decrypts sensitive AppConfig fields after loading from database.

    ``context`` is provided by SQLAlchemy for the load listener; optional when
    calling manually (e.g. from repositories after a query).
    """
    try:
        encryption_util = get_encryption_util()
        
        # Decrypt sensitive fields
        if target.access_key:
            try:
                # Only decrypt if it looks encrypted (contains salt delimiter)
                if '$' in str(target.access_key):
                    target.access_key = encryption_util.decrypt(str(target.access_key))
                    logger.debug("Successfully decrypted access_key")
            except Exception as e:
                # If decryption fails, log but keep original value
                logger.warning("Failed to decrypt access_key (may be plaintext or invalid format): %s", e)
                # Keep original value - might be plaintext from before migration
        
        if target.secret_access_key:
            try:
                if '$' in str(target.secret_access_key):
                    target.secret_access_key = encryption_util.decrypt(str(target.secret_access_key))
                    logger.debug("Successfully decrypted secret_access_key")
            except Exception as e:
                logger.warning("Failed to decrypt secret_access_key (may be plaintext or invalid format): %s", e)
        
        if target.bucket:
            try:
                if '$' in str(target.bucket):
                    target.bucket = encryption_util.decrypt(str(target.bucket))
                    logger.debug("Successfully decrypted bucket")
            except Exception as e:
                logger.warning("Failed to decrypt bucket (may be plaintext or invalid format): %s", e)
        
        if target.region:
            try:
                if '$' in str(target.region):
                    target.region = encryption_util.decrypt(str(target.region))
                    logger.debug("Successfully decrypted region")
            except Exception as e:
                logger.warning("Failed to decrypt region (may be plaintext or invalid format): %s", e)

        if target.session_token:
            try:
                if '$' in str(target.session_token):
                    target.session_token = encryption_util.decrypt(str(target.session_token))
                    logger.debug("Successfully decrypted session_token")
            except Exception as e:
                logger.warning("Failed to decrypt session_token (may be plaintext or invalid format): %s", e)
                
    except Exception as e:
        logger.error("Error in _decrypt_app_config_fields: %s", e, exc_info=True)


class ActivityInterval(Base):
    __tablename__ = "activity_interval"
    __table_args__ = (
        UniqueConstraint("start_time", "end_time", name="uq_activity_interval_start_time_end_time"),
    )
    id: Mapped[int] = Column(sqltypes.Integer, primary_key=True)
    start_time: Mapped[datetime] = Column(sqltypes.DateTime)
    end_time: Mapped[datetime] = Column(sqltypes.DateTime)
    activityLevel: Mapped[dict] = Column(sqltypes.JSON)
    is_uploaded: Mapped[bool] = Column(sqltypes.Boolean)
    counter: Mapped[int] = Column(sqltypes.Integer)
    weekday: Mapped[str] = Column(sqltypes.String)
    is_break: Mapped[int] = Column(sqltypes.Integer, default=0)
    is_idle: Mapped[bool] = Column(sqltypes.Boolean, default=False)
    has_on_demand_screenshot: Mapped[bool] = Column(sqltypes.Boolean, default=False)

class Notes(Base):
    """
    Notes table to store notes associated with multiple activity intervals.
    One note can be associated with multiple idle intervals.
    """
    __tablename__ = "notes"
    
    id: Mapped[int] = Column(sqltypes.Integer, primary_key=True)
    activity_interval_id: Mapped[list] = Column(sqltypes.JSON)  # Array of interval IDs
    note: Mapped[str] = Column(sqltypes.String)
    file_path: Mapped[str] = Column(sqltypes.String, nullable=True)
    timestamp: Mapped[datetime] = Column(sqltypes.DateTime, default=datetime.now)


class FixedMode(Base):
    __tablename__ = 'fixed_Mode'
    id: Mapped[int] = Column(sqltypes.Integer, primary_key=True)
    limit_start: Mapped[str] = Column(sqltypes.String, default="00:00:00")
    limit_end: Mapped[str] = Column(sqltypes.String, default="23:59:59")
    day_list: Mapped[list[str]] = Column(sqltypes.JSON)
    mode: Mapped[int] = Column(sqltypes.Integer)
    tracking: Mapped[int] = Column(sqltypes.Integer)

class ActivityPercentage(Base):
    __tablename__ = 'activity_percentage'
    id: Mapped[int] = Column(sqltypes.Integer, primary_key=True)
    start: Mapped[datetime] = Column(sqltypes.DateTime)
    stop: Mapped[datetime] = Column(sqltypes.DateTime)
    keyboard: Mapped[float] = Column(sqltypes.Float)
    mouse: Mapped[float] = Column(sqltypes.Float)
    average: Mapped[float] = Column(sqltypes.Float)
    minute: Mapped[int] = Column(sqltypes.Integer)


async def _ensure_activity_interval_unique_index(con) -> None:
    """
    Ensure SQLite enforces unique start/end pairs for activity intervals.

    For existing databases, we first remove duplicate rows so the unique index
    can be created successfully without breaking ongoing startup flow.
    """

    try:
        duplicate_result = await con.exec_driver_sql(
            """
            WITH ranked AS (
                SELECT
                    id,
                    ROW_NUMBER() OVER (
                        PARTITION BY start_time, end_time
                        ORDER BY
                            CASE WHEN is_uploaded = 0 THEN 0 ELSE 1 END,
                            id ASC
                    ) AS rn
                FROM activity_interval
            )
            DELETE FROM activity_interval
            WHERE id IN (
                SELECT id
                FROM ranked
                WHERE rn > 1
            )
            """
        )
        if duplicate_result.rowcount and duplicate_result.rowcount > 0:
            logger.warning(
                f"Removed {duplicate_result.rowcount} duplicate activity_interval row(s) before applying unique start/end constraint"
            )
    except Exception as e:
        logger.warning("Could not clean duplicate activity_interval rows before unique index creation: %s", e)

    try:
        index_list_result = await con.exec_driver_sql("PRAGMA index_list('activity_interval')")
        existing_indexes = index_list_result.fetchall()
        has_start_end_unique_index = False

        for index_row in existing_indexes:
            index_name = index_row[1]
            is_unique = bool(index_row[2])
            if not is_unique:
                continue

            index_info_result = await con.exec_driver_sql(f"PRAGMA index_info('{index_name}')")
            indexed_columns = [row[2] for row in index_info_result.fetchall()]
            if indexed_columns == ["start_time", "end_time"]:
                has_start_end_unique_index = True
                break

        if not has_start_end_unique_index:
            await con.exec_driver_sql(
                """
                CREATE UNIQUE INDEX IF NOT EXISTS ux_activity_interval_start_time_end_time
                ON activity_interval (start_time, end_time)
                """
            )
            logger.info("Created unique index on activity_interval(start_time, end_time)")
        else:
            logger.info("Unique index on activity_interval(start_time, end_time) already exists")
    except Exception as e:
        logger.error("Failed to ensure unique index on activity_interval(start_time, end_time): %s", e, exc_info=True)


async def _ensure_app_config_session_columns(con) -> None:
    """Add STS session columns to app_config when upgrading an existing database."""
    try:
        def _migrate(connection):
            import sqlalchemy as sa

            inspector = sa.inspect(connection)
            tables = inspector.get_table_names()
            if "app_config" not in tables:
                return
            columns = {col["name"] for col in inspector.get_columns("app_config")}
            if "session_token" not in columns:
                connection.execute(sa.text("ALTER TABLE app_config ADD COLUMN session_token VARCHAR"))
                logger.info("Added session_token column to app_config")
            if "expire_time" not in columns:
                connection.execute(sa.text("ALTER TABLE app_config ADD COLUMN expire_time VARCHAR"))
                logger.info("Added expire_time column to app_config")

        await con.run_sync(_migrate)
    except Exception as e:
        logger.warning("Could not ensure app_config session columns: %s", e)


async def _ensure_user_timezone_preference_column(con) -> None:
    """Add timezone_preference when upgrading an existing Python database."""
    try:
        def _migrate(connection):
            import sqlalchemy as sa

            inspector = sa.inspect(connection)
            if "user_details" not in inspector.get_table_names():
                return
            columns = {col["name"] for col in inspector.get_columns("user_details")}
            if "timezone_preference" not in columns:
                connection.execute(
                    sa.text(
                        "ALTER TABLE user_details "
                        "ADD COLUMN timezone_preference INTEGER DEFAULT 1"
                    )
                )
                logger.info("Added timezone_preference column to user_details")

        await con.run_sync(_migrate)
    except Exception as e:
        logger.warning("Could not ensure user timezone preference column: %s", e)


async def _ensure_org_timezone_preference_column(con) -> None:
    """Add timezone_preference to org_configuration when upgrading an existing DB."""
    try:
        def _migrate(connection):
            import sqlalchemy as sa

            inspector = sa.inspect(connection)
            if "org_configuration" not in inspector.get_table_names():
                return
            columns = {col["name"] for col in inspector.get_columns("org_configuration")}
            if "timezone_preference" not in columns:
                connection.execute(
                    sa.text(
                        "ALTER TABLE org_configuration "
                        "ADD COLUMN timezone_preference INTEGER DEFAULT 1"
                    )
                )
                logger.info("Added timezone_preference column to org_configuration")

        await con.run_sync(_migrate)
    except Exception as e:
        logger.warning("Could not ensure org timezone preference column: %s", e)


async def create_all_tables() -> None:
    """Create all database tables. Safe to call multiple times."""
    try:
        async with engine.begin() as con:
            await con.run_sync(Base.metadata.create_all)
            await _ensure_activity_interval_unique_index(con)
            await _ensure_app_config_session_columns(con)
            await _ensure_user_timezone_preference_column(con)
            await _ensure_org_timezone_preference_column(con)
        # Don't dispose engine - it's shared across the application
        logger.info("Database tables created/verified successfully")
    except Exception as e:
        # Check if error is "table already exists" - this is OK, tables are already there
        error_str = str(e).lower()
        if "already exists" in error_str or ("table" in error_str and "exists" in error_str):
            logger.info("Database tables already exist — normal on subsequent runs")
            return  # Don't raise - this is expected

        # For other errors, log and raise
        logger.error("Failed to create database tables: %s", e, exc_info=True)
        raise

async def main() -> None:
    """Create all database tables. Run this explicitly when needed."""
    await create_all_tables()
    await engine.dispose()

# Only run table creation if this file is executed directly, not when imported
if __name__ == "__main__":
    asyncio.run(main())
