-- ============================================================================
-- 香港裝修師傅大本營 · 打卡機 (Supabase PostgreSQL 一鍵建表 + 防刪改 WORM + RLS 保安程序)
-- 使用方法：複製全部內容 ➔ 貼入 Supabase 左側「SQL Editor」➔ 撳右下角「Run」即可完成！
-- ============================================================================
-- 1. 裝修同行判頭租戶表 (Multi-Tenant SaaS：支援創辦人監控已付費/未付費、加入日期及到期日)
CREATE TABLE IF NOT EXISTS saas_tenants (
id TEXT PRIMARY KEY,
company_name TEXT NOT NULL,
boss_name TEXT,
boss_phone VARCHAR(12),
boss_pin_hash TEXT DEFAULT '8888',
plan_tier TEXT DEFAULT 'PRO_10',
max_workers INT DEFAULT 10,
monthly_fee INT DEFAULT 50,
paid_status TEXT DEFAULT 'PAID',
joined_date DATE DEFAULT CURRENT_DATE,
expiry_date DATE DEFAULT (CURRENT_DATE + INTERVAL '30 days'),
subscription_active BOOLEAN DEFAULT TRUE,
raw_json JSONB
);
-- 1B. 創辦人全港師傅公告欄及商業廣告表 (支援超連結 URL 一撳打開網頁)
CREATE TABLE IF NOT EXISTS app_announcements (
id TEXT PRIMARY KEY,
badge TEXT NOT NULL,
title TEXT NOT NULL,
body TEXT NOT NULL,
target_url TEXT,
cta_text TEXT DEFAULT '🔗 撳此打開網址',
active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
raw_json JSONB
);
-- 2. 全港師傅主檔案表 (1個電話 = 1個獨立師傅戶口，支援跨工程公司開工)
CREATE TABLE IF NOT EXISTS workers (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
phone VARCHAR(8) UNIQUE NOT NULL,
pin TEXT DEFAULT '8888',
bound_device_id TEXT,
daily_wage INT DEFAULT 1600,
trade TEXT DEFAULT '裝修師傅',
icon TEXT DEFAULT '👷',
avatar_key TEXT DEFAULT 'w1',
created_at TIMESTAMPTZ DEFAULT NOW(),
raw_json JSONB
);
-- 2B. 師傅與多老細/多工程公司關聯表 (M:N 多對多關聯：1位師傅可同時跟多個老細開工)
CREATE TABLE IF NOT EXISTS company_worker_memberships (
membership_id TEXT PRIMARY KEY,
tenant_id TEXT REFERENCES saas_tenants(id) ON DELETE CASCADE,
worker_id TEXT REFERENCES workers(id) ON DELETE CASCADE,
agreed_daily_wage INT DEFAULT 1600,
active_status BOOLEAN DEFAULT TRUE,
UNIQUE(tenant_id, worker_id)
);
-- 3. 裝修工地項目表 (地址建立後永久鎖定 + 支援專屬防偽 QR Code 印製)
CREATE TABLE IF NOT EXISTS sites (
id TEXT PRIMARY KEY,
tenant_id TEXT,
boss_name TEXT,
qr_token TEXT,
name TEXT NOT NULL,
district TEXT,
system_address TEXT NOT NULL,
lat NUMERIC(9,6),
lng NUMERIC(9,6),
project_code TEXT,
raw_json JSONB
);
-- 4. 防篡改實時打卡紀錄表 (WORM 永久保存，支援早退半工 -0.5工 / 揀錯盤沖銷 -1.0工 平帳)
CREATE TABLE IF NOT EXISTS checkin_records (
id TEXT PRIMARY KEY,
worker_id TEXT,
worker_name TEXT,
worker_phone VARCHAR(8),
trade TEXT,
site_id TEXT,
site_name TEXT,
system_address TEXT,
checkin_method TEXT DEFAULT 'GPS_LOCK',
qr_token TEXT,
work_units NUMERIC(4,2) DEFAULT 1.0,
approval_status TEXT DEFAULT 'APPROVED',
record_type_label TEXT,
remark TEXT,
lat NUMERIC(9,6),
lng NUMERIC(9,6),
distance_meters INT DEFAULT 10,
device_id TEXT,
month_key VARCHAR(7) NOT NULL,
date_str VARCHAR(10),
timestamp TEXT,
lock_hash TEXT NOT NULL,
raw_json JSONB
);
-- 5. 雲端 PDF 出糧單永久檔案庫 (每生成一張 PDF 自動寫入雲端)
CREATE TABLE IF NOT EXISTS payslip_pdfs (
pdf_id TEXT PRIMARY KEY,
worker_id TEXT,
worker_name TEXT,
worker_phone VARCHAR(8),
trade TEXT,
month_key VARCHAR(7) NOT NULL,
period_key TEXT,
period_label TEXT NOT NULL,
site_breakdown_text TEXT NOT NULL,
work_days NUMERIC(5,2) NOT NULL,
daily_wage INT NOT NULL,
total_amount INT NOT NULL,
status TEXT DEFAULT '待發薪金',
created_at TEXT,
lock_hash TEXT NOT NULL,
raw_json JSONB
);
-- 6. 師傅請求出糧及審批狀態表 (待發薪金 -> 已發薪金)
CREATE TABLE IF NOT EXISTS payroll_requests (
id TEXT PRIMARY KEY,
pdf_id TEXT,
worker_id TEXT,
worker_name TEXT,
worker_phone VARCHAR(8),
trade TEXT,
month_key VARCHAR(7) NOT NULL,
period_key TEXT,
period_label TEXT NOT NULL,
site_breakdown_text TEXT NOT NULL,
work_days NUMERIC(5,2) NOT NULL,
daily_wage INT NOT NULL,
total_amount INT NOT NULL,
status TEXT DEFAULT '待發薪金',
created_at TEXT,
paid_at TEXT,
raw_json JSONB
);
-- 7. 【勞工處法定舉證保護】禁止任何人(包括老細)刪除打卡及PDF糧單紀錄之觸發器
CREATE OR REPLACE FUNCTION prevent_record_deletion()
RETURNS TRIGGER AS $$
BEGIN
RAISE EXCEPTION '⚖️ 法定防篡改保護：打卡及出糧紀錄受 WORM 保護不可刪除，如有早退或揀錯盤請以沖銷紀錄平帳！';
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS lock_checkin_no_delete ON checkin_records;
CREATE TRIGGER lock_checkin_no_delete
BEFORE DELETE ON checkin_records
FOR EACH ROW EXECUTE FUNCTION prevent_record_deletion();
DROP TRIGGER IF EXISTS lock_payslip_pdf_no_delete ON payslip_pdfs;
CREATE TRIGGER lock_payslip_pdf_no_delete
BEFORE DELETE ON payslip_pdfs
FOR EACH ROW EXECUTE FUNCTION prevent_record_deletion();
-- 8. 開啟 Row Level Security (RLS) 及允許前端 Anon Key 安全讀寫新增
ALTER TABLE saas_tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE app_announcements ENABLE ROW LEVEL SECURITY;
ALTER TABLE workers ENABLE ROW LEVEL SECURITY;
ALTER TABLE sites ENABLE ROW LEVEL SECURITY;
ALTER TABLE checkin_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE payslip_pdfs ENABLE ROW LEVEL SECURITY;
ALTER TABLE payroll_requests ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "allow_anon_all_tenants" ON saas_tenants;
CREATE POLICY "allow_anon_all_tenants" ON saas_tenants FOR ALL USING (true) WITH CHECK (true);
DROP POLICY IF EXISTS "allow_anon_all_announcements" ON app_announcements;
CREATE POLICY "allow_anon_all_announcements" ON app_announcements FOR ALL USING (true) WITH CHECK (true);
DROP POLICY IF EXISTS "allow_anon_all_workers" ON workers;
CREATE POLICY "allow_anon_all_workers" ON workers FOR ALL USING (true) WITH CHECK (true);
DROP POLICY IF EXISTS "allow_anon_all_sites" ON sites;
CREATE POLICY "allow_anon_all_sites" ON sites FOR ALL USING (true) WITH CHECK (true);
DROP POLICY IF EXISTS "allow_anon_all_checkins" ON checkin_records;
CREATE POLICY "allow_anon_all_checkins" ON checkin_records FOR ALL USING (true) WITH CHECK (true);
DROP POLICY IF EXISTS "allow_anon_all_pdfs" ON payslip_pdfs;
CREATE POLICY "allow_anon_all_pdfs" ON payslip_pdfs FOR ALL USING (true) WITH CHECK (true);
DROP POLICY IF EXISTS "allow_anon_all_payroll" ON payroll_requests;
CREATE POLICY "allow_anon_all_payroll" ON payroll_requests FOR ALL USING (true) WITH CHECK (true);
