"""
SQLite client & helpers for CuCu backend.

Thay thế hoàn toàn MongoDB bằng SQLite cho hệ thống CuCu Note.
Database path: backend/database/cuccu_note.db
Driver: aiosqlite (async/await với FastAPI)
"""

from __future__ import annotations

import json
import logging
import os
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"  # DOCX/PDF document storage


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)
        return cls._instance

    async def connect(self) -> None:
        """Initialize SQLite connection."""
        if self._db is None:
            try:
                # Ensure directory exists
                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)
                # CRITICAL FIX: Set row_factory để access column bằng tên
                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(
                    "✅ Connected to SQLite: %s (WAL mode, row_factory=Row)",
                    MEMO_DB_PATH,
                )
            except Exception as e:
                logger.error("❌ SQLite connection failed: %s", e)
                raise

    async def close(self) -> None:
        """Close SQLite connection."""
        if self._db:
            await self._db.close()
            self._db = None
            logger.info("SQLite connection closed")

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

    # Helper methods cho queries
    def _normalize_query(self, query: str) -> str:
        """Replace table names without prefix with cuccu_ prefix."""
        # List of base table names (without cuccu_ prefix)
        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"
        ]
        normalized = query
        for table in base_tables:
            # Replace only whole words to avoid accidental replacements
            # Pattern: table name as a word (not part of another word)
            import re
            pattern = r'\b' + re.escape(table) + r'\b'
            normalized = re.sub(pattern, f"cuccu_{table}", normalized)
        return normalized

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

    async def executemany(self, query: str, params: list[tuple]) -> aiosqlite.Cursor:
        """Execute many queries."""
        query = self._normalize_query(query)
        cursor = await self.db.executemany(query, params)
        await self.db.commit()
        return cursor

    async def fetch_one(self, query: str, params: tuple = ()) -> aiosqlite.Row | None:
        """Fetch one row."""
        query = self._normalize_query(query)
        cursor = await self.db.execute(query, params)
        row = await cursor.fetchone()
        await cursor.close()
        return row

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

    # MongoDB compatibility methods
    async def find_one(self, query: dict, projection: dict | None = None) -> aiosqlite.Row | None:
        """MongoDB-like find_one. Simplified: only supports simple equality on id or uid."""
        # Determine collection from current context (hacky but works for our usage)
        table_name = self._current_table if hasattr(self, '_current_table') else 'memos'

        if '_id' in query:
            id_val = query['_id']
            if isinstance(id_val, int):
                sql = f"SELECT * FROM {table_name} WHERE id = ?"
                return await self.fetch_one(sql, (id_val,))
            else:
                sql = f"SELECT * FROM {table_name} WHERE id = ? OR uid = ?"
                return await self.fetch_one(sql, (id_val, str(id_val)))

        if 'uid' in query:
            sql = f"SELECT * FROM {table_name} WHERE uid = ?"
            return await self.fetch_one(sql, (query['uid'],))

        # For other simple queries, try to build SQL
        conditions = []
        params = []
        for key, val in query.items():
            conditions.append(f"{key} = ?")
            params.append(val)
        where_clause = " AND ".join(conditions) if conditions else "1=1"
        sql = f"SELECT * FROM {table_name} WHERE {where_clause} LIMIT 1"
        return await self.fetch_one(sql, tuple(params))

    async def insert_one(self, doc: dict, table: str | None = None) -> Any:
        """MongoDB-like insert_one."""
        if table is None:
            table = self._current_table if hasattr(self, '_current_table') else 'memos'

        cols = ', '.join(doc.keys())
        placeholders = ', '.join(['?'] * len(doc))
        params = tuple(doc.values())

        sql = f"INSERT INTO {table} ({cols}) VALUES ({placeholders})"
        cursor = await self.execute(sql, params)
        # SQLite doesn't return inserted_id like MongoDB, but we can use lastrowid
        class Result:
            inserted_id = cursor.lastrowid
        return Result()

    async def update_one(self, query: dict, update: dict, upsert: bool = False) -> Any:
        """MongoDB-like update_one."""
        table = self._current_table if hasattr(self, '_current_table') else 'memos'

        set_clauses = []
        params = []
        for key, val in update.get('$set', {}).items():
            set_clauses.append(f"{key} = ?")
            params.append(val)

        if not set_clauses:
            class EmptyResult:
                modified_count = 0
                matched_count = 0
                upserted_id = None
            return EmptyResult()

        set_sql = ', '.join(set_clauses)
        where_params = []
        for key, val in query.items():
            where_params.append(f"{key} = ?")
            params.append(val)
        where_clause = ' AND '.join(where_params) if where_params else '1=1'

        sql = f"UPDATE {table} SET {set_sql} WHERE {where_clause}"
        result = await self.execute(sql, tuple(params))

        class Result:
            modified_count = result.rowcount
            matched_count = result.rowcount
            upserted_id = None
        return Result()

    async def delete_one(self, query: dict) -> Any:
        """MongoDB-like delete_one."""
        table = self._current_table if hasattr(self, '_current_table') else 'memos'

        conditions = []
        params = []
        for key, val in query.items():
            conditions.append(f"{key} = ?")
            params.append(val)
        where_clause = ' AND '.join(conditions) if conditions else '1=1'

        sql = f"DELETE FROM {table} WHERE {where_clause}"
        result = await self.execute(sql, tuple(params))

        class Result:
            deleted_count = result.rowcount
        return Result()

    async def delete_many(self, query: dict) -> Any:
        """MongoDB-like delete_many."""
        table = self._current_table if hasattr(self, '_current_table') else 'memos'

        conditions = []
        params = []
        for key, val in query.items():
            conditions.append(f"{key} = ?")
            params.append(val)
        where_clause = ' AND '.join(conditions) if conditions else '1=1'

        sql = f"DELETE FROM {table} WHERE {where_clause}"
        result = await self.execute(sql, tuple(params))

        class Result:
            deleted_count = result.rowcount
        return Result()

    async def count_documents(self, query: dict) -> int:
        """MongoDB-like count_documents."""
        table = self._current_table if hasattr(self, '_current_table') else 'memos'

        conditions = []
        params = []
        for key, val in query.items():
            conditions.append(f"{key} = ?")
            params.append(val)
        where_clause = ' AND '.join(conditions) if conditions else '1=1'

        sql = f"SELECT COUNT(*) as cnt FROM {table} WHERE {where_clause}"
        row = await self.fetch_one(sql, tuple(params))
        return row['cnt'] if row else 0

    async def find(self, query: dict, projection: dict | None = None) -> list[aiosqlite.Row]:
        """MongoDB-like find."""
        table = self._current_table if hasattr(self, '_current_table') else 'memos'

        conditions = []
        params = []
        for key, val in query.items():
            if isinstance(val, dict):
                for op, v in val.items():
                    if op == '$in':
                        placeholders = ','.join(['?'] * len(v))
                        conditions.append(f"{key} IN ({placeholders})")
                        params.extend(v)
                    elif op == '$ne':
                        conditions.append(f"{key} != ?")
                        params.append(v)
                    elif op == '$exists':
                        if v:
                            conditions.append(f"{key} IS NOT NULL")
                        else:
                            conditions.append(f"{key} IS NULL")
            else:
                conditions.append(f"{key} = ?")
                params.append(val)

        where_clause = ' AND '.join(conditions) if conditions else '1=1'
        sql = f"SELECT * FROM {table} WHERE {where_clause}"
        return await self.fetch_all(sql, tuple(params))

    async def find_one(self, query: dict, projection: dict | None = None) -> aiosqlite.Row | None:
        """MongoDB-like find_one."""
        results = await self.find(query, projection)
        return results[0] if results else None

    async def insert_one(self, doc: dict, table: str | None = None) -> Any:
        """MongoDB-like insert_one."""
        if table is None:
            table = self._current_table if hasattr(self, '_current_table') else 'memos'

        cols = ', '.join(doc.keys())
        placeholders = ', '.join(['?'] * len(doc))
        params = tuple(doc.values())

        sql = f"INSERT INTO {table} ({cols}) VALUES ({placeholders})"
        cursor = await self.execute(sql, params)

        class Result:
            inserted_id = cursor.lastrowid
        return Result()

    async def update_one(self, query: dict, update: dict, upsert: bool = False) -> Any:
        """MongoDB-like update_one."""
        table = self._current_table if hasattr(self, '_current_table') else 'memos'

        set_clauses = []
        params = []
        for key, val in update.get('$set', {}).items():
            set_clauses.append(f"{key} = ?")
            params.append(val)

        if not set_clauses:
            class EmptyResult:
                modified_count = 0
                matched_count = 0
                upserted_id = None
            return EmptyResult()

        set_sql = ', '.join(set_clauses)
        where_params = []
        for key, val in query.items():
            where_params.append(f"{key} = ?")
            params.append(val)
        where_clause = ' AND '.join(where_params)

        sql = f"UPDATE {table} SET {set_sql} WHERE {where_clause}"
        result = await self.execute(sql, tuple(params))

        class Result:
            modified_count = result.rowcount
            matched_count = result.rowcount
            upserted_id = None
        return Result()

    async def find_one_and_update(self, query: dict, update: dict, return_document: bool = False, upsert: bool = False) -> aiosqlite.Row | None:
        """MongoDB-like find_one_and_update."""
        table = self._current_table if hasattr(self, '_current_table') else 'memos'

        # Get current document first
        old_doc = await self.find_one(query)
        if not old_doc and not upsert:
            return None

        # Perform update
        update_result = await self.update_one(query, update, upsert=upsert)

        if return_document:
            # Return the updated document
            if old_doc:
                # Refetch to get updated data
                return await self.find_one(query)
            else:
                # Upsert case - try to find by unique fields in query
                return await self.find_one(query)
        else:
            return old_doc

    async def delete_one(self, query: dict) -> Any:
        """MongoDB-like delete_one."""
        table = self._current_table if hasattr(self, '_current_table') else 'memos'

        conditions = []
        params = []
        for key, val in query.items():
            conditions.append(f"{key} = ?")
            params.append(val)
        where_clause = ' AND '.join(conditions) if conditions else '1=1'

        sql = f"DELETE FROM {table} WHERE {where_clause}"
        result = await self.execute(sql, tuple(params))

        class Result:
            deleted_count = result.rowcount
        return Result()

    async def delete_many(self, query: dict) -> Any:
        """MongoDB-like delete_many."""
        table = self._current_table if hasattr(self, '_current_table') else 'memos'

        conditions = []
        params = []
        for key, val in query.items():
            conditions.append(f"{key} = ?")
            params.append(val)
        where_clause = ' AND '.join(conditions) if conditions else '1=1'

        sql = f"DELETE FROM {table} WHERE {where_clause}"
        result = await self.execute(sql, tuple(params))

        class Result:
            deleted_count = result.rowcount
        return Result()

    async def count_documents(self, query: dict) -> int:
        """MongoDB-like count_documents."""
        table = self._current_table if hasattr(self, '_current_table') else 'memos'

        conditions = []
        params = []
        for key, val in query.items():
            if isinstance(val, dict):
                for op, v in val.items():
                    if op == '$ne':
                        conditions.append(f"{key} != ?")
                        params.append(v)
                    elif op == '$exists':
                        if v:
                            conditions.append(f"{key} IS NOT NULL")
                        else:
                            conditions.append(f"{key} IS NULL")
                    elif op == '$in':
                        placeholders = ','.join(['?'] * len(v))
                        conditions.append(f"{key} IN ({placeholders})")
                        params.extend(v)
            else:
                conditions.append(f"{key} = ?")
                params.append(val)

        where_clause = ' AND '.join(conditions) if conditions else '1=1'
        sql = f"SELECT COUNT(*) as cnt FROM {table} WHERE {where_clause}"
        row = await self.fetch_one(sql, tuple(params))
        return row['cnt'] if row else 0

    async def aggregate(self, pipeline: list[dict]) -> list:
        """MongoDB-like aggregate (limited support)."""
        table = self._current_table if hasattr(self, '_current_table') else 'memos'

        match_cond = {}
        group_spec = {}

        for stage in pipeline:
            if '$match' in stage:
                match_cond.update(stage['$match'])
            if '$group' in stage:
                group_spec = stage['$group']

        # Build WHERE from match_cond
        where_conds = []
        params = []
        for key, val in match_cond.items():
            if isinstance(val, dict):
                for op, v in val.items():
                    if op == '$in':
                        placeholders = ','.join(['?'] * len(v))
                        where_conds.append(f"{key} IN ({placeholders})")
                        params.extend(v)
                    elif op == '$ne':
                        where_conds.append(f"{key} != ?")
                        params.append(v)
            else:
                where_conds.append(f"{key} = ?")
                params.append(val)

        where_clause = ' AND '.join(where_conds) if where_conds else '1=1'

        if group_spec:
            # Our usage: {"_id": "$parent", "count": {"$sum": 1}}
            group_field = 'parent'
            sql = f"""
                SELECT {group_field} as _id, COUNT(*) as count
                FROM {table}
                WHERE {where_clause}
                GROUP BY {group_field}
            """
            rows = await self.fetch_all(sql, tuple(params))
            return [{"_id": row["_id"], "count": row["count"]} for row in rows]

        return []

    # Collection helpers (tương thùng với MongoDB API)
    @property
    def memos(self):
        self._current_table = TABLE_MEMOS
        return self

    @property
    def attachments(self):
        self._current_table = TABLE_ATTACHMENTS
        return self

    @property
    def memo_relations(self):
        self._current_table = TABLE_MEMO_RELATIONS
        return self

    @property
    def reactions(self):
        self._current_table = TABLE_REACTIONS
        return self

    @property
    def memo_embeddings(self):
        self._current_table = TABLE_MEMO_EMBEDDINGS
        return self

    @property
    def inbox(self):
        self._current_table = TABLE_INBOX
        return self

    @property
    def user_settings(self):
        self._current_table = TABLE_USER_SETTINGS
        return self

    @property
    def shortcuts(self):
        self._current_table = TABLE_SHORTCUTS
        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 cached_user_profiles(self):
        self._current_table = TABLE_USER_PROFILES
        return self

    @property
    def users(self):
        self._current_table = TABLE_USERS
        return self

    @property
    def refresh_tokens(self):
        self._current_table = TABLE_REFRESH_TOKENS
        return self

    @property
    def activities(self):
        self._current_table = TABLE_ACTIVITIES
        return self

    @property
    def notifications(self):
        self._current_table = TABLE_NOTIFICATIONS
        return self

    @property
    def activities(self):
        return self

    @property
    def notifications(self):
        return self


# Singleton instance
sqlite_client = SQLiteClient()


def generate_uid() -> str:
    """Generate a unique ID for memos (similar to nanoid)."""
    import secrets

    return secrets.token_urlsafe(16)[:22]


def utc_now() -> datetime:
    """Get current UTC time."""
    return datetime.now(timezone.utc)


def serialize_row(row: aiosqlite.Row | None) -> dict[str, Any] | None:
    """Convert SQLite row to JSON-serializable dict."""
    if row is None:
        return None
    result = dict(row)
    # Convert datetime fields to ISO format
    for key, value in result.items():
        if isinstance(value, datetime):
            result[key] = value.isoformat()
        elif isinstance(value, bytes):
            # Handle JSON fields stored as TEXT (bytes come from aiosqlite)
            try:
                if value:
                    result[key] = json.loads(value.decode('utf-8'))
                else:
                    result[key] = None
            except Exception:
                result[key] = value.decode('utf-8') if value else None
    return result


def parse_time(dt_value: Any) -> datetime | None:
    """Parse time từ SQLite (ISO string, timestamp, hoặc datetime)."""
    if dt_value is None:
        return None
    if isinstance(dt_value, datetime):
        return dt_value
    if isinstance(dt_value, (int, float)):
        return datetime.fromtimestamp(dt_value, tz=timezone.utc)
    # ISO string
    try:
        # Handle ISO format với hoặc không có timezone
        dt = datetime.fromisoformat(str(dt_value).replace('Z', '+00:00'))
        if dt.tzinfo is None:
            dt = dt.replace(tzinfo=timezone.utc)
        return dt
    except Exception:
        logger.warning(f"Failed to parse datetime: {dt_value}")
        return None


def format_time(dt: datetime | None) -> str | None:
    """Format datetime thành ISO string cho API response."""
    if dt is None:
        return None
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)
    return dt.astimezone(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")


async def init_sqlite():
    """Initialize SQLite database with tables and indexes."""
    await sqlite_client.connect()
    db = sqlite_client.db

    # ====================== USERS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_USERS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE,
            password_hash TEXT,
            nickname TEXT,
            username TEXT UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== MEMOS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_MEMOS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            uid TEXT UNIQUE,
            creator_id TEXT NOT NULL,
            content TEXT NOT NULL,
            visibility TEXT NOT NULL DEFAULT 'PRIVATE',
            pinned BOOLEAN DEFAULT 0,
            row_status TEXT DEFAULT 'NORMAL',
            parent TEXT,
            payload TEXT,  -- JSON: tags, hasLink, hasTaskList, hasCode, hasIncompleteTasks
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            deadline TIMESTAMP,
            priority TEXT,
            reminder_at TIMESTAMP,
            is_completed BOOLEAN DEFAULT 0,
            completed_at TIMESTAMP,
            anonymous_id TEXT,
            workspace_id TEXT DEFAULT 'PERSONAL',  -- Data isolation: PERSONAL | AI_SALES_CRM
            is_read BOOLEAN DEFAULT 0  -- Inbox unread status: 0=UNREAD, 1=READ
        )
    """)

    # Migration: add workspace_id column if missing (for existing DBs)
    cursor = await db.execute(f"PRAGMA table_info({TABLE_MEMOS})")
    columns = await cursor.fetchall()
    column_names = [row[1] for row in columns]  # row[1] is column name
    if 'workspace_id' not in column_names:
        await db.execute(f"ALTER TABLE {TABLE_MEMOS} ADD COLUMN workspace_id TEXT DEFAULT 'PERSONAL'")
        logger.info("✅ Added workspace_id column to memos table (migration)")
    if 'is_read' not in column_names:
        await db.execute(f"ALTER TABLE {TABLE_MEMOS} ADD COLUMN is_read BOOLEAN DEFAULT 0")
        logger.info("✅ Added is_read column to memos table (migration)")

    # ====================== ATTACHMENTS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_ATTACHMENTS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            uid TEXT UNIQUE,
            creator_id TEXT NOT NULL,
            filename TEXT NOT NULL,
            type TEXT NOT NULL,
            size INTEGER DEFAULT 0,
            storage_type TEXT DEFAULT 'LOCAL',
            reference TEXT,
            memo_id TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== MEMO RELATIONS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_MEMO_RELATIONS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            memo_id TEXT NOT NULL,
            related_memo_id TEXT NOT NULL,
            relation_type TEXT DEFAULT 'REFERENCE',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(memo_id, related_memo_id, relation_type)
        )
    """)

    # ====================== REACTIONS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_REACTIONS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            content_id TEXT NOT NULL,
            creator_id TEXT NOT NULL,
            reaction_type TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(content_id, creator_id, reaction_type)
        )
    """)

    # ====================== MEMO EMBEDDINGS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_MEMO_EMBEDDINGS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            memo_id TEXT UNIQUE NOT NULL,
            content TEXT,
            tags TEXT,  -- JSON array
            date_key TEXT,
            embedding TEXT,  -- JSON array (list of floats)
            dim INTEGER,
            model TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== INBOX (Notifications) ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_INBOX} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            sender_id TEXT NOT NULL,
            receiver_id TEXT NOT NULL,
            status TEXT DEFAULT 'UNREAD',
            message_type TEXT,
            memo_id TEXT,
            content TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== USER SETTINGS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_USER_SETTINGS} (
            user_id TEXT PRIMARY KEY,
            locale TEXT DEFAULT 'en',
            theme TEXT DEFAULT 'system',
            memo_visibility TEXT DEFAULT 'PRIVATE',
            enable_notifications BOOLEAN DEFAULT 1,
            openai_api_key_encrypted TEXT,
            timezone TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== SHORTCUTS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_SHORTCUTS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            creator_id TEXT NOT NULL,
            name TEXT NOT NULL,
            filter TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== TEAMS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_TEAMS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            owner_id TEXT NOT NULL,
            name TEXT,
            invite_code TEXT UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== TEAM MEMBERS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_TEAM_MEMBERS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            team_id INTEGER NOT NULL,
            user_id TEXT NOT NULL,
            role TEXT DEFAULT 'MEMBER',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(team_id, user_id)
        )
    """)

    # ====================== TEAM MEMOS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_TEAM_MEMOS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            team_id INTEGER NOT NULL,
            creator_id TEXT NOT NULL,
            space TEXT,
            content TEXT,
            visibility TEXT DEFAULT 'PRIVATE',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== TEAM COMMENTS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_TEAM_COMMENTS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            memo_id TEXT NOT NULL,
            team_id INTEGER NOT NULL,
            creator_id TEXT NOT NULL,
            content TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== TEAM REACTIONS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_TEAM_REACTIONS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            memo_id TEXT NOT NULL,
            user_id TEXT NOT NULL,
            emoji TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(memo_id, user_id, emoji)
        )
    """)

    # ====================== USER PROFILES (Cache) ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_USER_PROFILES} (
            user_id TEXT PRIMARY KEY,
            username TEXT,
            first_name TEXT,
            last_name TEXT,
            avatar_url TEXT,
            cached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== REFRESH TOKENS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_REFRESH_TOKENS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            token TEXT UNIQUE NOT NULL,
            expires_at TIMESTAMP NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== ACTIVITIES ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_ACTIVITIES} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            type TEXT NOT NULL,
            creator_id TEXT NOT NULL,
            memo_id TEXT NOT NULL,
            related_memo_id TEXT,
            level TEXT DEFAULT 'INFO',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== NOTIFICATIONS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_NOTIFICATIONS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            recipient_id TEXT NOT NULL,
            sender_id TEXT NOT NULL,
            activity_id INTEGER,
            type TEXT DEFAULT 'MEMO_COMMENT',
            status TEXT DEFAULT 'UNREAD',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== MEMO VERSIONS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_MEMO_VERSIONS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            memo_id TEXT NOT NULL,
            version_index INTEGER NOT NULL,
            content TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            created_by TEXT,
            UNIQUE(memo_id, version_index)
        )
    """)

    # ====================== PERSONAL ACCESS TOKENS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_PERSONAL_ACCESS_TOKENS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            token_hash TEXT UNIQUE NOT NULL,
            description TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            expires_at TIMESTAMP,
            last_used_at TIMESTAMP,
            is_active BOOLEAN DEFAULT 1
        )
    """)

    # ====================== WEBHOOKS ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_WEBHOOKS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            name TEXT NOT NULL,
            url TEXT NOT NULL,
            secret TEXT,
            events TEXT,
            is_active BOOLEAN DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # ====================== DOCUMENTS (DOCX/PDF Management) ======================
    await db.execute(f"""
        CREATE TABLE IF NOT EXISTS {TABLE_DOCUMENTS} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            uid TEXT UNIQUE NOT NULL,
            creator_id TEXT NOT NULL,
            filename TEXT NOT NULL,
            original_filename TEXT NOT NULL,
            file_path TEXT NOT NULL,
            file_size INTEGER DEFAULT 0,
            mime_type TEXT DEFAULT 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
            workspace_id TEXT DEFAULT 'PERSONAL',
            status TEXT DEFAULT 'PENDING',  -- PENDING | PROCESSING | COMPLETED | FAILED
            pages INTEGER DEFAULT 0,
            title TEXT,
            content TEXT,  -- Extracted markdown content
            summary TEXT,  -- AI-generated summary
            tags TEXT,  -- JSON array of tags
            ai_enhanced BOOLEAN DEFAULT 0,
            error_message TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            processed_at TIMESTAMP
        )
    """)

    # ====================== INDEXES ======================
    # Memos indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memos_creator_created ON {TABLE_MEMOS}(creator_id, created_at DESC)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memos_creator_visibility ON {TABLE_MEMOS}(creator_id, visibility)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memos_creator_pinned ON {TABLE_MEMOS}(creator_id, pinned)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memos_creator_rowstatus ON {TABLE_MEMOS}(creator_id, row_status)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memos_uid ON {TABLE_MEMOS}(uid)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memos_parent ON {TABLE_MEMOS}(parent)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memos_created_at ON {TABLE_MEMOS}(created_at DESC)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memos_workspace ON {TABLE_MEMOS}(workspace_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memos_is_read ON {TABLE_MEMOS}(is_read)")

    # Memo relations indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memo_relations_memo ON {TABLE_MEMO_RELATIONS}(memo_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memo_relations_related ON {TABLE_MEMO_RELATIONS}(related_memo_id)")

    # Reactions indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_reactions_content ON {TABLE_REACTIONS}(content_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_reactions_creator ON {TABLE_REACTIONS}(creator_id)")

    # Embeddings indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_embeddings_memo ON {TABLE_MEMO_EMBEDDINGS}(memo_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_embeddings_date ON {TABLE_MEMO_EMBEDDINGS}(date_key)")

    # Inbox indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_inbox_receiver_created ON {TABLE_INBOX}(receiver_id, created_at DESC)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_inbox_receiver_status ON {TABLE_INBOX}(receiver_id, status)")

    # User settings index
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_user_settings_user ON {TABLE_USER_SETTINGS}(user_id)")

    # Shortcuts index
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_shortcuts_creator ON {TABLE_SHORTCUTS}(creator_id)")

    # Teams indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_teams_owner ON {TABLE_TEAMS}(owner_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_teams_invite_code ON {TABLE_TEAMS}(invite_code)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_team_members_team ON {TABLE_TEAM_MEMBERS}(team_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_team_members_user ON {TABLE_TEAM_MEMBERS}(user_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_team_memos_team_space ON {TABLE_TEAM_MEMOS}(team_id, space)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_team_memos_creator ON {TABLE_TEAM_MEMOS}(team_id, creator_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_team_comments_memo ON {TABLE_TEAM_COMMENTS}(memo_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_team_reactions_memo ON {TABLE_TEAM_REACTIONS}(memo_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_team_reactions_user_emoji ON {TABLE_TEAM_REACTIONS}(memo_id, user_id, emoji)")

    # User profiles index
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_user_profiles_user ON {TABLE_USER_PROFILES}(user_id)")

    # Refresh tokens indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user ON {TABLE_REFRESH_TOKENS}(user_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_refresh_tokens_token ON {TABLE_REFRESH_TOKENS}(token)")

    # Personal Access Tokens indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_pat_user ON {TABLE_PERSONAL_ACCESS_TOKENS}(user_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_pat_token_hash ON {TABLE_PERSONAL_ACCESS_TOKENS}(token_hash)")

    # Webhooks indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_webhooks_user ON {TABLE_WEBHOOKS}(user_id)")

    # Documents indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_documents_creator ON {TABLE_DOCUMENTS}(creator_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_documents_workspace ON {TABLE_DOCUMENTS}(workspace_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_documents_status ON {TABLE_DOCUMENTS}(status)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_documents_created ON {TABLE_DOCUMENTS}(created_at DESC)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_documents_uid ON {TABLE_DOCUMENTS}(uid)")

    # Activities indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_activities_created ON {TABLE_ACTIVITIES}(created_at DESC)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_activities_creator ON {TABLE_ACTIVITIES}(creator_id)")

    # Notifications indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_notifications_recipient ON {TABLE_NOTIFICATIONS}(recipient_id)")
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_notifications_created ON {TABLE_NOTIFICATIONS}(created_at DESC)")

    # Memo versions indexes
    await db.execute(f"CREATE INDEX IF NOT EXISTS idx_memo_versions_memo ON {TABLE_MEMO_VERSIONS}(memo_id, version_index DESC)")

    await db.commit()
    logger.info("✅ SQLite tables and indexes created successfully")


async def init_sqlite_full():
    """Alias cho init_sqlite để tương thích với init_mongodb."""
    await init_sqlite()


async def close_sqlite():
    """Close SQLite connection."""
    await sqlite_client.close()


# Export functions với tên tương thích MongoDB
__all__ = [
    "sqlite_client",
    "generate_uid",
    "utc_now",
    "serialize_row",
    "parse_time",
    "format_time",
    "init_sqlite",
    "init_sqlite_full",
    "close_sqlite",
]
