요금제 시스템 구현 설계 (DB·API·서비스·프론트엔드·마이그레이션)

재직인원 ≤ 5명      → 0원
5 < 재직인원 ≤ 10명  → 정액 20,000원 (PLAN_FREE는 0원)
재직인원 > 10명      → 정액 + (재직인원 - 10) × 1인당 단가

CREATE TABLE billing_plans (
    id              SERIAL PRIMARY KEY,
    plan_code       VARCHAR(30) UNIQUE NOT NULL,
    name_ko         VARCHAR(100) NOT NULL,
    name_en         VARCHAR(100),
    price_per_user  INTEGER NOT NULL DEFAULT 0,
    base_free_seats INTEGER NOT NULL DEFAULT 5,
    flat_fee_seats  INTEGER NOT NULL DEFAULT 10,
    flat_fee_amount INTEGER NOT NULL DEFAULT 20000,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    description     TEXT,
    created_by      UUID,
    updated_by      UUID,
    created_at      TIMESTAMP DEFAULT NOW(),
    updated_at      TIMESTAMP DEFAULT NOW()
);

CREATE TABLE billing_snapshots (
    id                    UUID PRIMARY KEY,
    tenant_id             UUID NOT NULL REFERENCES tenants(id),
    billing_month         VARCHAR(7) NOT NULL,  -- "2026-06"
    plan_code             VARCHAR(30) NOT NULL,
    active_employee_count INTEGER NOT NULL,
    price_per_user        INTEGER NOT NULL,
    base_amount           INTEGER NOT NULL DEFAULT 0,
    extra_amount          INTEGER NOT NULL DEFAULT 0,
    total_amount          INTEGER NOT NULL,
    memo                  TEXT,
    created_by            UUID,
    created_at            TIMESTAMP DEFAULT NOW(),
    updated_at            TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_id, billing_month)
);

CREATE TABLE tenant_billing_overrides (
    id                    UUID PRIMARY KEY,
    tenant_id             UUID UNIQUE NOT NULL REFERENCES tenants(id),
    custom_price_per_user INTEGER,
    custom_flat_fee       INTEGER,
    discount_reason       VARCHAR(500),
    valid_from            TIMESTAMP,
    valid_until           TIMESTAMP,
    created_by            UUID,
    updated_by            UUID,
    created_at            TIMESTAMP DEFAULT NOW(),
    updated_at            TIMESTAMP DEFAULT NOW()
);

GET    /v2/system/billing-plans
PUT    /v2/system/billing-plans/{plan_code}
GET    /v2/system/usage
POST   /v2/system/billing-snapshots/generate
GET    /v2/system/billing-snapshots?month=2026-06
PATCH  /v2/system/tenants/{id}/trial

GET    /v2/admin/billing/current
GET    /v2/admin/billing/history

# 1. 순수 계산 함수 (단위 테스트 대상)
def calc_monthly_bill(active_count, price_per_user,
                      base_free_seats=5, flat_fee_seats=10,
                      flat_fee_amount=20000) -> dict: ...

# 2. DB 의존 함수
async def count_active_employees(db, tenant_id) -> int
async def get_billing_plan(db, plan_code) -> BillingPlan | None
async def get_all_billing_plans(db) -> list[BillingPlan]
async def update_billing_plan(db, plan_code, data, admin_id) -> BillingPlan

# 3. 사용량 조회
async def get_all_tenants_usage(db) -> list[dict]
async def get_tenant_current_billing(db, tenant_id) -> dict

# 4. 스냅샷
async def generate_monthly_snapshots(db, billing_month, admin_id) -> dict
async def get_billing_snapshots(db, tenant_id=None, month=None) -> list[dict]

# 5. 체험판 관리
async def update_tenant_trial(db, tenant_id, action, days, admin_id) -> Tenant

# {menu_url: set(plan_codes)} — 앱 시작 시 메모리 로드
_MENU_PLAN_CACHE: dict[str, set[str]] = {}