Files
dbx-main/scripts/sql/live_db_init.sql
king 61f5b3c320 feat(라이브커머스): 달력 보기와 호스트 비용 계산 추가
2시간 칸 × 날짜 달력(카드 머리 = Tiktok Handle, SALES·COM·Fee), 끌어 옮기기·
맞바꾸기, Jump to Date, 날짜별 합계·광고비·ROAS 를 넣는다.
호스트 비용 = 시간 × RM 25 + 판매금액 10%, 표 보기에도 표시한다.
끝 시간을 필수로 바꾸고 광고비 표는 마이그레이션 002 로 추가한다.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-04 16:58:07 +09:00

128 lines
7.9 KiB
PL/PgSQL

-- =====================================================================
-- live_db 초기화 스크립트 (PostgreSQL) — 라이브 커머스 모듈(2026-11-04)
-- =====================================================================
-- 말레이시아 라이브 커머스 방송 — 호스트 정보(설정)와 방송 스케줄(호스트·날짜·시간·판매금액·장소·결과 이미지).
-- 멱등(idempotent): 여러 번 실행해도 안전. 기존 데이터를 삭제하지 않는다. DROP/TRUNCATE 없음(트리거 다시 만들기만).
--
-- 실행 방법 (운영 PostgreSQL 컨테이너명: postgres-db, 백업 먼저):
--
-- 1) DB / 역할 / 테이블 생성 (superuser 로 1회)
-- read -s -p "live_app password: " APP_PWD; echo
-- docker exec -i postgres-db psql -U postgres -v app_password="$APP_PWD" < scripts/sql/live_db_init.sql
--
-- 2) main-app .env 에 연결 정보 등록
-- LIVE_DB_URL=postgresql://live_app:<APP_PWD>@postgres-db:5432/live_db
--
-- 3) main-app 재기동
-- cd /opt/www/main && docker compose up -d --build --no-deps web
-- =====================================================================
\set ON_ERROR_STOP on
SELECT 'CREATE DATABASE live_db ENCODING ''UTF8'' TEMPLATE template0'
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'live_db')
\gexec
SELECT 'CREATE ROLE live_app LOGIN PASSWORD ' || quote_literal(:'app_password')
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'live_app')
\gexec
SELECT 'ALTER ROLE live_app WITH LOGIN PASSWORD ' || quote_literal(:'app_password')
\gexec
GRANT CONNECT ON DATABASE live_db TO live_app;
\connect live_db
CREATE OR REPLACE FUNCTION live_set_updated_at() RETURNS trigger AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- ════════════════════════════════════════════════════════════
-- 1) 호스트 — 설정에서 미리 등록(개인정보·계좌 포함, 설정 화면은 ERP 관리자만)
-- ════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS live_hosts (
id BIGSERIAL PRIMARY KEY,
full_name TEXT NOT NULL CHECK (char_length(full_name) BETWEEN 1 AND 200), -- Full Name (as per IC)
phone TEXT NOT NULL DEFAULT '',
email TEXT NOT NULL DEFAULT '',
address TEXT NOT NULL DEFAULT '',
bank_name TEXT NOT NULL DEFAULT '',
bank_account TEXT NOT NULL DEFAULT '',
tiktok_handle TEXT NOT NULL DEFAULT '', -- @ 없이
is_active BOOLEAN NOT NULL DEFAULT TRUE, -- FALSE = 새 방송에서 못 고름
created_by TEXT NOT NULL DEFAULT '',
updated_by TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
COMMENT ON TABLE live_hosts IS '라이브 커머스 — 호스트 정보(개인정보·계좌 포함)';
DROP TRIGGER IF EXISTS trg_live_hosts_updated_at ON live_hosts;
CREATE TRIGGER trg_live_hosts_updated_at BEFORE UPDATE ON live_hosts
FOR EACH ROW EXECUTE FUNCTION live_set_updated_at();
-- ════════════════════════════════════════════════════════════
-- 2) 방송 스케줄 — 날짜·시간은 입력한 말레이시아 시간 그대로(시간대 변환 없음), 판매금액 단위 RM
-- ════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS live_broadcasts (
id BIGSERIAL PRIMARY KEY,
host_id BIGINT NOT NULL REFERENCES live_hosts(id) ON DELETE RESTRICT, -- 방송 기록이 있는 호스트는 못 지움
broadcast_date DATE NOT NULL,
start_time TIME NOT NULL,
end_time TIME,
venue TEXT NOT NULL DEFAULT '',
sales_amount NUMERIC(14,2) CHECK (sales_amount IS NULL OR sales_amount >= 0), -- NULL = 아직 입력 안 함
created_by TEXT NOT NULL DEFAULT '',
updated_by TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
COMMENT ON TABLE live_broadcasts IS '라이브 커머스 — 방송 스케줄';
CREATE INDEX IF NOT EXISTS idx_live_broadcasts_date ON live_broadcasts (broadcast_date, start_time);
CREATE INDEX IF NOT EXISTS idx_live_broadcasts_host ON live_broadcasts (host_id);
DROP TRIGGER IF EXISTS trg_live_broadcasts_updated_at ON live_broadcasts;
CREATE TRIGGER trg_live_broadcasts_updated_at BEFORE UPDATE ON live_broadcasts
FOR EACH ROW EXECUTE FUNCTION live_set_updated_at();
-- ════════════════════════════════════════════════════════════
-- 3) 방송 결과 이미지 — 서버가 확인·메타데이터 제거 후 저장(방송을 지우면 함께 지워짐)
-- ════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS live_broadcast_images (
id BIGSERIAL PRIMARY KEY,
broadcast_id BIGINT NOT NULL REFERENCES live_broadcasts(id) ON DELETE CASCADE,
filename TEXT NOT NULL DEFAULT '',
content_type TEXT NOT NULL,
data BYTEA NOT NULL,
width INTEGER NOT NULL DEFAULT 0,
height INTEGER NOT NULL DEFAULT 0,
created_by TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
COMMENT ON TABLE live_broadcast_images IS '라이브 커머스 — 방송 결과 이미지';
CREATE INDEX IF NOT EXISTS idx_live_images_broadcast ON live_broadcast_images (broadcast_id, id);
-- ════════════════════════════════════════════════════════════
-- 3-1) 날짜별 광고비 — 마이그레이션 002 와 같음(달력 보기 AD SPEND·ROAS)
-- ════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS live_daily_ad_spend (
ad_date DATE PRIMARY KEY,
ad_spend NUMERIC(14,2) NOT NULL CHECK (ad_spend >= 0),
updated_by TEXT NOT NULL DEFAULT '',
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
COMMENT ON TABLE live_daily_ad_spend IS '라이브 커머스 — 날짜별 광고비(RM)';
-- ════════════════════════════════════════════════════════════
-- 4) 권한 (live_app: CRUD only)
-- ════════════════════════════════════════════════════════════
GRANT USAGE ON SCHEMA public TO live_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON live_hosts, live_broadcasts, live_broadcast_images, live_daily_ad_spend TO live_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO live_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO live_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO live_app;
SELECT 'live_db ready' AS status;