-- ============================================================================
-- GUARDiA CMS — PostgreSQL 스키마 (cms_db)
-- AI 기반 통합 콘텐츠 관리 시스템 (Shopping CMS)
-- 보안 불변: PII/자격증명 *_enc AES 저장·응답 마스킹, delivery published만
-- ============================================================================

-- ── 운영자 계정 (auth/admin) — RBAC: SUPERADMIN/EDITOR/AUTHOR/VIEWER ──────────
CREATE TABLE IF NOT EXISTS cms_user (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(200) UNIQUE NOT NULL,
    password_hash VARCHAR(500) NOT NULL,
    display_name VARCHAR(200),
    role VARCHAR(50) DEFAULT 'VIEWER',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);
-- 기본 관리자 (비밀번호: admin123 / BCrypt $2a). 운영 배포 시 즉시 변경.
INSERT INTO cms_user (username, password_hash, display_name, role)
VALUES ('admin', '$2a$10$Uo4GFiJ2TZy9KlGRALfDt.gcE0e7..57rXBRaEv1mJG2XqANG3OI2', '최고관리자', 'SUPERADMIN')
ON CONFLICT (username) DO NOTHING;
INSERT INTO cms_user (username, password_hash, display_name, role)
VALUES ('editor', '$2a$10$Uo4GFiJ2TZy9KlGRALfDt.gcE0e7..57rXBRaEv1mJG2XqANG3OI2', '편집자', 'EDITOR')
ON CONFLICT (username) DO NOTHING;
INSERT INTO cms_user (username, password_hash, display_name, role)
VALUES ('author', '$2a$10$Uo4GFiJ2TZy9KlGRALfDt.gcE0e7..57rXBRaEv1mJG2XqANG3OI2', '작성자', 'AUTHOR')
ON CONFLICT (username) DO NOTHING;

CREATE TABLE IF NOT EXISTS cms_audit_log (
    id BIGSERIAL PRIMARY KEY,
    actor VARCHAR(200),
    action VARCHAR(100),
    target VARCHAR(300),
    detail TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cms_audit_action ON cms_audit_log(action);
CREATE INDEX IF NOT EXISTS idx_cms_audit_actor ON cms_audit_log(actor);

CREATE TABLE IF NOT EXISTS cms_setting (
    key VARCHAR(200) PRIMARY KEY,
    value TEXT,
    updated_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO cms_setting (key, value) VALUES
('ollama_text_model', 'llama3'),
('ollama_vision_model', 'llava'),
('default_locale', 'ko'),
('moderation_auto_reject_threshold', '0.7'),
('itsm_inquiry_to_sr', 'true')
ON CONFLICT (key) DO NOTHING;

-- ── 콘텐츠 (content) — 헤드리스 페이지/포스트/블록 + 워크플로우 ────────────────
CREATE TABLE IF NOT EXISTS cms_content (
    id BIGSERIAL PRIMARY KEY,
    content_type VARCHAR(50) DEFAULT 'PAGE',     -- PAGE, POST, BLOCK, PRODUCT_DETAIL
    slug VARCHAR(300) NOT NULL,
    title VARCHAR(500),
    summary TEXT,
    blocks JSONB,                                -- 헤드리스 블록 배열
    locale VARCHAR(20) DEFAULT 'ko',
    status VARCHAR(30) DEFAULT 'DRAFT',          -- DRAFT, REVIEW, APPROVED, PUBLISHED, ARCHIVED
    version INTEGER DEFAULT 1,
    menu_id BIGINT,
    tags VARCHAR(500),
    seo_meta JSONB,
    scheduled_at TIMESTAMP,
    published_at TIMESTAMP,
    created_by VARCHAR(200),
    updated_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cms_content_status ON cms_content(status);
CREATE INDEX IF NOT EXISTS idx_cms_content_slug ON cms_content(slug, locale);

CREATE TABLE IF NOT EXISTS cms_content_version (
    id BIGSERIAL PRIMARY KEY,
    content_id BIGINT REFERENCES cms_content(id) ON DELETE CASCADE,
    version INTEGER,
    title VARCHAR(500),
    summary TEXT,
    blocks JSONB,
    status VARCHAR(30),
    note TEXT,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cms_cversion_content ON cms_content_version(content_id);

-- ── 상품 콘텐츠 (product) — 상세빌더 + Mall 매핑 ─────────────────────────────
CREATE TABLE IF NOT EXISTS cms_product_content (
    id BIGSERIAL PRIMARY KEY,
    mall_product_id VARCHAR(200),
    product_name VARCHAR(500),
    summary TEXT,
    detail_blocks JSONB,
    specs JSONB,
    locale VARCHAR(20) DEFAULT 'ko',
    status VARCHAR(30) DEFAULT 'DRAFT',          -- DRAFT, PUBLISHED
    created_by VARCHAR(200),
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cms_product_mall ON cms_product_content(mall_product_id);

-- ── 배너/프로모션 (banner) ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cms_banner (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(300),
    banner_type VARCHAR(50) DEFAULT 'PROMOTION', -- HERO, PROMOTION, POPUP, CAMPAIGN, EXHIBITION
    image_url VARCHAR(1000),
    link_url VARCHAR(1000),
    position VARCHAR(100),
    target_rule JSONB,
    sort_order INTEGER,
    is_active BOOLEAN DEFAULT TRUE,
    start_at TIMESTAMP,
    end_at TIMESTAMP,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);

-- ── 미디어 (media) ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cms_media (
    id BIGSERIAL PRIMARY KEY,
    file_name VARCHAR(500),
    media_type VARCHAR(30) DEFAULT 'IMAGE',      -- IMAGE, VIDEO, FILE
    url VARCHAR(1000),
    folder VARCHAR(200) DEFAULT 'default',
    mime_type VARCHAR(100),
    size_bytes BIGINT,
    alt_text VARCHAR(1000),
    tags VARCHAR(500),
    ref_count INTEGER DEFAULT 0,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cms_media_folder ON cms_media(folder);

-- ── 메뉴/카테고리 (menu) — 트리 ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cms_menu (
    id BIGSERIAL PRIMARY KEY,
    parent_id BIGINT,
    name VARCHAR(300),
    menu_type VARCHAR(30) DEFAULT 'MENU',        -- MENU, CATEGORY
    url VARCHAR(1000),
    slug VARCHAR(300),
    sort_order INTEGER,
    depth INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ── 테마 (theme) ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cms_theme (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(300),
    layout JSONB,
    tokens JSONB,
    is_active BOOLEAN DEFAULT FALSE,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);

-- ── 다국어 (i18n) ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cms_i18n (
    id BIGSERIAL PRIMARY KEY,
    locale VARCHAR(20),
    resource_key VARCHAR(300),
    value TEXT,
    updated_at TIMESTAMP DEFAULT NOW(),
    UNIQUE (locale, resource_key)
);

-- ── SEO (seo) ───────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cms_seo_meta (
    id BIGSERIAL PRIMARY KEY,
    path_key VARCHAR(500),
    title VARCHAR(500),
    description TEXT,
    keywords VARCHAR(1000),
    og_title VARCHAR(500),
    og_image VARCHAR(1000),
    canonical VARCHAR(1000),
    locale VARCHAR(20) DEFAULT 'ko',
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS cms_redirect (
    id BIGSERIAL PRIMARY KEY,
    from_path VARCHAR(1000),
    to_path VARCHAR(1000),
    status_code INTEGER DEFAULT 301,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ── 폼빌더 (form) ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cms_form (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(300),
    slug VARCHAR(300),
    fields JSONB,
    is_active BOOLEAN DEFAULT TRUE,
    created_by VARCHAR(200),
    created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS cms_form_submission (
    id BIGSERIAL PRIMARY KEY,
    form_id BIGINT REFERENCES cms_form(id) ON DELETE CASCADE,
    payload JSONB,
    is_spam BOOLEAN DEFAULT FALSE,
    spam_risk DOUBLE PRECISION,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ── 채널 (channel) ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cms_channel (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(300),
    channel_type VARCHAR(30) DEFAULT 'WEB',      -- WEB, MOBILE, APP, KIOSK
    endpoint VARCHAR(1000),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ── UGC (ugc) — 게시판/리뷰/댓글/문의 + 모더레이션 ──────────────────────────
CREATE TABLE IF NOT EXISTS cms_ugc (
    id BIGSERIAL PRIMARY KEY,
    ugc_type VARCHAR(30) DEFAULT 'COMMENT',      -- BOARD, REVIEW, COMMENT, INQUIRY
    target_content_id BIGINT,
    target_ref VARCHAR(300),
    parent_id BIGINT,
    author_name VARCHAR(200),
    author_email_enc TEXT,                       -- PII AES 암호화 (응답 마스킹)
    body TEXT,
    rating INTEGER,
    moderation_status VARCHAR(30) DEFAULT 'PENDING', -- PENDING, APPROVED, REJECTED
    ai_risk DOUBLE PRECISION,
    ai_reason VARCHAR(500),
    sentiment VARCHAR(20),
    itsm_sr_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW(),
    moderated_at TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_cms_ugc_moderation ON cms_ugc(moderation_status);
CREATE INDEX IF NOT EXISTS idx_cms_ugc_type ON cms_ugc(ugc_type);

-- ── 회원 (member) — PII 암호화 ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cms_member (
    id BIGSERIAL PRIMARY KEY,
    member_code VARCHAR(100),
    name VARCHAR(200),
    email_enc TEXT,                              -- AES 암호화 (응답 마스킹)
    phone_enc TEXT,                              -- AES 암호화 (응답 마스킹)
    segment VARCHAR(50) DEFAULT 'NEW',
    status VARCHAR(30) DEFAULT 'ACTIVE',
    crm_customer_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

-- ── 콘텐츠 성과 (analytics) ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cms_content_stat (
    content_id BIGINT PRIMARY KEY,
    views BIGINT DEFAULT 0,
    dwell_seconds BIGINT DEFAULT 0,
    conversions BIGINT DEFAULT 0,
    updated_at TIMESTAMP DEFAULT NOW()
);

-- ============================================================================
-- 데모 시드 콘텐츠
-- ============================================================================
INSERT INTO cms_content (content_type, slug, title, summary, blocks, locale, status, version, published_at, created_by, updated_by)
SELECT 'PAGE', 'welcome', 'GUARDiA CMS에 오신 것을 환영합니다',
  'AI 기반 통합 콘텐츠 관리 시스템 데모 페이지',
  '[{"type":"hero","title":"Shopping CMS","text":"헤드리스 + AI"},{"type":"text","value":"콘텐츠를 작성하고 게시 워크플로우로 발행하세요."}]'::jsonb,
  'ko', 'PUBLISHED', 1, NOW(), 'system', 'system'
WHERE NOT EXISTS (SELECT 1 FROM cms_content WHERE slug = 'welcome' AND locale = 'ko');

INSERT INTO cms_content (content_type, slug, title, summary, blocks, locale, status, version, created_by, updated_by)
SELECT 'POST', 'spring-promotion-draft', '봄맞이 기획전 (작성중)',
  '게시 워크플로우 데모용 초안',
  '[{"type":"text","value":"봄맞이 신상품을 소개합니다."}]'::jsonb,
  'ko', 'DRAFT', 1, 'author', 'author'
WHERE NOT EXISTS (SELECT 1 FROM cms_content WHERE slug = 'spring-promotion-draft' AND locale = 'ko');

INSERT INTO cms_menu (parent_id, name, menu_type, slug, sort_order, depth, is_active)
SELECT NULL, '전체 카테고리', 'CATEGORY', 'all', 0, 0, TRUE
WHERE NOT EXISTS (SELECT 1 FROM cms_menu WHERE slug = 'all');

INSERT INTO cms_banner (name, banner_type, image_url, link_url, position, sort_order, is_active)
SELECT '메인 히어로 배너', 'HERO', '/assets/demo-hero.jpg', '/welcome', 'MAIN_TOP', 0, TRUE
WHERE NOT EXISTS (SELECT 1 FROM cms_banner WHERE name = '메인 히어로 배너');

INSERT INTO cms_channel (name, channel_type, endpoint, is_active)
SELECT '웹', 'WEB', 'https://cms.zioinfo.co.kr', TRUE
WHERE NOT EXISTS (SELECT 1 FROM cms_channel WHERE name = '웹');

INSERT INTO cms_i18n (locale, resource_key, value) VALUES
('ko', 'common.welcome', '환영합니다'),
('ko', 'common.search', '검색'),
('en', 'common.welcome', 'Welcome'),
('en', 'common.search', 'Search')
ON CONFLICT (locale, resource_key) DO NOTHING;

INSERT INTO cms_theme (name, layout, tokens, is_active, created_by)
SELECT '기본 테마', '{"header":true,"footer":true}'::jsonb,
  '{"primary":"#1e3a8a","font":"Pretendard"}'::jsonb, TRUE, 'system'
WHERE NOT EXISTS (SELECT 1 FROM cms_theme WHERE name = '기본 테마');
