-- 000003: 审核表、收藏表、评分表

-- 应用审核表
CREATE TABLE app_reviews (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id          UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
    version         VARCHAR(20) NOT NULL,
    submitter_id    UUID NOT NULL REFERENCES users(id),
    reviewer_id     UUID REFERENCES users(id),
    status          VARCHAR(20) NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'approved', 'rejected', 'withdrawn')),
    submit_comment  TEXT,
    review_comment  TEXT,
    submitted_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    reviewed_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_reviews_app ON app_reviews(app_id);
CREATE INDEX idx_reviews_status ON app_reviews(status);
CREATE INDEX idx_reviews_submitter ON app_reviews(submitter_id);

-- 收藏表
CREATE TABLE app_favorites (
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    app_id      UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, app_id)
);

CREATE INDEX idx_favorites_user ON app_favorites(user_id);
CREATE INDEX idx_favorites_app ON app_favorites(app_id);

-- 评分表
CREATE TABLE app_ratings (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    app_id      UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    score       SMALLINT NOT NULL CHECK (score >= 1 AND score <= 5),
    comment     TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(app_id, user_id)
);

CREATE INDEX idx_ratings_app ON app_ratings(app_id);
CREATE INDEX idx_ratings_user ON app_ratings(user_id);

CREATE TRIGGER update_ratings_updated_at
    BEFORE UPDATE ON app_ratings
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();