import os
from typing import Any

import aiosqlite

from config import MEMO_DB_PATH


DB_PATH = MEMO_DB_PATH


async def init_memo_db():
    """Ensure memo database and tables exist."""
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    async with aiosqlite.connect(DB_PATH) as db:
        # Main memos table - creator_id is TEXT to store Clerk user IDs
        await db.execute(
            """
            CREATE TABLE IF NOT EXISTS memos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                content TEXT NOT NULL,
                visibility TEXT NOT NULL DEFAULT 'PRIVATE',
                tags_json TEXT NOT NULL DEFAULT '[]',
                creator_id TEXT NOT NULL DEFAULT 'anonymous',
                created_at TEXT NOT NULL DEFAULT (DATETIME('now')),
                updated_at TEXT NOT NULL DEFAULT (DATETIME('now'))
            )
            """
        )
        
        # Create index on creator_id for fast user-specific queries
        await db.execute(
            "CREATE INDEX IF NOT EXISTS idx_memos_creator_id ON memos (creator_id)"
        )
        
        await db.execute(
            """
            CREATE TABLE IF NOT EXISTS memo_embeddings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                memo_id INTEGER NOT NULL,
                content TEXT,
                tags_json TEXT NOT NULL DEFAULT '[]',
                date_key TEXT,
                embedding_json TEXT NOT NULL,
                dim INTEGER NOT NULL,
                model TEXT DEFAULT 'stub-embedding',
                created_at TEXT NOT NULL DEFAULT (DATETIME('now')),
                updated_at TEXT NOT NULL DEFAULT (DATETIME('now'))
            )
            """
        )
        await db.execute(
            "CREATE INDEX IF NOT EXISTS idx_memo_embeddings_memo_id ON memo_embeddings (memo_id)"
        )
        await db.execute(
            "CREATE INDEX IF NOT EXISTS idx_memo_embeddings_date_key ON memo_embeddings (date_key)"
        )
        await db.commit()


async def execute(query: str, params: tuple[Any, ...] = ()) -> int:
    """Execute write query and return lastrowid if available."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(query, params)
        await db.commit()
        return cursor.lastrowid or 0


async def fetch_one(query: str, params: tuple[Any, ...] = ()) -> dict[str, Any] | None:
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        async with db.execute(query, params) as cursor:
            row = await cursor.fetchone()
            return dict(row) if row else None


async def fetch_all(query: str, params: tuple[Any, ...] = ()) -> list[dict[str, Any]]:
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        async with db.execute(query, params) as cursor:
            rows = await cursor.fetchall()
            return [dict(r) for r in rows]
