database/migrations/002_organizations_users.sql

-- ============================================================================
-- Carboany Harness System — 조직 및 사용자 스키마
-- Migration: 002_organizations_users
--
-- 설계 원칙:
--   1. UUID PK: 분산 시스템 확장성 확보
--   2. 역할 기반 접근 제어: role 컬럼으로 Admin/Sponsor/Participant/SME 구분
--   3. OAuth 소셜 로그인: 별도 테이블로 다중 프로바이더 지원
--   4. API 키 해시 저장: 원본 키는 발급 시 1회만 노출
-- ============================================================================

-- ────────────────────────────────────────────────────────────────────────────
-- 1. 조직 (Organizations)
--    지자체, 공공기관, 대기업(스폰서), 중소기업(SME) 통합 관리
-- ────────────────────────────────────────────────────────────────────────────

CREATE TABLE organizations (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        VARCHAR(200) NOT NULL,
    type        VARCHAR(20) NOT NULL
                    CHECK (type IN ('municipality', 'public_corp', 'government', 'enterprise', 'sme')),
    parent_id   UUID REFERENCES organizations(id),  -- SME → 스폰서 계층 관계 (nullable)
    created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE INDEX idx_org_type ON organizations(type);
CREATE INDEX idx_org_parent ON organizations(parent_id) WHERE parent_id IS NOT NULL;

-- ────────────────────────────────────────────────────────────────────────────
-- 2. 사용자 (Users)
--    4개 도메인 역할: admin, sponsor_mgr, participant, sme_mgr
-- ────────────────────────────────────────────────────────────────────────────

CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email           VARCHAR(255) UNIQUE NOT NULL,
    password_hash   VARCHAR(255),                 -- OAuth 전용 사용자는 NULL 가능
    name            VARCHAR(100) NOT NULL,
    role            VARCHAR(20) NOT NULL
                        CHECK (role IN ('admin', 'sponsor_mgr', 'participant', 'sme_mgr')),
    org_id          UUID REFERENCES organizations(id),
    phone           VARCHAR(20),                  -- 본인 인증용 (모바일)
    avatar_url      VARCHAR(500),
    created_at      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_org ON users(org_id) WHERE org_id IS NOT NULL;
CREATE INDEX idx_users_role ON users(role);

-- ────────────────────────────────────────────────────────────────────────────
-- 3. OAuth 소셜 로그인 연결 (OAuth Connections)
--    Google, 카카오, 네이버, 정부 SSO 다중 프로바이더 지원
-- ────────────────────────────────────────────────────────────────────────────

CREATE TABLE oauth_connections (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    provider    VARCHAR(30) NOT NULL
                    CHECK (provider IN ('google', 'kakao', 'naver', 'gov_sso')),
    provider_id VARCHAR(255) NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,

    UNIQUE (provider, provider_id)
);

CREATE INDEX idx_oauth_user ON oauth_connections(user_id);

-- ────────────────────────────────────────────────────────────────────────────
-- 4. API 키 (API Keys)
--    외부 시스템 연동용, 해시 저장, 레이트 리밋 관리
-- ────────────────────────────────────────────────────────────────────────────

CREATE TABLE api_keys (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id      UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    key_hash    VARCHAR(255) NOT NULL,            -- SHA-256 해시 (원본 미저장)
    prefix      VARCHAR(20) NOT NULL,             -- 식별용 접두사 (예: "cbny_live_")
    label       VARCHAR(100),                     -- 용도 라벨 (예: "세종시 연동키")
    rate_limit  INT DEFAULT 10000,                -- 일일 호출 한도
    expires_at  TIMESTAMPTZ,                      -- NULL이면 무기한
    is_active   BOOLEAN DEFAULT true NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE INDEX idx_apikeys_org ON api_keys(org_id);
CREATE INDEX idx_apikeys_prefix ON api_keys(prefix);