"""
SQLite client & helpers for CuCu backend.
MongoDB-like interface for SQLite backend.
"""

from __future__ import annotations

import json
import logging
import os
import re
from datetime import datetime, timezone
from typing import Any, AsyncIterator

import aiosqlite

logger = logging.getLogger(__name__)

# Database path từ config
from config import MEMO_DB_PATH

# Table names với prefix cuccu_
TABLE_USERS = "cuccu_users"
TABLE_MEMOS = "cuccu_memos"
TABLE_ATTACHMENTS = "cuccu_attachments"
TABLE_MEMO_RELATIONS = "cuccu_memo_relations"
TABLE_REACTIONS = "cuccu_reactions"
TABLE_MEMO_EMBEDDINGS = "cuccu_memo_embeddings"
TABLE_INBOX = "cuccu_inbox"
TABLE_USER_SETTINGS = "cuccu_user_settings"
TABLE_SHORTCUTS = "cuccu_shortcuts"
TABLE_TEAMS = "cuccu_teams"
TABLE_TEAM_MEMBERS = "cuccu_team_members"
TABLE_TEAM_MEMOS = "cuccu_team_memos"
TABLE_TEAM_COMMENTS = "cuccu_team_comments"
TABLE_TEAM_REACTIONS = "cuccu_team_reactions"
TABLE_USER_PROFILES = "cuccu_user_profiles"
TABLE_REFRESH_TOKENS = "cuccu_refresh_tokens"
TABLE_ACTIVITIES = "cuccu_activities"
TABLE_NOTIFICATIONS = "cuccu_notifications"
TABLE_MEMO_VERSIONS = "cuccu_memo_versions"
TABLE_PERSONAL_ACCESS_TOKENS = "cuccu_personal_access_tokens"
TABLE_WEBHOOKS = "cuccu_webhooks"
TABLE_DOCUMENTS = "cuccu_documents"
TABLE_CHAT_HISTORY = "cuccu_chat_history"


class SQLiteClient:
    """Singleton SQLite client cho toàn backend CuCu."""
    _instance: "SQLiteClient | None" = None
    _db: aiosqlite.Connection | None = None

    def __new__(cls) -> "SQLiteClient":
        if cls._instance is None:
            cls._instance = super().__new__(cls)
            cls._instance._current_table = TABLE_MEMOS
        return cls._instance

    async def connect(self) -> None:
        if self._db is None:
            db_dir = os.path.dirname(MEMO_DB_PATH)
            if db_dir and not os.path.exists(db_dir):
                os.makedirs(db_dir, exist_ok=True)
            self._db = await aiosqlite.connect(MEMO_DB_PATH)
            self._db.row_factory = aiosqlite.Row
            await self._db.execute("PRAGMA foreign_keys = ON")
            await self._db.execute("PRAGMA journal_mode = WAL")
            await self._db.commit()
            logger.info(f"✅ SQLite Connected: {MEMO_DB_PATH}")

    async def close(self) -> None:
        if self._db:
            await self._db.close()
            self._db = None

    @property
    def db(self) -> aiosqlite.Connection:
        if self._db is None: raise RuntimeError("SQLite not connected.")
        return self._db

    @property
    def mongodb(self): return self

    def __getitem__(self, name):
        self._current_table = f"cuccu_{name}" if not name.startswith("cuccu_") else name
        return self

    def _normalize_query(self, query: str) -> str:
        base_tables = [
            "memos", "attachments", "memo_relations", "reactions",
            "memo_embeddings", "inbox", "user_settings", "shortcuts",
            "teams", "team_members", "team_memos", "team_comments",
            "team_reactions", "user_profiles", "refresh_tokens",
            "activities", "notifications", "memo_versions",
            "personal_access_tokens", "webhooks", "users", "documents", "chat_history"
        ]
        normalized = query
        for table in base_tables:
            pattern = r'\b' + re.escape(table) + r'\b'
            if f"cuccu_{table}" not in normalized:
                normalized = re.sub(pattern, f"cuccu_{table}", normalized)
        return normalized

    async def execute(self, query: str, params: tuple = ()) -> aiosqlite.Cursor:
        query = self._normalize_query(query)
        cursor = await self.db.execute(query, params)
        await self.db.commit()
        return cursor

    async def fetch_one(self, query: str, params: tuple = ()) -> aiosqlite.Row | None:
        query = self._normalize_query(query)
        async with self.db.execute(query, params) as cursor:
            return await cursor.fetchone()

    async def fetch_all(self, query: str, params: tuple = ()) -> list[aiosqlite.Row]:
        query = self._normalize_query(query)
        async with self.db.execute(query, params) as cursor:
            return await cursor.fetchall()

    async def find_one(self, query: dict, projection: dict | None = None) -> dict | None:
        table = self._current_table
        conditions = []
        params = []
        for k, v in query.items():
            if k == "_id": k = "id"
            conditions.append(f"{k} = ?")
            params.append(v)
        where = " AND ".join(conditions) if conditions else "1=1"
        sql = f"SELECT * FROM {table} WHERE {where} LIMIT 1"
        row = await self.fetch_one(sql, tuple(params))
        return dict(row) if row else None

    def find(self, query: dict, projection: dict | None = None) -> Any:
        table = self._current_table
        conditions = []
        params = []
        for k, v in query.items():
            if k == "_id": k = "id"
            if isinstance(v, dict):
                for op, val in v.items():
                    if op == "$in":
                        placeholders = ",".join(["?"] * len(val))
                        conditions.append(f"{k} IN ({placeholders})")
                        params.extend(val)
                    elif op == "$gte":
                        conditions.append(f"{k} >= ?")
                        params.append(val)
                    elif op == "$lt":
                        conditions.append(f"{k} < ?")
                        params.append(val)
                    elif op == "$lte":
                        conditions.append(f"{k} <= ?")
                        params.append(val)
                    elif op == "$gt":
                        conditions.append(f"{k} > ?")
                        params.append(val)
                    elif op == "$ne":
                        conditions.append(f"{k} != ?")
                        params.append(val)
            else:
                conditions.append(f"{k} = ?")
                params.append(v)
        
        where = " AND ".join(conditions) if conditions else "1=1"
        sql = f"SELECT * FROM {table} WHERE {where}"
        
        class AsyncCursor:
            def __init__(self, client, sql, params):
                self.client = client
                self.sql = sql
                self.params = params
                self._limit = None
                self._sort = None

            def sort(self, field, direction=-1):
                if isinstance(field, list):
                    self._sort = ", ".join([f"{f} {'DESC' if d == -1 else 'ASC'}" for f, d in field])
                else:
                    self._sort = f"{field} {'DESC' if direction == -1 else 'ASC'}"
                return self

            def limit(self, n):
                self._limit = n
                return self

            async def to_list(self, length=100):
                final_sql = self.sql
                if self._sort: final_sql += f" ORDER BY {self._sort}"
                limit_val = self._limit or length
                if limit_val: final_sql += f" LIMIT {limit_val}"
                rows = await self.client.fetch_all(final_sql, self.params)
                return [dict(r) for r in rows]

            def __aiter__(self): return self
            async def __anext__(self): raise StopAsyncIteration

        return AsyncCursor(self, sql, tuple(params))

    async def insert_one(self, doc: dict) -> Any:
        table = self._current_table
        data = {k: v for k, v in doc.items() if k != "_id"}
        cols = ", ".join(data.keys())
        placeholders = ", ".join(["?"] * len(data))
        sql = f"INSERT INTO {table} ({cols}) VALUES ({placeholders})"
        cursor = await self.execute(sql, tuple(data.values()))
        class Res: inserted_id = cursor.lastrowid
        return Res()

    async def insert_many(self, docs: list[dict]) -> Any:
        for doc in docs: await self.insert_one(doc)

    async def update_one(self, query: dict, update: dict, upsert=False) -> Any:
        table = self._current_table
        set_data = update.get("$set", {})
        if not set_data:
            class Res: modified_count = 0; matched_count = 0
            return Res()
        set_clauses = [f"{k} = ?" for k in set_data.keys()]
        params = list(set_data.values())
        where_clauses = []
        for k, v in query.items():
            if k == "_id": k = "id"
            where_clauses.append(f"{k} = ?")
            params.append(v)
        sql = f"UPDATE {table} SET {', '.join(set_clauses)} WHERE {' AND '.join(where_clauses)}"
        cursor = await self.execute(sql, tuple(params))
        class Res: modified_count = cursor.rowcount; matched_count = cursor.rowcount
        return Res()

    async def delete_many(self, query: dict) -> Any:
        table = self._current_table
        where_clauses = []
        params = []
        for k, v in query.items():
            if k == "_id": k = "id"
            where_clauses.append(f"{k} = ?")
            params.append(v)
        sql = f"DELETE FROM {table} WHERE {' AND '.join(where_clauses) if where_clauses else '1=1'}"
        cursor = await self.execute(sql, tuple(params))
        class Res: deleted_count = cursor.rowcount
        return Res()

    async def count_documents(self, query: dict) -> int:
        table = self._current_table
        where_clauses = []
        params = []
        for k, v in query.items():
            if k == "_id": k = "id"
            if isinstance(v, dict) and "$gte" in v:
                where_clauses.append(f"{k} >= ?")
                params.append(v["$gte"])
            else:
                where_clauses.append(f"{k} = ?")
                params.append(v)
        sql = f"SELECT COUNT(*) as cnt FROM {table} WHERE {' AND '.join(where_clauses) if where_clauses else '1=1'}"
        row = await self.fetch_one(sql, tuple(params))
        return row["cnt"] if row else 0

    async def distinct(self, field: str) -> list:
        table = self._current_table
        sql = f"SELECT DISTINCT {field} FROM {table}"
        rows = await self.fetch_all(sql)
        return [row[field] for row in rows]

    def aggregate(self, pipeline: list[dict]) -> Any:
        table = self._current_table
        match_q = {}
        group_q = {}
        for stage in pipeline:
            if "$match" in stage: match_q.update(stage["$match"])
            if "$group" in stage: group_q = stage["$group"]
        
        where_conds = []
        params = []
        for k, v in match_q.items():
            if isinstance(v, dict) and "$in" in v:
                placeholders = ",".join(["?"] * len(v["$in"]))
                where_conds.append(f"{k} IN ({placeholders})")
                params.extend(v["$in"])
            else:
                where_conds.append(f"{k} = ?")
                params.append(v)
        
        where = " AND ".join(where_conds) if where_conds else "1=1"
        group_field = "memo_id" # Default for our usage
        sql = f"SELECT {group_field} as _id, COUNT(*) as count FROM {table} WHERE {where} GROUP BY {group_field}"
        
        class AggCursor:
            def __init__(self, client, sql, params): self.client = client; self.sql = sql; self.params = params
            async def to_list(self, length=100):
                rows = await self.client.fetch_all(self.sql, self.params)
                return [dict(r) for r in rows]
            def __aiter__(self): return self
            async def __anext__(self): raise StopAsyncIteration
        
        return AggCursor(self, sql, tuple(params))

    async def create_index(self, *args, **kwargs):
        # We don't implement full index support via MongoDB API, 
        # indices are handled in init_sqlite
        pass

    @property
    def users(self): self._current_table = TABLE_USERS; return self
    @property
    def memos(self): self._current_table = TABLE_MEMOS; return self
    @property
    def teams(self): self._current_table = TABLE_TEAMS; return self
    @property
    def team_members(self): self._current_table = TABLE_TEAM_MEMBERS; return self
    @property
    def team_memos(self): self._current_table = TABLE_TEAM_MEMOS; return self
    @property
    def team_comments(self): self._current_table = TABLE_TEAM_COMMENTS; return self
    @property
    def team_reactions(self): self._current_table = TABLE_TEAM_REACTIONS; return self
    @property
    def shortcuts(self): self._current_table = TABLE_SHORTCUTS; return self
    @property
    def refresh_tokens(self): self._current_table = TABLE_REFRESH_TOKENS; return self
    @property
    def inbox(self): self._current_table = TABLE_INBOX; return self
    @property
    def user_profiles(self): self._current_table = TABLE_USER_PROFILES; return self
    @property
    def activities(self): self._current_table = TABLE_ACTIVITIES; return self
    @property
    def cached_user_profiles(self): self._current_table = TABLE_USER_PROFILES; return self

sqlite_client = SQLiteClient()

def generate_uid():
    import secrets
    return secrets.token_urlsafe(16)[:22]

def utc_now(): return datetime.now(timezone.utc)

def serialize_row(row):
    if not row: return None
    res = dict(row)
    for k, v in res.items():
        if isinstance(v, datetime): res[k] = v.isoformat()
    return res

def parse_time(v):
    if not v: return None
    if isinstance(v, datetime): return v
    try: return datetime.fromisoformat(str(v).replace("Z", "+00:00"))
    except: return None

def format_time(dt):
    if not dt: return None
    return dt.strftime("%Y-%m-%dT%H:%M:%SZ")

async def init_sqlite():
    await sqlite_client.connect()
    db = sqlite_client.db
    tables = [
        (TABLE_USERS, "id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT UNIQUE, password_hash TEXT, nickname TEXT, username TEXT UNIQUE, role TEXT DEFAULT 'user', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_MEMOS, "id INTEGER PRIMARY KEY AUTOINCREMENT, uid TEXT UNIQUE, creator_id TEXT, content TEXT, visibility TEXT DEFAULT 'PRIVATE', pinned BOOLEAN DEFAULT 0, row_status TEXT DEFAULT 'NORMAL', parent TEXT, payload TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, workspace_id TEXT DEFAULT 'PERSONAL', is_read BOOLEAN DEFAULT 0, is_completed BOOLEAN DEFAULT 0, completed_at TIMESTAMP, deadline TIMESTAMP, priority INTEGER DEFAULT 0, reminder_at TIMESTAMP, anonymous_id TEXT"),
        (TABLE_TEAMS, "id INTEGER PRIMARY KEY AUTOINCREMENT, owner_id TEXT, name TEXT, description TEXT, invite_code TEXT UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_TEAM_MEMBERS, "id INTEGER PRIMARY KEY AUTOINCREMENT, team_id INTEGER, user_id TEXT, role TEXT DEFAULT 'MEMBER', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(team_id, user_id)"),
        (TABLE_TEAM_MEMOS, "id INTEGER PRIMARY KEY AUTOINCREMENT, team_id INTEGER, creator_id TEXT, space TEXT, content TEXT, visibility TEXT DEFAULT 'PRIVATE', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_TEAM_COMMENTS, "id INTEGER PRIMARY KEY AUTOINCREMENT, memo_id TEXT, team_id INTEGER, creator_id TEXT, content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_TEAM_REACTIONS, "id INTEGER PRIMARY KEY AUTOINCREMENT, memo_id TEXT, user_id TEXT, emoji TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(memo_id, user_id, emoji)"),
        (TABLE_USER_PROFILES, "user_id TEXT PRIMARY KEY, username TEXT, first_name TEXT, last_name TEXT, avatar_url TEXT, display_name TEXT, email TEXT, cached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_ACTIVITIES, "id INTEGER PRIMARY KEY AUTOINCREMENT, type TEXT, creator_id TEXT, memo_id TEXT, related_memo_id TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_CHAT_HISTORY, "id INTEGER PRIMARY KEY, identity_key TEXT, message TEXT, is_human BOOLEAN, timestamp TIMESTAMP"),
        (TABLE_ATTACHMENTS, "id INTEGER PRIMARY KEY AUTOINCREMENT, uid TEXT UNIQUE, memo_id TEXT, file_name TEXT, file_type TEXT, file_size INTEGER, blob_id TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_MEMO_RELATIONS, "id INTEGER PRIMARY KEY AUTOINCREMENT, memo_id TEXT, related_memo_id TEXT, type TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_REACTIONS, "id INTEGER PRIMARY KEY AUTOINCREMENT, content_id TEXT, creator_id TEXT, reaction_type TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_MEMO_EMBEDDINGS, "id INTEGER PRIMARY KEY AUTOINCREMENT, memo_id TEXT, content TEXT, tags TEXT, date_key TEXT, embedding TEXT, dim INTEGER, model TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_INBOX, "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, message TEXT, is_read BOOLEAN DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_USER_SETTINGS, "user_id TEXT PRIMARY KEY, settings_json TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_SHORTCUTS, "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, title TEXT, payload TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_NOTIFICATIONS, "id INTEGER PRIMARY KEY AUTOINCREMENT, recipient_id TEXT, sender_id TEXT, activity_id INTEGER, notification_type TEXT, status TEXT DEFAULT 'UNREAD', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_MEMO_VERSIONS, "id INTEGER PRIMARY KEY AUTOINCREMENT, memo_id TEXT, version INTEGER, content TEXT, created_by TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_PERSONAL_ACCESS_TOKENS, "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, description TEXT, token_hash TEXT, expires_at TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_WEBHOOKS, "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, name TEXT, url TEXT, events TEXT, is_active BOOLEAN DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_DOCUMENTS, "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, title TEXT, content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
        (TABLE_REFRESH_TOKENS, "id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, token TEXT UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP")
    ]
    for name, schema in tables:
        await db.execute(f"CREATE TABLE IF NOT EXISTS {name} ({schema})")
    
    # Manual column check
    cursor = await db.execute(f"PRAGMA table_info({TABLE_TEAMS})")
    rows = await cursor.fetchall()
    cols = [r[1] for r in rows]
    if "description" not in cols:
        await db.execute(f"ALTER TABLE {TABLE_TEAMS} ADD COLUMN description TEXT")
    
    await db.commit()
    logger.info("✅ SQLite initialized")

async def init_sqlite_full(): await init_sqlite()
async def close_sqlite(): await sqlite_client.close()

__all__ = ["sqlite_client", "generate_uid", "utc_now", "serialize_row", "parse_time", "format_time", "init_sqlite", "init_sqlite_full", "close_sqlite"]
