# qa-copilot/workspace.py
"""
Collaborative workspace for local tier (#18).

Shared generation history, test review queue, and team story assignments.
Storage: userdata.db (or configurable path), WAL mode.
"""
from __future__ import annotations

import datetime
import sqlite3
import uuid
from pathlib import Path

SCRIPT_DIR = Path(__file__).parent
_DEFAULT_DB = str(SCRIPT_DIR / "userdata.db")


def init_db(db_path: str = _DEFAULT_DB) -> None:
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS shared_generations (
            id             TEXT PRIMARY KEY,
            user_id        TEXT NOT NULL,
            story_key      TEXT NOT NULL,
            generated_json TEXT NOT NULL,
            module         TEXT NOT NULL DEFAULT '',
            review_status  TEXT NOT NULL DEFAULT 'pending',
            created_at     TEXT NOT NULL
        )
    """)
    conn.execute("CREATE INDEX IF NOT EXISTS idx_shared_gen_story ON shared_generations(story_key)")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS reviews (
            id          TEXT PRIMARY KEY,
            gen_id      TEXT NOT NULL REFERENCES shared_generations(id),
            reviewer_id TEXT NOT NULL,
            verdict     TEXT NOT NULL,
            comment     TEXT NOT NULL DEFAULT '',
            created_at  TEXT NOT NULL
        )
    """)
    conn.execute("CREATE INDEX IF NOT EXISTS idx_reviews_gen ON reviews(gen_id)")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS story_assignments (
            story_key   TEXT PRIMARY KEY,
            user_id     TEXT NOT NULL,
            assigned_at TEXT NOT NULL
        )
    """)
    conn.commit()
    conn.close()


def share_generation(user_id: str, story_key: str, generated_json: str, module: str, db_path: str = _DEFAULT_DB) -> str:
    gen_id = str(uuid.uuid4())[:8]
    now = datetime.datetime.utcnow().isoformat()
    conn = sqlite3.connect(db_path)
    conn.execute(
        "INSERT INTO shared_generations (id, user_id, story_key, generated_json, module, created_at) VALUES (?,?,?,?,?,?)",
        (gen_id, user_id, story_key, generated_json, module, now),
    )
    conn.commit()
    conn.close()
    return gen_id


def get_shared_history(limit: int = 50, db_path: str = _DEFAULT_DB) -> list[dict]:
    conn = sqlite3.connect(db_path)
    rows = conn.execute(
        "SELECT id, user_id, story_key, module, review_status, created_at FROM shared_generations ORDER BY created_at DESC LIMIT ?",
        (limit,),
    ).fetchall()
    conn.close()
    return [
        {"id": r[0], "user_id": r[1], "story_key": r[2], "module": r[3], "review_status": r[4], "created_at": r[5]}
        for r in rows
    ]


def submit_review(gen_id: str, reviewer_id: str, verdict: str, comment: str = "", db_path: str = _DEFAULT_DB) -> str:
    review_id = str(uuid.uuid4())[:8]
    now = datetime.datetime.utcnow().isoformat()
    conn = sqlite3.connect(db_path)
    conn.execute(
        "INSERT INTO reviews (id, gen_id, reviewer_id, verdict, comment, created_at) VALUES (?,?,?,?,?,?)",
        (review_id, gen_id, reviewer_id, verdict, comment, now),
    )
    conn.execute("UPDATE shared_generations SET review_status = ? WHERE id = ?", (verdict, gen_id))
    conn.commit()
    conn.close()
    return review_id


def get_reviews(gen_id: str, db_path: str = _DEFAULT_DB) -> list[dict]:
    conn = sqlite3.connect(db_path)
    rows = conn.execute(
        "SELECT id, reviewer_id, verdict, comment, created_at FROM reviews WHERE gen_id = ? ORDER BY created_at",
        (gen_id,),
    ).fetchall()
    conn.close()
    return [{"id": r[0], "reviewer_id": r[1], "verdict": r[2], "comment": r[3], "created_at": r[4]} for r in rows]


def get_pending_reviews(db_path: str = _DEFAULT_DB) -> list[dict]:
    conn = sqlite3.connect(db_path)
    rows = conn.execute(
        "SELECT id, user_id, story_key, module, created_at FROM shared_generations WHERE review_status = 'pending' ORDER BY created_at",
    ).fetchall()
    conn.close()
    return [{"id": r[0], "user_id": r[1], "story_key": r[2], "module": r[3], "created_at": r[4]} for r in rows]


def assign_story(story_key: str, user_id: str, db_path: str = _DEFAULT_DB) -> None:
    now = datetime.datetime.utcnow().isoformat()
    conn = sqlite3.connect(db_path)
    conn.execute(
        "INSERT INTO story_assignments (story_key, user_id, assigned_at) VALUES (?, ?, ?) ON CONFLICT(story_key) DO UPDATE SET user_id = excluded.user_id, assigned_at = excluded.assigned_at",
        (story_key, user_id, now),
    )
    conn.commit()
    conn.close()


def get_assignment(story_key: str, db_path: str = _DEFAULT_DB) -> str | None:
    conn = sqlite3.connect(db_path)
    row = conn.execute("SELECT user_id FROM story_assignments WHERE story_key = ?", (story_key,)).fetchone()
    conn.close()
    return row[0] if row else None