-- Snapshot schema cho docker-entrypoint-initdb (chạy lần đầu khi tạo volume DB).
-- App cũng gọi Base.metadata.create_all() lúc khởi động nên đây chủ yếu là tham
-- chiếu + tạo sẵn database 'keycloak' (Phase 5 dùng chung Postgres này).

CREATE TABLE IF NOT EXISTS users (
    id            SERIAL PRIMARY KEY,
    is_deleted    BOOLEAN NOT NULL DEFAULT false,
    created_at    TIMESTAMP NOT NULL DEFAULT now(),
    updated_at    TIMESTAMP,
    email         VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255),
    display_name  VARCHAR(100),
    role          VARCHAR(16) NOT NULL DEFAULT 'user',
    is_active     BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE IF NOT EXISTS conversations (
    id         SERIAL PRIMARY KEY,
    is_deleted BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    updated_at TIMESTAMP,
    user_id    INTEGER REFERENCES users (id),
    title      VARCHAR(255),
    model      VARCHAR(32)
);
CREATE INDEX IF NOT EXISTS conversations_user_idx
    ON conversations (user_id) WHERE is_deleted = false;

CREATE TABLE IF NOT EXISTS messages (
    id              SERIAL PRIMARY KEY,
    is_deleted      BOOLEAN NOT NULL DEFAULT false,
    created_at      TIMESTAMP NOT NULL DEFAULT now(),
    conversation_id INTEGER NOT NULL REFERENCES conversations (id),
    role            VARCHAR(32) NOT NULL,
    blocks          JSONB NOT NULL
);
CREATE INDEX IF NOT EXISTS messages_conversation_idx ON messages (conversation_id);

CREATE TABLE IF NOT EXISTS artifacts (
    id              SERIAL PRIMARY KEY,
    is_deleted      BOOLEAN NOT NULL DEFAULT false,
    created_at      TIMESTAMP NOT NULL DEFAULT now(),
    conversation_id INTEGER REFERENCES conversations (id),
    type            VARCHAR(20) NOT NULL,
    title           VARCHAR(255),
    local_path      VARCHAR(512),
    preview         JSONB
);
CREATE INDEX IF NOT EXISTS artifacts_conversation_idx
    ON artifacts (conversation_id) WHERE is_deleted = false;

-- Database riêng cho Keycloak (Phase 5) — bỏ qua lỗi nếu đã tồn tại.
SELECT 'CREATE DATABASE keycloak'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'keycloak')\gexec