私人收藏管理｜SQL 安裝檔

請把下面 SQL 複製後，貼到 Supabase → SQL Editor → New query，再按 Run。
不要貼 CSS。開頭必須是註解 -- YUNYANG PRIVATE COLLECTION 或 alter table / create table。
第一次：複製「完整安裝」。若 P0 已跑過、只要補出貨：複製「只跑出貨」。藏館權限：複製「私人藏館權限」。安全加固（禁止匿名讀客戶）：複製「存取加固」。
-- ============================================================
-- YUNYANG PRIVATE COLLECTION｜私人收藏管理（P0）
-- 在現有 clients / purchases 上擴充，不砍舊表。
-- 於 Supabase SQL Editor 執行本檔。
-- ============================================================

-- ---------- 客戶擴充欄位（Client Profile）----------
alter table public.clients
  add column if not exists client_code text,
  add column if not exists nickname text,
  add column if not exists phone text,
  add column if not exists line_id text,
  add column if not exists instagram_id text,
  -- CLIENT_STAGE｜客戶階段（正式身分）
  add column if not exists client_stage text default 'BESPOKE_CLIENT',
  -- Candidate 只是系統提醒，非正式身分
  add column if not exists collector_candidate boolean default false,
  add column if not exists private_client_candidate boolean default false,
  -- 正式身分核准狀態
  add column if not exists collector_status text default 'NONE',
  add column if not exists private_client_status text default 'NONE',
  add column if not exists first_purchase_date date,
  add column if not exists last_purchase_date date,
  add column if not exists total_spend numeric default 0,
  add column if not exists order_count integer default 0,
  add column if not exists average_order_value numeric default 0,
  add column if not exists highest_single_order numeric default 0,
  -- 偏好 Preference
  add column if not exists favorite_categories text,
  add column if not exists favorite_stones text,
  add column if not exists favorite_colors text,
  add column if not exists preferred_style text,
  add column if not exists preferred_size text,
  add column if not exists preferred_bead_size text,
  add column if not exists preferred_budget_min numeric,
  add column if not exists preferred_budget_max numeric,
  add column if not exists quality_preference text,
  add column if not exists disliked_stones text,
  add column if not exists disliked_styles text,
  add column if not exists special_preferences text,
  add column if not exists collector_type text,
  add column if not exists price_sensitivity text,
  add column if not exists new_product_preference text,
  add column if not exists private_sourcing_interest boolean default false,
  add column if not exists collection_management_interest boolean default false,
  -- PRE_OWNED_ACCEPTANCE｜是否接受前手收藏（偏好標籤，不是會員種類）
  add column if not exists pre_owned_acceptance text default 'UNKNOWN',
  -- RELEASE_WILLINGNESS｜是否願意釋出收藏（偏好標籤）
  add column if not exists release_willingness text default 'UNKNOWN';

comment on column public.clients.client_stage is '正式客戶階段：BESPOKE_CLIENT訂製客戶 / COLLECTOR收藏家 / PRIVATE_CLIENT私人客戶';
comment on column public.clients.collector_candidate is '收藏家候選人（系統提醒，非正式身分）';
comment on column public.clients.private_client_candidate is '私人客戶候選人（系統提醒，非正式身分）';
comment on column public.clients.pre_owned_acceptance is '是否接受前手收藏：YES/CONDITIONAL/NO/UNKNOWN（僅偏好標籤）';
comment on column public.clients.release_willingness is '是否願意釋出收藏：YES/MAYBE/NO/UNKNOWN（僅偏好標籤）';

create unique index if not exists clients_client_code_uidx on public.clients (client_code) where client_code is not null;
create index if not exists clients_stage_idx on public.clients (client_stage);
create index if not exists clients_phone_idx on public.clients (phone);
create index if not exists clients_line_idx on public.clients (line_id);
create index if not exists clients_ig_idx on public.clients (instagram_id);

-- ---------- 收藏品 Collection（以物為中心）----------
create table if not exists public.yy_collections (
  id uuid primary key default gen_random_uuid(),
  -- COLLECTOR_ID｜收藏編號（永久不變）例：YY-MOON-2026-001
  collector_id text unique,
  category text,
  stone_type text,
  product_name text not null,
  year integer,
  source text,
  supplier_internal_id text,
  purchase_cost numeric,
  initial_list_price numeric,
  original_sale_price numeric,
  quality_grade text,
  collector_grade text,
  -- STATUS｜商品狀態（改變必須寫 timeline，不可直接覆蓋歷史語意）
  status text not null default 'IN_STOCK',
  diameter numeric,
  dimensions text,
  weight numeric,
  hole_size text,
  crystal_clarity text,
  light_performance text,
  color_description text,
  natural_features text,
  defects text,
  condition_grade text,
  processing_status text,
  drilled boolean default false,
  hole_modified boolean default false,
  polished boolean default false,
  resized boolean default false,
  redesigned boolean default false,
  accessories text,
  processing_notes text,
  current_owner_client_id uuid references public.clients(id) on delete set null,
  final_condition_verified boolean default false,
  record_level text default 'LIGHTWEIGHT', -- LIGHTWEIGHT一般 / FULL高端完整履歷
  notes text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

comment on table public.yy_collections is '收藏品主檔（Collection）。高端商品必須 Full Record + Collector ID';
comment on column public.yy_collections.collector_id is '收藏編號 COLLECTOR_ID，永久不變';
comment on column public.yy_collections.status is 'IN_STOCK庫存中 / RESERVED保留 / SOLD已售 / CLIENT_COLLECTION客人收藏中 / RETURNED_FOR_SERVICE售後回廠 / PRIVATE_SALE_TEST讓藏測試 / ARCHIVE封存 / SOLD_ARCHIVE已售封存';
comment on column public.yy_collections.final_condition_verified is 'FINAL CONDITION｜出貨前最終狀態確認是否完成';

create index if not exists yy_collections_status_idx on public.yy_collections (status);
create index if not exists yy_collections_stone_idx on public.yy_collections (stone_type);
create index if not exists yy_collections_owner_idx on public.yy_collections (current_owner_client_id);

-- ---------- Timeline｜歷程（只新增、不覆蓋）----------
create table if not exists public.yy_collection_timeline (
  id uuid primary key default gen_random_uuid(),
  collection_id uuid not null references public.yy_collections(id) on delete cascade,
  event_type text not null, -- STATUS_CHANGE / OWNERSHIP / SERVICE / NOTE / FINAL_CONDITION / PRIVATE_SALE
  from_status text,
  to_status text,
  from_client_id uuid references public.clients(id) on delete set null,
  to_client_id uuid references public.clients(id) on delete set null,
  note text,
  operator text,
  meta jsonb default '{}'::jsonb,
  created_at timestamptz default now()
);

comment on table public.yy_collection_timeline is '收藏履歷 Timeline：狀態／所有權／加工等，只 append 不覆蓋';

create index if not exists yy_collection_timeline_cid_idx on public.yy_collection_timeline (collection_id, created_at desc);

-- ---------- Wishlist｜想找清單 ----------
create table if not exists public.yy_wishlists (
  id uuid primary key default gen_random_uuid(),
  client_id uuid not null references public.clients(id) on delete cascade,
  stone_type text,
  category text,
  desired_size_min numeric,
  desired_size_max numeric,
  desired_quality text,
  desired_color text,
  desired_features text,
  budget_min numeric,
  budget_max numeric,
  urgency text default 'NORMAL',
  priority integer default 3,
  status text not null default 'ACTIVE',
  notes text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

comment on table public.yy_wishlists is 'Wishlist｜想找清單。客人明確說「有這種幫我找」時建立';
comment on column public.yy_wishlists.status is 'ACTIVE進行中 / MATCHED已媒合 / CONTACTED已聯絡 / PURCHASED已購入 / PAUSED暫停 / CLOSED關閉';

create index if not exists yy_wishlists_client_idx on public.yy_wishlists (client_id);
create index if not exists yy_wishlists_status_idx on public.yy_wishlists (status);
create index if not exists yy_wishlists_stone_idx on public.yy_wishlists (stone_type);

-- ---------- Interest Event｜有意義的關注紀錄（高端商品）----------
create table if not exists public.yy_interest_events (
  id uuid primary key default gen_random_uuid(),
  client_id uuid not null references public.clients(id) on delete cascade,
  collection_id uuid references public.yy_collections(id) on delete set null,
  event_type text not null, -- VIEWED / ASKED_PRICE / REQUESTED_VIDEO / INTERESTED / PASSED / WAITLIST / PURCHASED
  pass_reason text, -- PRICE / SIZE / COLOR / QUALITY / STYLE / BUDGET / WAITING / OTHER
  notes text,
  created_at timestamptz default now()
);

comment on table public.yy_interest_events is 'Interest Event｜高端商品有意義的關注（問價、要影片、考慮中等）';

-- ---------- Service Event｜加工／售後紀錄 ----------
create table if not exists public.yy_service_events (
  id uuid primary key default gen_random_uuid(),
  collection_id uuid not null references public.yy_collections(id) on delete cascade,
  client_id uuid references public.clients(id) on delete set null,
  service_date date not null default current_date,
  service_type text not null, -- DRILL打孔 / HOLE_MODIFICATION擴孔 / RESIZE改尺寸 / REDESIGN重設計 / REPAIR維修 / CLEANING清潔 / PURIFICATION淨化 / INSPECTION檢查 / RE_CERTIFICATION重新認證 / OTHER
  before_photos text[],
  after_photos text[],
  notes text,
  operator text,
  created_at timestamptz default now()
);

comment on table public.yy_service_events is 'Service Event｜加工／售後：禁止改原始資料假裝商品本來如此';

-- ---------- Private Client Lead｜私人客戶线索（P0 入口）----------
create table if not exists public.yy_private_client_leads (
  id uuid primary key default gen_random_uuid(),
  client_id uuid not null references public.clients(id) on delete cascade,
  reason text,
  next_action text default 'OWNER_COLLECTION_REVIEW_INVITE',
  assigned_to text default 'OWNER',
  status text not null default 'OPEN', -- OPEN / IN_REVIEW / APPROVED / DECLINED / CLOSED
  notes text,
  created_by text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

comment on table public.yy_private_client_leads is 'Private Client Lead｜私人客戶线索：員工不可承諾回收／讓藏，統一交 OWNER 盤點邀請';

-- ---------- Today Tasks｜今日待辦 ----------
create table if not exists public.yy_today_tasks (
  id uuid primary key default gen_random_uuid(),
  task_type text not null,
  title text not null,
  related_client_id uuid references public.clients(id) on delete set null,
  related_collection_id uuid references public.yy_collections(id) on delete set null,
  related_wishlist_id uuid references public.yy_wishlists(id) on delete set null,
  priority integer default 3,
  status text not null default 'OPEN', -- OPEN / ASSIGNED / DONE / SNOOZED
  assigned_to text,
  snooze_until date,
  meta jsonb default '{}'::jsonb,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

comment on table public.yy_today_tasks is 'Today Tasks｜今日待辦：Wishlist回覆、媒合、Final Condition、出貨、缺漏資料等';

create index if not exists yy_today_tasks_status_idx on public.yy_today_tasks (status);

-- ---------- RLS（沿用 is_admin）----------
alter table public.yy_collections enable row level security;
alter table public.yy_collection_timeline enable row level security;
alter table public.yy_wishlists enable row level security;
alter table public.yy_interest_events enable row level security;
alter table public.yy_service_events enable row level security;
alter table public.yy_private_client_leads enable row level security;
alter table public.yy_today_tasks enable row level security;

drop policy if exists yy_collections_admin_all on public.yy_collections;
create policy yy_collections_admin_all on public.yy_collections for all using (public.is_admin()) with check (public.is_admin());

drop policy if exists yy_collection_timeline_admin_all on public.yy_collection_timeline;
create policy yy_collection_timeline_admin_all on public.yy_collection_timeline for all using (public.is_admin()) with check (public.is_admin());

drop policy if exists yy_wishlists_admin_all on public.yy_wishlists;
create policy yy_wishlists_admin_all on public.yy_wishlists for all using (public.is_admin()) with check (public.is_admin());

drop policy if exists yy_interest_events_admin_all on public.yy_interest_events;
create policy yy_interest_events_admin_all on public.yy_interest_events for all using (public.is_admin()) with check (public.is_admin());

drop policy if exists yy_service_events_admin_all on public.yy_service_events;
create policy yy_service_events_admin_all on public.yy_service_events for all using (public.is_admin()) with check (public.is_admin());

drop policy if exists yy_private_client_leads_admin_all on public.yy_private_client_leads;
create policy yy_private_client_leads_admin_all on public.yy_private_client_leads for all using (public.is_admin()) with check (public.is_admin());

drop policy if exists yy_today_tasks_admin_all on public.yy_today_tasks;
create policy yy_today_tasks_admin_all on public.yy_today_tasks for all using (public.is_admin()) with check (public.is_admin());

-- ========== 出貨 SOP（Shipping）追加 ==========

-- ============================================================
-- YUNYANG PRIVATE COLLECTION｜出貨 SOP（Shipping）
-- 若已執行過 supabase-private-collection.sql，再執行本檔即可。
-- ============================================================

create table if not exists public.yy_shipments (
  id uuid primary key default gen_random_uuid(),
  collection_id uuid not null references public.yy_collections(id) on delete restrict,
  client_id uuid not null references public.clients(id) on delete restrict,
  -- Checklist｜出貨檢查（中文說明見 comment）
  check_client boolean not null default false,
  check_order boolean not null default false,
  check_payment boolean not null default false,
  check_collector_id boolean not null default false,
  check_final_condition boolean not null default false,
  check_shipping_info boolean not null default false,
  order_ref text,                 -- 訂單／交易編號（可填手動編號）
  payment_status text,            -- PAID已付款 / PARTIAL部分 / UNPAID未付
  payment_note text,
  recipient_name text,
  recipient_phone text,
  shipping_address text,
  carrier text,                   -- 宅配／郵寄／面交…
  tracking_number text,
  shipped_at timestamptz,
  status text not null default 'DRAFT', -- DRAFT草稿 / READY待出貨 / SHIPPED已出貨 / CANCELLED取消
  blocked_reason text,            -- 例如 FINAL CONDITION 未完成
  notes text,
  operator text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

comment on table public.yy_shipments is '出貨紀錄 Shipping Record。完成後收藏品狀態：SOLD → CLIENT_COLLECTION';
comment on column public.yy_shipments.check_client is '已確認客戶 Client';
comment on column public.yy_shipments.check_order is '已確認訂單 Order';
comment on column public.yy_shipments.check_payment is '已確認付款 Payment';
comment on column public.yy_shipments.check_collector_id is '已確認收藏編號 Collector ID';
comment on column public.yy_shipments.check_final_condition is '已完成出貨前最終狀態確認 Final Condition';
comment on column public.yy_shipments.check_shipping_info is '已確認收件資料 Shipping';

create index if not exists yy_shipments_status_idx on public.yy_shipments (status);
create index if not exists yy_shipments_client_idx on public.yy_shipments (client_id);
create index if not exists yy_shipments_collection_idx on public.yy_shipments (collection_id);

alter table public.yy_shipments enable row level security;
drop policy if exists yy_shipments_admin_all on public.yy_shipments;
create policy yy_shipments_admin_all on public.yy_shipments
  for all using (public.is_admin()) with check (public.is_admin());

-- 出貨照片／條件欄位補強（若尚無）
alter table public.yy_collections
  add column if not exists final_condition_notes text,
  add column if not exists final_condition_at timestamptz,
  add column if not exists final_condition_by text;