flowdeck/app/db.py

"""FlowDeck — Database layer (SQLite with WAL mode)."""
from __future__ import annotations

import sqlite3
from contextlib import contextmanager
from pathlib import Path


def get_db_path() -> Path:
    from app.config import settings
    return settings.db_path


def init_db():
    """Create tables if they don't exist."""
    db_path = get_db_path()
    if str(db_path) != ":memory:":
        db_path.parent.mkdir(parents=True, exist_ok=True)

    with get_conn() as conn:
        conn.execute("PRAGMA journal_mode=WAL")
        conn.execute("PRAGMA foreign_keys=ON")
        conn.executescript("""
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                login TEXT NOT NULL UNIQUE,
                full_name TEXT NOT NULL DEFAULT '',
                email TEXT NOT NULL DEFAULT '',
                avatar_url TEXT NOT NULL DEFAULT '',
                avatar_color TEXT NOT NULL DEFAULT '#3A3A3A',
                password_hash TEXT,
                is_admin INTEGER NOT NULL DEFAULT 0,
                is_active INTEGER NOT NULL DEFAULT 1,
                last_login TIMESTAMP,
                login_attempts INTEGER NOT NULL DEFAULT 0,
                locked_until TIMESTAMP,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS login_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id),
                ip_address TEXT DEFAULT '',
                user_agent TEXT DEFAULT '',
                logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS user_tokens (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                gitea_user_id INTEGER NOT NULL UNIQUE,
                gitea_token TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS user_oauth_tokens (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id),
                provider TEXT NOT NULL,
                access_token TEXT NOT NULL,
                refresh_token TEXT,
                expires_at TIMESTAMP,
                instance_url TEXT DEFAULT '',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, provider)
            );

            CREATE TABLE IF NOT EXISTS boards (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_owner TEXT NOT NULL,
                project_name TEXT NOT NULL,
                columns_json TEXT NOT NULL DEFAULT '["Backlog","À faire","En cours","Révision","Terminé"]',
                wip_limits_json TEXT DEFAULT '{}',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(project_owner, project_name)
            );

            CREATE TABLE IF NOT EXISTS cards (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                board_id INTEGER NOT NULL REFERENCES boards(id),
                gitea_issue_id INTEGER,
                column_name TEXT NOT NULL DEFAULT 'Backlog',
                position INTEGER NOT NULL DEFAULT 0,
                priority TEXT DEFAULT 'Medium',
                due_date TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS notes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_owner TEXT NOT NULL,
                project_name TEXT NOT NULL,
                title TEXT NOT NULL DEFAULT 'Notes',
                content TEXT NOT NULL DEFAULT '',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(project_owner, project_name, title)
            );

            CREATE TABLE IF NOT EXISTS col_mapping (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                board_id INTEGER NOT NULL REFERENCES boards(id),
                column_name TEXT NOT NULL,
                gitea_label TEXT NOT NULL,
                close_issue BOOLEAN NOT NULL DEFAULT 0,
                UNIQUE(board_id, column_name)
            );

            CREATE TABLE IF NOT EXISTS checklists (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                board_id INTEGER NOT NULL,
                gitea_issue_id INTEGER NOT NULL,
                title TEXT NOT NULL DEFAULT 'Checklist',
                position INTEGER NOT NULL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(board_id, gitea_issue_id, title)
            );

            CREATE TABLE IF NOT EXISTS checklist_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                checklist_id INTEGER NOT NULL REFERENCES checklists(id) ON DELETE CASCADE,
                content TEXT NOT NULL DEFAULT '',
                checked BOOLEAN NOT NULL DEFAULT 0,
                position INTEGER NOT NULL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS project_properties (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_owner TEXT NOT NULL,
                project_name TEXT NOT NULL,
                name TEXT NOT NULL,
                prop_type TEXT NOT NULL DEFAULT 'select',
                options_json TEXT DEFAULT '[]',
                position INTEGER NOT NULL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(project_owner, project_name, name)
            );

            CREATE TABLE IF NOT EXISTS property_values (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                property_id INTEGER NOT NULL REFERENCES project_properties(id) ON DELETE CASCADE,
                gitea_issue_id INTEGER NOT NULL,
                value TEXT NOT NULL DEFAULT '',
                UNIQUE(property_id, gitea_issue_id)
            );

            CREATE TABLE IF NOT EXISTS ai_keywords (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_owner TEXT NOT NULL,
                project_name TEXT NOT NULL,
                keyword TEXT NOT NULL,
                color TEXT NOT NULL DEFAULT '#787774',
                usage_count INTEGER NOT NULL DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(project_owner, project_name, keyword)
            );

            CREATE TABLE IF NOT EXISTS pages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                workspace TEXT NOT NULL,
                workspace_id INTEGER REFERENCES workspaces(id),
                title TEXT NOT NULL DEFAULT 'New page',
                content TEXT NOT NULL DEFAULT '',
                parent_section TEXT DEFAULT 'Private',
                parent_id INTEGER REFERENCES pages(id),
                share_mode TEXT DEFAULT 'private',
                published INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            -- v1.3.0: Database Concept — Notion-style collections
            CREATE TABLE IF NOT EXISTS collections (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT DEFAULT '',
                icon TEXT DEFAULT '📋',
                schema_json TEXT NOT NULL DEFAULT '[]',
                gitea_owner TEXT,
                gitea_repo TEXT,
                is_locked BOOLEAN NOT NULL DEFAULT 0,
                is_inline BOOLEAN NOT NULL DEFAULT 0,
                parent_page_id INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS collection_pages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                collection_id INTEGER NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
                title TEXT NOT NULL DEFAULT '',
                icon TEXT DEFAULT '📄',
                position INTEGER NOT NULL DEFAULT 0,
                parent_id INTEGER REFERENCES collection_pages(id),
                gitea_issue_id INTEGER,
                gitea_issue_number INTEGER,
                property_values_json TEXT NOT NULL DEFAULT '{}',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
            CREATE INDEX IF NOT EXISTS idx_cp_collection ON collection_pages(collection_id, position);
            CREATE INDEX IF NOT EXISTS idx_cp_parent ON collection_pages(parent_id);
            CREATE INDEX IF NOT EXISTS idx_cp_gitea ON collection_pages(gitea_issue_id);

            CREATE TABLE IF NOT EXISTS collection_views (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                collection_id INTEGER NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
                name TEXT NOT NULL DEFAULT 'Default View',
                view_type TEXT NOT NULL DEFAULT 'table',
                config_json TEXT NOT NULL DEFAULT '{}',
                position INTEGER NOT NULL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            -- v1.4.0: Propriétés Avancées — collection_properties (remplace project_properties)
            CREATE TABLE IF NOT EXISTS collection_properties (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                collection_id INTEGER NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
                name TEXT NOT NULL,
                prop_type TEXT NOT NULL DEFAULT 'text',
                options_json TEXT DEFAULT '[]',
                number_format TEXT DEFAULT 'number',
                related_collection_id INTEGER REFERENCES collections(id) ON DELETE SET NULL,
                reverse_name TEXT,
                relation_property_id INTEGER REFERENCES collection_properties(id) ON DELETE SET NULL,
                target_property_id INTEGER REFERENCES collection_properties(id) ON DELETE SET NULL,
                rollup_function TEXT,
                formula_expression TEXT,
                position INTEGER NOT NULL DEFAULT 0,
                required BOOLEAN NOT NULL DEFAULT 0,
                visible_in_views BOOLEAN NOT NULL DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(collection_id, name)
            );

            -- v2.0.0: Multi-user — workspaces, members, comments, history, favorites, templates
            CREATE TABLE IF NOT EXISTS workspaces (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                owner_id INTEGER NOT NULL REFERENCES users(id),
                settings_json TEXT NOT NULL DEFAULT '{}',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS workspace_members (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                workspace_id INTEGER NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
                user_id INTEGER NOT NULL REFERENCES users(id),
                role TEXT NOT NULL DEFAULT 'editor',
                joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(workspace_id, user_id)
            );

            CREATE TABLE IF NOT EXISTS comments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                page_id INTEGER NOT NULL REFERENCES collection_pages(id) ON DELETE CASCADE,
                user_id INTEGER NOT NULL REFERENCES users(id),
                body TEXT NOT NULL DEFAULT '',
                parent_id INTEGER REFERENCES comments(id),
                resolved BOOLEAN NOT NULL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS page_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                page_id INTEGER NOT NULL REFERENCES collection_pages(id) ON DELETE CASCADE,
                user_id INTEGER REFERENCES users(id),
                change_type TEXT NOT NULL,
                snapshot_json TEXT NOT NULL DEFAULT '{}',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            -- v2.2.0: Migrate favorites — drop old schema referencing collection_pages
            DROP TABLE IF EXISTS favorites;

            CREATE TABLE IF NOT EXISTS favorites (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id),
                page_id INTEGER NOT NULL REFERENCES pages(id),
                position INTEGER NOT NULL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, page_id)
            );

            CREATE TABLE IF NOT EXISTS database_templates (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT DEFAULT '',
                schema_json TEXT NOT NULL DEFAULT '[]',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS page_templates (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                collection_id INTEGER NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
                name TEXT NOT NULL DEFAULT 'Default',
                property_values_json TEXT NOT NULL DEFAULT '{}',
                content_json TEXT DEFAULT '[]',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            -- v2.2.0: Tags system
            CREATE TABLE IF NOT EXISTS tags (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                color TEXT DEFAULT '#787774',
                user_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(id),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(name, user_id)
            );

            CREATE TABLE IF NOT EXISTS page_tags (
                page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
                tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (page_id, tag_id)
            );
            CREATE INDEX IF NOT EXISTS idx_pt_page ON page_tags(page_id);
            CREATE INDEX IF NOT EXISTS idx_pt_tag ON page_tags(tag_id);
        """)
        # Migration: add parent_id if missing (v1.0.0+)
        try:
            conn.execute("ALTER TABLE pages ADD COLUMN parent_id INTEGER REFERENCES pages(id)")
        except sqlite3.OperationalError:
            pass
        # Migration: add avatar_color (v2.3.0+)
        try:
            conn.execute("ALTER TABLE users ADD COLUMN avatar_color TEXT NOT NULL DEFAULT '#3A3A3A'")
        except sqlite3.OperationalError:
            pass
        # Migration: add sort_order for drag & drop tree reordering (v1.1.0+)
        try:
            conn.execute("ALTER TABLE pages ADD COLUMN sort_order INTEGER NOT NULL DEFAULT 0")
        except sqlite3.OperationalError:
            pass
        # Migration: add content_format for Notion-style block editor (v1.2.0+)
        # 'markdown' = legacy textarea, 'blocks' = JSON array of block objects
        try:
            conn.execute("ALTER TABLE pages ADD COLUMN content_format TEXT NOT NULL DEFAULT 'markdown'")
        except sqlite3.OperationalError:
            pass
        # Migration: add deleted_at for trash/soft-delete
        try:
            conn.execute("ALTER TABLE pages ADD COLUMN deleted_at TEXT")
        except sqlite3.OperationalError:
            pass
        try:
            conn.execute("ALTER TABLE pages ADD COLUMN share_mode TEXT DEFAULT 'private'")
        except sqlite3.OperationalError:
            pass
        try:
            conn.execute("ALTER TABLE pages ADD COLUMN published INTEGER DEFAULT 0")
        except sqlite3.OperationalError:
            pass
        try:
            conn.execute("ALTER TABLE pages ADD COLUMN workspace_id INTEGER REFERENCES workspaces(id)")
        except sqlite3.OperationalError:
            pass
        # v2.2: Auth locale
        for col in ["password_hash", "is_active", "last_login", "login_attempts", "locked_until"]:
            try:
                conn.execute(f"ALTER TABLE users ADD COLUMN {col} {'TEXT' if col in ('password_hash','last_login','locked_until') else 'INTEGER NOT NULL DEFAULT ' + ('1' if col=='is_active' else '0')}")
            except sqlite3.OperationalError:
                pass
        # v2.5: Login history
        try:
            conn.execute("ALTER TABLE users ADD COLUMN is_admin INTEGER NOT NULL DEFAULT 0")
        except sqlite3.OperationalError:
            pass
        try:
            conn.execute("""CREATE TABLE IF NOT EXISTS login_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id),
                ip_address TEXT DEFAULT '',
                user_agent TEXT DEFAULT '',
                logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )""")
        except sqlite3.OperationalError:
            pass
        # v2.6: Tags per-user
        try:
            conn.execute("ALTER TABLE tags ADD COLUMN user_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(id)")
        except sqlite3.OperationalError:
            pass
        try:
            # Recreate UNIQUE constraint for per-user tags
            conn.execute("CREATE TABLE IF NOT EXISTS tags_new (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, color TEXT DEFAULT '#787774', user_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(id), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(name, user_id))")
            conn.execute("INSERT OR IGNORE INTO tags_new (id, name, color, user_id, created_at) SELECT id, name, color, COALESCE(user_id,0), created_at FROM tags")
            conn.execute("DROP TABLE tags")
            conn.execute("ALTER TABLE tags_new RENAME TO tags")
        except sqlite3.OperationalError:
            pass
        conn.commit()
        # v2.7: Private pages for Gitea workspaces
        conn.execute("""
            CREATE TABLE IF NOT EXISTS gitea_private_pages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id),
                gitea_owner TEXT NOT NULL,
                gitea_repo TEXT NOT NULL,
                title TEXT NOT NULL DEFAULT 'Untitled',
                content TEXT DEFAULT '',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        conn.commit()

        # ── v4.0: Account & Integrations ──
        # 1) auth_method on users
        try:
            conn.execute("ALTER TABLE users ADD COLUMN auth_method TEXT DEFAULT 'local'")
        except sqlite3.OperationalError:
            pass
        # Detect existing auth: if user has a gitea_token in user_tokens → 'gitea'
        conn.execute(
            "UPDATE users SET auth_method='gitea' WHERE id IN (SELECT gitea_user_id FROM user_tokens)"
        )
        conn.execute(
            "UPDATE users SET auth_method='local' WHERE auth_method IS NULL"
        )
        # 2) Publishing & sharing columns on pages
        try:
            conn.execute("ALTER TABLE pages ADD COLUMN is_published INTEGER NOT NULL DEFAULT 0")
        except sqlite3.OperationalError:
            pass
        try:
            conn.execute("ALTER TABLE pages ADD COLUMN publish_slug TEXT DEFAULT ''")
        except sqlite3.OperationalError:
            pass
        try:
            conn.execute("ALTER TABLE pages ADD COLUMN is_shared INTEGER NOT NULL DEFAULT 0")
        except sqlite3.OperationalError:
            pass
        # 3) page_shares table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS page_shares (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                page_id INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
                shared_with_user_id INTEGER REFERENCES users(id),
                shared_with_email TEXT DEFAULT '',
                permission TEXT NOT NULL DEFAULT 'view',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                created_by INTEGER REFERENCES users(id)
            )
        """)
        # 4) recents table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS recents (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id),
                page_id INTEGER NOT NULL REFERENCES pages(id),
                workspace TEXT NOT NULL DEFAULT '',
                source_type TEXT NOT NULL DEFAULT 'local',
                accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, page_id)
            )
        """)
        conn.commit()


@contextmanager
def get_conn():
    """Yield a SQLite connection (WAL mode, foreign keys on)."""
    db_path = get_db_path()
    conn = sqlite3.connect(str(db_path))
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    try:
        yield conn
    finally:
        conn.close()