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;
