49 lines
3.1 KiB
SQL
49 lines
3.1 KiB
SQL
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), openid text UNIQUE NOT NULL, unionid text,
|
|
nickname text NOT NULL DEFAULT '微信用户', avatar_url text,
|
|
created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS exhibitions (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text NOT NULL, venue text NOT NULL,
|
|
address text NOT NULL DEFAULT '', starts_at timestamptz NOT NULL, ends_at timestamptz NOT NULL,
|
|
description text NOT NULL DEFAULT '', cover_url text, created_at timestamptz NOT NULL DEFAULT now(),
|
|
CHECK (ends_at > starts_at)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text UNIQUE NOT NULL, color text NOT NULL DEFAULT '#7C5CFC'
|
|
);
|
|
CREATE TABLE IF NOT EXISTS activities (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), exhibition_id uuid NOT NULL REFERENCES exhibitions(id) ON DELETE CASCADE,
|
|
title text NOT NULL, description text NOT NULL DEFAULT '', location text NOT NULL DEFAULT '',
|
|
starts_at timestamptz NOT NULL, ends_at timestamptz, capacity integer CHECK (capacity IS NULL OR capacity > 0),
|
|
published boolean NOT NULL DEFAULT false, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(),
|
|
CHECK (ends_at IS NULL OR ends_at > starts_at)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS activity_images (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), activity_id uuid NOT NULL REFERENCES activities(id) ON DELETE CASCADE,
|
|
url text NOT NULL, sort_order integer NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS activity_tags (
|
|
activity_id uuid NOT NULL REFERENCES activities(id) ON DELETE CASCADE,
|
|
tag_id uuid NOT NULL REFERENCES tags(id) ON DELETE CASCADE, PRIMARY KEY (activity_id, tag_id)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS participations (
|
|
activity_id uuid NOT NULL REFERENCES activities(id) ON DELETE CASCADE,
|
|
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE, purpose text NOT NULL DEFAULT '',
|
|
joined_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (activity_id, user_id)
|
|
);
|
|
DO $$ BEGIN CREATE TYPE group_review_status AS ENUM ('pending', 'approved', 'rejected'); EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
CREATE TABLE IF NOT EXISTS activity_groups (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), activity_id uuid NOT NULL REFERENCES activities(id) ON DELETE CASCADE,
|
|
submitted_by uuid REFERENCES users(id) ON DELETE SET NULL, name text NOT NULL, qr_image_url text NOT NULL,
|
|
note text NOT NULL DEFAULT '', status group_review_status NOT NULL DEFAULT 'pending', review_note text NOT NULL DEFAULT '',
|
|
reviewed_at timestamptz, created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_activities_exhibition_start ON activities(exhibition_id, starts_at);
|
|
CREATE INDEX IF NOT EXISTS idx_activities_published_start ON activities(published, starts_at);
|
|
CREATE INDEX IF NOT EXISTS idx_participations_activity ON participations(activity_id, joined_at);
|
|
CREATE INDEX IF NOT EXISTS idx_groups_review ON activity_groups(status, created_at);
|
|
|