105 lines
4.0 KiB
SQL
105 lines
4.0 KiB
SQL
create extension if not exists pgcrypto;
|
|
|
|
do $$ begin
|
|
create type user_role as enum ('user', 'admin');
|
|
exception
|
|
when duplicate_object then null;
|
|
end $$;
|
|
|
|
do $$ begin
|
|
create type cloud_status as enum ('pending', 'approved', 'rejected');
|
|
exception
|
|
when duplicate_object then null;
|
|
end $$;
|
|
|
|
do $$ begin
|
|
create type cloud_rarity as enum ('common', 'uncommon', 'rare');
|
|
exception
|
|
when duplicate_object then null;
|
|
end $$;
|
|
|
|
create table if not exists users (
|
|
id uuid primary key default gen_random_uuid(),
|
|
email text not null unique,
|
|
password_hash text not null,
|
|
username text not null unique,
|
|
avatar_url text,
|
|
role user_role not null default 'user',
|
|
is_disabled boolean not null default false,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists cloud_types (
|
|
id integer primary key,
|
|
name text not null,
|
|
name_en text not null,
|
|
genus text not null,
|
|
rarity cloud_rarity not null default 'common',
|
|
description text,
|
|
icon_url text,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists clouds (
|
|
id uuid primary key default gen_random_uuid(),
|
|
user_id uuid not null references users(id) on delete cascade,
|
|
cloud_type_id integer references cloud_types(id) on delete set null,
|
|
custom_cloud_type text,
|
|
image_url text not null,
|
|
thumbnail_url text,
|
|
latitude numeric(9, 6),
|
|
longitude numeric(9, 6),
|
|
location_name text,
|
|
description text,
|
|
captured_at timestamptz,
|
|
status cloud_status not null default 'pending',
|
|
is_hidden boolean not null default false,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists user_collections (
|
|
id uuid primary key default gen_random_uuid(),
|
|
user_id uuid not null references users(id) on delete cascade,
|
|
cloud_type_id integer not null references cloud_types(id) on delete cascade,
|
|
first_cloud_id uuid references clouds(id) on delete set null,
|
|
unlocked_at timestamptz not null default now(),
|
|
unique (user_id, cloud_type_id)
|
|
);
|
|
|
|
create index if not exists idx_clouds_public_gallery
|
|
on clouds (status, is_hidden, created_at desc);
|
|
|
|
create index if not exists idx_clouds_type_public
|
|
on clouds (cloud_type_id, status, is_hidden, created_at desc);
|
|
|
|
create index if not exists idx_clouds_map_captured
|
|
on clouds (captured_at, status, is_hidden)
|
|
where latitude is not null and longitude is not null;
|
|
|
|
create index if not exists idx_clouds_user_created
|
|
on clouds (user_id, created_at desc);
|
|
|
|
create index if not exists idx_user_collections_user
|
|
on user_collections (user_id, unlocked_at);
|
|
|
|
insert into cloud_types (id, name, name_en, genus, rarity, description)
|
|
values
|
|
(1, '积云', 'Cumulus', '低云', 'common', '轮廓清晰、底部较平的棉花状云，常见于晴朗天气。'),
|
|
(2, '层云', 'Stratus', '低云', 'common', '低而均匀的灰白色云层，常覆盖天空并带来阴沉观感。'),
|
|
(3, '卷云', 'Cirrus', '高云', 'common', '纤细如羽毛的高云，由冰晶组成，常预示天气变化。'),
|
|
(4, '积雨云', 'Cumulonimbus', '直展云', 'rare', '垂直发展旺盛的雷暴云，可带来强降水、雷电或冰雹。'),
|
|
(5, '层积云', 'Stratocumulus', '低云', 'common', '成片或成层的块状云，常有明暗相间的结构。'),
|
|
(6, '高积云', 'Altocumulus', '中云', 'uncommon', '中高度的小块状或波状云，常成群排列。'),
|
|
(7, '高层云', 'Altostratus', '中云', 'uncommon', '灰蓝色或灰色的中层云幕，常使太阳呈毛玻璃状。'),
|
|
(8, '雨层云', 'Nimbostratus', '中云', 'uncommon', '厚重暗灰的降水云层，通常带来持续性降雨或降雪。'),
|
|
(9, '卷层云', 'Cirrostratus', '高云', 'uncommon', '薄幕状高云，常覆盖大范围天空并产生日晕或月晕。'),
|
|
(10, '卷积云', 'Cirrocumulus', '高云', 'rare', '细小颗粒状高云，常呈鱼鳞状排列。')
|
|
on conflict (id) do update set
|
|
name = excluded.name,
|
|
name_en = excluded.name_en,
|
|
genus = excluded.genus,
|
|
rarity = excluded.rarity,
|
|
description = excluded.description;
|