-- ============================================================
-- 大樓財務雲端管理平台 — 完整資料庫 Schema
-- PostgreSQL 15+ (Supabase)
-- 規則：所有金額欄位使用 NUMERIC(19,4)，嚴禁 FLOAT/DOUBLE
-- ============================================================

-- ① 啟用必要擴充套件
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- 模糊搜尋支援

-- ② 自定義 ENUM 類型
CREATE TYPE user_role AS ENUM ('committee_admin', 'guard', 'resident');
CREATE TYPE plan_tier AS ENUM ('free', 'pro', 'enterprise');
CREATE TYPE unit_type AS ENUM ('residential', 'commercial', 'parking', 'storage');
CREATE TYPE invoice_status AS ENUM ('pending', 'partial', 'paid', 'overdue', 'voided');
CREATE TYPE payment_method AS ENUM ('cash', 'transfer', 'check');
CREATE TYPE payment_status AS ENUM ('completed', 'pending_review', 'refunded', 'voided');
CREATE TYPE petty_cash_status AS ENUM ('pending', 'approved', 'rejected');
CREATE TYPE fee_type AS ENUM ('fixed', 'per_sqm');

-- ③ buildings — 多租戶核心表（每棟大樓一個 Workspace）
CREATE TABLE public.buildings (
  id              uuid          PRIMARY KEY DEFAULT uuid_generate_v4(),
  name            text          NOT NULL,
  address         text          NOT NULL,
  total_units     integer       NOT NULL DEFAULT 0,
  plan_tier       plan_tier     NOT NULL DEFAULT 'free',
  is_active       boolean       NOT NULL DEFAULT true,
  timezone        text          NOT NULL DEFAULT 'Asia/Taipei',
  settings        jsonb         NOT NULL DEFAULT '{}',  -- 大樓級設定
  metadata        jsonb         NOT NULL DEFAULT '{}',  -- 擴充欄位
  created_at      timestamptz   NOT NULL DEFAULT now(),
  updated_at      timestamptz   NOT NULL DEFAULT now()
);

-- ④ profiles — 擴展 Supabase auth.users，關聯使用者角色
CREATE TABLE public.profiles (
  id              uuid          PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  building_id     uuid          NOT NULL REFERENCES buildings(id) ON DELETE CASCADE,
  role            user_role     NOT NULL,
  full_name       text          NOT NULL,
  phone           text,
  avatar_url      text,
  is_active       boolean       NOT NULL DEFAULT true,
  last_login_at   timestamptz,
  metadata        jsonb         NOT NULL DEFAULT '{}',
  created_at      timestamptz   NOT NULL DEFAULT now(),
  updated_at      timestamptz   NOT NULL DEFAULT now()
);

-- ⑤ units — 住戶單位（每戶一筆，balance 為核心財務狀態）
CREATE TABLE public.units (
  id              uuid            PRIMARY KEY DEFAULT uuid_generate_v4(),
  building_id     uuid            NOT NULL REFERENCES buildings(id),
  unit_number     text            NOT NULL,                -- 例："3F-01"
  floor           integer,
  unit_type       unit_type       NOT NULL DEFAULT 'residential',
  area_sqm        numeric(10,2),                          -- 坪數（可選）
  owner_profile_id uuid           REFERENCES profiles(id),
  resident_profile_id uuid        REFERENCES profiles(id),
  -- ⚠️ 核心財務欄位：正數=預繳，負數=欠費，嚴禁使用 FLOAT
  balance         numeric(19,4)  NOT NULL DEFAULT 0.0000,
  is_active       boolean         NOT NULL DEFAULT true,
  metadata        jsonb           NOT NULL DEFAULT '{}',
  created_at      timestamptz     NOT NULL DEFAULT now(),
  updated_at      timestamptz     NOT NULL DEFAULT now(),
  UNIQUE (building_id, unit_number)  -- 同棟大樓內戶號唯一
);

-- ⑥ billing_configs — 計費設定（支援多類型費率）
CREATE TABLE public.billing_configs (
  id              uuid          PRIMARY KEY DEFAULT uuid_generate_v4(),
  building_id     uuid          NOT NULL REFERENCES buildings(id),
  name            text          NOT NULL,              -- 費用名稱，如"管理費"
  unit_type       unit_type     NOT NULL,
  fee_type        fee_type      NOT NULL DEFAULT 'fixed',
  rate            numeric(19,4) NOT NULL,             -- 月費金額或每坪單價
  effective_from  date          NOT NULL,
  effective_to    date,                                -- NULL = 無限期有效
  is_active       boolean       NOT NULL DEFAULT true,
  metadata        jsonb         NOT NULL DEFAULT '{}',
  created_at      timestamptz   NOT NULL DEFAULT now()
);

-- ⑦ invoices — 帳單（FIFO 沖帳的主體）
CREATE TABLE public.invoices (
  id                  uuid            PRIMARY KEY DEFAULT uuid_generate_v4(),
  building_id         uuid            NOT NULL REFERENCES buildings(id),
  unit_id             uuid            NOT NULL REFERENCES units(id),
  billing_config_id   uuid            REFERENCES billing_configs(id),
  invoice_number      text            NOT NULL,          -- 系統產生，如"INV-2025-03-001"
  billing_period      daterange       NOT NULL,          -- 計費期間
  due_date            date            NOT NULL,          -- FIFO 排序的關鍵欄位
  amount              numeric(19,4)  NOT NULL,
  paid_amount         numeric(19,4)  NOT NULL DEFAULT 0.0000,
  status              invoice_status  NOT NULL DEFAULT 'pending',
  description         text,
  metadata            jsonb           NOT NULL DEFAULT '{}',
  created_by          uuid            REFERENCES profiles(id),
  voided_at           timestamptz,
  voided_by           uuid            REFERENCES profiles(id),
  created_at          timestamptz     NOT NULL DEFAULT now(),
  updated_at          timestamptz     NOT NULL DEFAULT now(),
  UNIQUE (building_id, invoice_number),
  -- 確保金額非負
  CONSTRAINT invoices_amount_positive     CHECK (amount > 0),
  CONSTRAINT invoices_paid_not_exceed     CHECK (paid_amount <= amount),
  CONSTRAINT invoices_paid_non_negative   CHECK (paid_amount >= 0)
);

-- ⑧ payments — 收款紀錄（保全登記的原始收款）
CREATE TABLE public.payments (
  id              uuid            PRIMARY KEY DEFAULT uuid_generate_v4(),
  building_id     uuid            NOT NULL REFERENCES buildings(id),
  unit_id         uuid            NOT NULL REFERENCES units(id),
  guard_id        uuid            NOT NULL REFERENCES profiles(id), -- 經手人
  amount          numeric(19,4)  NOT NULL,
  method          payment_method  NOT NULL,
  status          payment_status  NOT NULL DEFAULT 'completed',
  notes           text,
  reference_no    text,               -- 轉帳單號
  paid_at         timestamptz     NOT NULL DEFAULT now(),
  metadata        jsonb           NOT NULL DEFAULT '{}',
  created_at      timestamptz     NOT NULL DEFAULT now(),
  CONSTRAINT payments_amount_positive CHECK (amount > 0)
);

-- ⑨ payment_allocations — FIFO 沖帳明細（核心關係表）
CREATE TABLE public.payment_allocations (
  id                uuid            PRIMARY KEY DEFAULT uuid_generate_v4(),
  building_id       uuid            NOT NULL REFERENCES buildings(id),
  payment_id        uuid            NOT NULL REFERENCES payments(id),
  invoice_id        uuid            NOT NULL REFERENCES invoices(id),
  allocated_amount  numeric(19,4)  NOT NULL,
  created_at        timestamptz     NOT NULL DEFAULT now(),
  CONSTRAINT allocations_positive CHECK (allocated_amount > 0)
);

-- ⑩ petty_cash — 零用金支出（保全登記，管委員審核）
CREATE TABLE public.petty_cash (
  id              uuid                PRIMARY KEY DEFAULT uuid_generate_v4(),
  building_id     uuid                NOT NULL REFERENCES buildings(id),
  guard_id        uuid                NOT NULL REFERENCES profiles(id),
  amount          numeric(19,4)      NOT NULL,
  category        text                NOT NULL,
  description     text                NOT NULL,
  receipt_url     text,
  status          petty_cash_status   NOT NULL DEFAULT 'pending',
  approved_by     uuid                REFERENCES profiles(id),
  approved_at     timestamptz,
  metadata        jsonb               NOT NULL DEFAULT '{}',
  created_at      timestamptz         NOT NULL DEFAULT now()
);

-- ============================================================
-- 索引設計（效能優化）
-- ============================================================
CREATE INDEX idx_profiles_building ON profiles(building_id);
CREATE INDEX idx_units_building ON units(building_id);
CREATE INDEX idx_units_balance ON units(building_id, balance); -- 欠費查詢
CREATE INDEX idx_invoices_unit_due ON invoices(unit_id, due_date); -- FIFO 排序
CREATE INDEX idx_invoices_status ON invoices(building_id, status);
CREATE INDEX idx_payments_unit ON payments(unit_id, paid_at);
CREATE INDEX idx_payments_guard ON payments(guard_id); -- 保全稽核查詢
CREATE INDEX idx_allocations_payment ON payment_allocations(payment_id);
CREATE INDEX idx_allocations_invoice ON payment_allocations(invoice_id);
CREATE INDEX idx_petty_cash_guard ON petty_cash(building_id, guard_id);

-- GIN 索引：模糊搜尋住戶姓名
CREATE INDEX idx_profiles_name_trgm  ON profiles USING GIN(full_name gin_trgm_ops);
CREATE INDEX idx_units_number_trgm   ON units    USING GIN(unit_number gin_trgm_ops);

-- updated_at 自動更新 Trigger
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at_buildings
  BEFORE UPDATE ON buildings
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER set_updated_at_units
  BEFORE UPDATE ON units
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER set_updated_at_invoices
  BEFORE UPDATE ON invoices
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================
-- FIFO 沖帳核心預存程序
-- 呼叫方式：SELECT * FROM process_payment_fifo(p_building_id, p_unit_id, p_amount, p_method, p_guard_id);
-- 所有操作在單一 DB 交易中完成，確保原子性 (Atomicity)
-- ============================================================

CREATE OR REPLACE FUNCTION process_payment_fifo(
  p_building_id   uuid,
  p_unit_id       uuid,
  p_amount        numeric,
  p_method        payment_method,
  p_guard_id      uuid,
  p_notes         text DEFAULT NULL,
  p_reference_no  text DEFAULT NULL
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_payment_id       uuid;
  v_remaining        numeric(19,4);
  v_invoice          RECORD;
  v_available        numeric(19,4);
  v_allocated        numeric(19,4);
  v_allocation_count integer := 0;
  v_result           jsonb;
  v_allocations      jsonb := '[]';

BEGIN
  -- ① 前置驗證：確認 unit 屬於該 building（多租戶安全檢查）
  IF NOT EXISTS (
    SELECT 1 FROM units
    WHERE id = p_unit_id AND building_id = p_building_id
  ) THEN
    RAISE EXCEPTION 'Unit does not belong to building: %', p_unit_id;
  END IF;

  -- ② 確認金額合法
  IF p_amount <= 0 THEN
    RAISE EXCEPTION 'Payment amount must be positive, got: %', p_amount;
  END IF;

  -- ③ 建立 payments 紀錄（鎖定此筆收款）
  INSERT INTO payments (
    building_id, unit_id, guard_id, amount,
    method, status, notes, reference_no
  ) VALUES (
    p_building_id, p_unit_id, p_guard_id, p_amount,
    p_method, 'completed', p_notes, p_reference_no
  )
  RETURNING id INTO v_payment_id;

  -- ④ 初始化剩餘金額
  v_remaining := p_amount;

  -- ⑤ FIFO 核心迴圈：依 due_date ASC 取得所有未結清帳單
  FOR v_invoice IN
    SELECT id, amount, paid_amount, status
    FROM   invoices
    WHERE  unit_id      = p_unit_id
      AND  building_id  = p_building_id
      AND  status      IN ('pending', 'partial', 'overdue')
    ORDER BY due_date ASC    -- ← FIFO 關鍵排序
    FOR UPDATE               -- ← 悲觀鎖定，防止並發衝突
  LOOP
    EXIT WHEN v_remaining <= 0;

    -- 計算此帳單的可抵扣金額
    v_available := v_invoice.amount - v_invoice.paid_amount;

    IF v_available <= 0 THEN
      CONTINUE;  -- 跳過已結清的帳單（防止資料不一致）
    END IF;

    -- 決定本次抵扣金額
    v_allocated := LEAST(v_remaining, v_available);

    -- 建立沖帳明細紀錄
    INSERT INTO payment_allocations (
      building_id, payment_id, invoice_id, allocated_amount
    ) VALUES (
      p_building_id, v_payment_id, v_invoice.id, v_allocated
    );

    -- 更新帳單 paid_amount 與狀態
    UPDATE invoices
    SET
      paid_amount = paid_amount + v_allocated,
      status = CASE
        WHEN (paid_amount + v_allocated) >= amount THEN 'paid'::invoice_status
        ELSE 'partial'::invoice_status
      END,
      updated_at = now()
    WHERE id = v_invoice.id;

    -- 累積沖帳結果（回傳用）
    v_allocations := v_allocations || jsonb_build_object(
      'invoice_id', v_invoice.id,
      'allocated', v_allocated
    );

    v_remaining := v_remaining - v_allocated;
    v_allocation_count := v_allocation_count + 1;
  END LOOP;

  -- ⑥ 更新 unit.balance（全量收款 + 原始餘額 - 所有已結清帳單的影響）
  -- balance 語義：正=預繳，負=欠費
  -- 由於帳單產生時 balance 已扣減，這裡只需加上收款金額
  UPDATE units
  SET
    balance     = balance + p_amount,
    updated_at  = now()
  WHERE id = p_unit_id;

  -- ⑦ 組裝回傳結果
  v_result := jsonb_build_object(
    'payment_id',         v_payment_id,
    'total_paid',         p_amount,
    'applied_to_invoices', p_amount - v_remaining,
    'prepaid_credit',     v_remaining,     -- 若 > 0 表示預繳增加
    'invoices_cleared',   v_allocation_count,
    'allocations',        v_allocations
  );

  RETURN v_result;

-- ⑧ 例外處理：任何錯誤都 ROLLBACK 整個交易
EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION 'FIFO payment failed: % %', SQLERRM, SQLSTATE;
END;
$$;

'use server'

import { createServerClient } from '@supabase/ssr'
import { Decimal }           from 'decimal.js'
import { revalidatePath }    from 'next/cache'
import { z }                 from 'zod'

// ① Zod Schema 驗證收款輸入
const PaymentSchema = z.object({
  unitId:      z.string().uuid(),
  amount:      z.string().refine(v => new Decimal(v).gt(0), '金額必須大於零'),
  method:      z.enum(['cash', 'transfer', 'check']),
  notes:       z.string().optional(),
  referenceNo: z.string().optional(),
})

export async function processPaymentAction(formData: FormData) {
  // ② 解析並驗證表單資料
  const raw = Object.fromEntries(formData.entries())
  const parsed = PaymentSchema.safeParse(raw)

  if (!parsed.success) {
    return { error: parsed.error.flatten() }
  }

  const { unitId, amount, method, notes, referenceNo } = parsed.data

  // ③ 使用 Decimal.js 確保精準度
  const preciseAmount = new Decimal(amount).toFixed(4)

  const supabase = await createServerClient(...)

  // ④ 取得目前登入保全資訊
  const { data: { user } } = await supabase.auth.getUser()
  if (!user) return { error: '未授權' }

  const { data: profile } = await supabase
    .from('profiles')
    .select('building_id, role')
    .eq('id', user.id)
    .single()

  // ⑤ 確認為 guard 或 committee_admin
  if (!profile || !['guard', 'committee_admin'].includes(profile.role)) {
    return { error: '無收款權限' }
  }

  // ⑥ 呼叫 FIFO 預存程序（在 DB 交易中執行）
  const { data: result, error } = await supabase
    .rpc('process_payment_fifo', {
      p_building_id:  profile.building_id,
      p_unit_id:      unitId,
      p_amount:       preciseAmount,
      p_method:       method,
      p_guard_id:     user.id,
      p_notes:        notes ?? null,
      p_reference_no: referenceNo ?? null,
    })

  if (error) {
    return { error: error.message }
  }

  // ⑦ 重新驗證相關頁面快取
  revalidatePath(`/guard/units/${unitId}`)
  revalidatePath(`/admin/invoices`)

  return { success: true, data: result }
}

// ============================================================
// Next.js 15 App Router 路由架構
// ============================================================

app/
├── (auth)/                         // 未登入用戶
│   ├── login/                     // 統一登入頁面（Supabase Auth）
│   └── register/                 // 大樓自助註冊
│
├── (guard)/                        // 🛡️ 保全專區（平板優先 UI）
│   ├── layout.tsx               // 保全 Layout（簡潔、大按鈕）
│   ├── dashboard/               // 保全首頁（快速操作入口）
│   ├── collect/                  // 收款流程
│   │   ├── search/               // Step 1: 搜尋住戶
│   │   ├── [unitId]/             // Step 2: 確認收款
│   │   └── success/             // Step 3: 收款成功
│   ├── petty-cash/new/          // 登記零用金支出
│   └── history/                  // 個人操作紀錄
│
├── (admin)/                        // 👑 管委員後台（桌面/平板）
│   ├── layout.tsx               // 管理員 Layout（側欄導覽）
│   ├── dashboard/               // 財務總覽儀表板
│   ├── units/                    // 住戶單位管理
│   │   ├── page.tsx             // 單位列表（含餘額）
│   │   └── [unitId]/page.tsx  // 單位詳情 + 帳單歷史
│   ├── invoices/                 // 帳單管理
│   │   ├── page.tsx             // 帳單列表
│   │   └── generate/           // 批量產生帳單
│   ├── payments/                 // 收款紀錄 + 稽核
│   ├── petty-cash/              // 零用金審核
│   ├── reports/                  // 財務報表
│   ├── staff/                    // 保全帳號管理
│   └── settings/                // 大樓設定 + 計費費率
│
└── (resident)/                     // 🏠 住戶自服務
    ├── layout.tsx
    ├── dashboard/               // 我的帳務總覽
    ├── invoices/                 // 我的帳單明細
    ├── payments/                 // 我的繳費紀錄
    └── reports/                  // 大樓公開財報

// 所有 Server Action 統一回傳格式
type ActionResult<T> = 
  | { success: true;  data: T }
  | { success: false; error: { code: string; message: string; details?: unknown } }

// 錯誤碼定義
const ErrorCodes = {
  UNAUTHORIZED:      'E001',  // 未授權
  FORBIDDEN:         'E002',  // 無此操作權限
  TENANT_MISMATCH:   'E003',  // 跨租戶資料存取
  INVALID_AMOUNT:    'E101',  // 金額格式錯誤
  DUPLICATE_INVOICE: 'E201',  // 重複產生帳單
  INVOICE_VOIDED:    'E202',  // 帳單已作廢
  PAYMENT_FAILED:    'E301',  // 沖帳失敗（DB 交易錯誤）
  UNIT_NOT_FOUND:    'E401',  // 找不到住戶單位
} as const

# Supabase
NEXT_PUBLIC_SUPABASE_URL=https://xxx.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJhbGci...
SUPABASE_SERVICE_ROLE_KEY=eyJhbGci...  # Server-only！絕不暴露前端
SUPABASE_JWT_SECRET=your-jwt-secret

# 郵件服務
RESEND_API_KEY=re_xxxx
RESEND_FROM_EMAIL=noreply@yourbuilding.app

# 應用設定
NEXT_PUBLIC_APP_URL=https://app.buildingfinance.tw
NEXT_PUBLIC_APP_NAME=大樓財務雲端管理平台