-- 000004: 使用记录表

-- 使用明细表
CREATE TABLE app_usage_logs (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id            UUID NOT NULL REFERENCES applications(id),
    user_id           UUID NOT NULL REFERENCES users(id),
    dept_id           UUID REFERENCES departments(id),

    conversation_id   VARCHAR(255),
    message_count     INTEGER NOT NULL DEFAULT 1,

    prompt_tokens     INTEGER NOT NULL DEFAULT 0,
    completion_tokens INTEGER NOT NULL DEFAULT 0,
    total_tokens      INTEGER NOT NULL DEFAULT 0,
    model_name        VARCHAR(100),
    estimated_cost    DECIMAL(10, 6) NOT NULL DEFAULT 0,

    duration_ms       INTEGER,
    is_successful     BOOLEAN NOT NULL DEFAULT true,
    error_message     TEXT,
    client_type       VARCHAR(20) DEFAULT 'web'
                      CHECK (client_type IN ('web', 'wechat', 'dingtalk', 'feishu', 'api')),

    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_usage_app ON app_usage_logs(app_id);
CREATE INDEX idx_usage_user ON app_usage_logs(user_id);
CREATE INDEX idx_usage_dept ON app_usage_logs(dept_id);
CREATE INDEX idx_usage_created ON app_usage_logs(created_at);
CREATE INDEX idx_usage_model ON app_usage_logs(model_name);

-- 日汇总表（定时任务聚合）
CREATE TABLE app_usage_daily (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    date            DATE NOT NULL,
    app_id          UUID NOT NULL REFERENCES applications(id),
    dept_id         UUID REFERENCES departments(id),

    total_requests  BIGINT NOT NULL DEFAULT 0,
    unique_users    INTEGER NOT NULL DEFAULT 0,
    total_tokens    BIGINT NOT NULL DEFAULT 0,
    total_cost      DECIMAL(12, 6) NOT NULL DEFAULT 0,
    avg_duration_ms INTEGER NOT NULL DEFAULT 0,
    error_count     INTEGER NOT NULL DEFAULT 0,

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(date, app_id, dept_id)
);

CREATE INDEX idx_daily_date ON app_usage_daily(date);
CREATE INDEX idx_daily_app ON app_usage_daily(app_id);
CREATE INDEX idx_daily_dept ON app_usage_daily(dept_id);