-- ============================================================================
-- GUARDiA SIGNAGE — 전자간판(e-SignBoard / ESB) 중앙관제 플랫폼 PostgreSQL 스키마
-- (signage_db) · 파생 베이스: guardia-esn. 사업계획서(E-SIGNBOARD) 7대 기능 반영.
-- 지역: 서울 / 대전 / 대구 / 부산 · 매장코드(store_code) 중심 중앙관리.
-- 모든 CREATE/INSERT 는 멱등(IF NOT EXISTS / ON CONFLICT DO NOTHING).
-- ============================================================================

-- ── 운영사(테넌트) ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_tenant (
    id          BIGSERIAL PRIMARY KEY,
    tenant_code VARCHAR(20)  UNIQUE NOT NULL,
    tenant_name VARCHAR(200) NOT NULL,
    description TEXT,
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT NOW()
);

INSERT INTO signage_tenant (tenant_code, tenant_name, description) VALUES
  ('ZIOINFO',  '지오정보기술',   '전자간판 중앙관제 운영(기본)'),
  ('CULINAN',  '컬리넌홀딩스',   'e-SignBoard 사업 운영사'),
  ('GOV',      '지자체 간판개선', '정부/지자체 간판 표준화 시범사업')
ON CONFLICT (tenant_code) DO NOTHING;

-- ── 지역 마스터(서울/대전/대구/부산) ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_region (
    id          BIGSERIAL PRIMARY KEY,
    tenant_code VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    region_code VARCHAR(30)  NOT NULL,
    region_name VARCHAR(100) NOT NULL,
    dc_name     VARCHAR(200),        -- 관할 데이터센터(H.Q.) 명
    description TEXT,
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_code, region_code)
);

INSERT INTO signage_region (tenant_code, region_code, region_name, dc_name) VALUES
  ('ZIOINFO', 'SEOUL',   '서울', '서울 중앙 데이터센터'),
  ('ZIOINFO', 'DAEJEON', '대전', '대전 지역센터'),
  ('ZIOINFO', 'DAEGU',   '대구', '대구 지역센터'),
  ('ZIOINFO', 'BUSAN',   '부산', '부산 지역센터')
ON CONFLICT (tenant_code, region_code) DO NOTHING;

-- ── 매장 그룹(지역 단위 그룹) ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_store_group (
    id          BIGSERIAL PRIMARY KEY,
    tenant_code VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    group_code  VARCHAR(50)  NOT NULL,
    group_name  VARCHAR(200) NOT NULL,
    description TEXT,
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_code, group_code)
);

INSERT INTO signage_store_group (tenant_code, group_code, group_name) VALUES
  ('ZIOINFO', 'GRP-SEOUL',   '서울권 매장'),
  ('ZIOINFO', 'GRP-DAEJEON', '대전권 매장'),
  ('ZIOINFO', 'GRP-DAEGU',   '대구권 매장'),
  ('ZIOINFO', 'GRP-BUSAN',   '부산권 매장')
ON CONFLICT (tenant_code, group_code) DO NOTHING;

-- ── 매장(store_code = 매장코드, 중앙관리 단위) ────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_store (
    id             BIGSERIAL PRIMARY KEY,
    tenant_code    VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    store_code     VARCHAR(50)  NOT NULL,   -- 매장코드 (예: 1000, 2000, 4001)
    store_name     VARCHAR(200) NOT NULL,
    address        VARCHAR(500),
    phone          VARCHAR(50),
    store_group_id BIGINT REFERENCES signage_store_group(id),
    manager_name   VARCHAR(100),
    is_active      BOOLEAN DEFAULT TRUE,
    created_at     TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_code, store_code)
);

INSERT INTO signage_store (tenant_code, store_code, store_name, address, manager_name, store_group_id)
SELECT 'ZIOINFO', '1000', '서울 강남 1호점', '서울 강남구 테헤란로 1', '김서울', g.id
FROM signage_store_group g WHERE g.group_code = 'GRP-SEOUL' AND g.tenant_code = 'ZIOINFO'
ON CONFLICT (tenant_code, store_code) DO NOTHING;

INSERT INTO signage_store (tenant_code, store_code, store_name, address, manager_name, store_group_id)
SELECT 'ZIOINFO', '2000', '대전 둔산 2호점', '대전 서구 둔산로 2', '박대전', g.id
FROM signage_store_group g WHERE g.group_code = 'GRP-DAEJEON' AND g.tenant_code = 'ZIOINFO'
ON CONFLICT (tenant_code, store_code) DO NOTHING;

INSERT INTO signage_store (tenant_code, store_code, store_name, address, manager_name, store_group_id)
SELECT 'ZIOINFO', '4001', '부산 서면 1호점', '부산 부산진구 중앙대로 4001', '이부산', g.id
FROM signage_store_group g WHERE g.group_code = 'GRP-BUSAN' AND g.tenant_code = 'ZIOINFO'
ON CONFLICT (tenant_code, store_code) DO NOTHING;

-- ── 템플릿 그룹(Template/Font/Image Code 묶음) ───────────────────────────────
CREATE TABLE IF NOT EXISTS signage_template_group (
    id          BIGSERIAL PRIMARY KEY,
    tenant_code VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    group_code  VARCHAR(50)  NOT NULL,
    group_name  VARCHAR(200) NOT NULL,
    description TEXT,
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_code, group_code)
);

INSERT INTO signage_template_group (tenant_code, group_code, group_name, description) VALUES
  ('ZIOINFO', 'TG-STD',   '표준 간판 템플릿군', '상호/전화/문구 표준 레이아웃'),
  ('ZIOINFO', 'TG-PROMO', '광고 템플릿군',      '주기 광고 편성용 템플릿')
ON CONFLICT (tenant_code, group_code) DO NOTHING;

-- ── 간판 템플릿(Template/Font/Image Code) ────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_template (
    id                BIGSERIAL PRIMARY KEY,
    tenant_code       VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    template_code     VARCHAR(50)  NOT NULL,
    template_name     VARCHAR(200) NOT NULL,
    template_type     VARCHAR(50)  DEFAULT 'SIGN',   -- SIGN, FONT, IMAGE, PROMO, CUSTOM
    template_group_id BIGINT REFERENCES signage_template_group(id),
    font_code         VARCHAR(50),                   -- Font Code
    image_code        VARCHAR(50),                   -- Image Code
    width             INTEGER,
    height            INTEGER,
    description       TEXT,
    layout_json       TEXT,
    is_active         BOOLEAN DEFAULT TRUE,
    created_at        TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_code, template_code)
);

INSERT INTO signage_template (tenant_code, template_code, template_name, template_type, font_code, image_code, width, height, description) VALUES
  ('ZIOINFO', 'SIGN-STD',  '표준 간판(상호/전화/문구)', 'SIGN',  'FONT-NANUM', 'IMG-FRAME01', 1920, 480, '가로형 표준 전자간판'),
  ('ZIOINFO', 'SIGN-PROMO','광고 편성 간판',           'PROMO', 'FONT-NANUM', 'IMG-PROMO01', 1920, 480, '주기 광고 표시'),
  ('ZIOINFO', 'FONT-NANUM','나눔고딕 폰트',            'FONT',  'FONT-NANUM', NULL,          NULL, NULL, '기본 폰트 코드')
ON CONFLICT (tenant_code, template_code) DO NOTHING;

-- ── 간판정보 변환(중앙배포 소스 데이터) ──────────────────────────────────────
-- 상호/전화/문구 등 간판정보 원본 → 중앙배포(zip/md5/REST) 대상 레코드.
CREATE TABLE IF NOT EXISTS signage_pos_cvt (
    id            BIGSERIAL PRIMARY KEY,
    tenant_code   VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    store_id      BIGINT REFERENCES signage_store(id),
    pos_code      VARCHAR(100),
    product_code  VARCHAR(100) NOT NULL,   -- 간판 콘텐츠 코드
    product_name  VARCHAR(300),            -- 상호/콘텐츠 명
    price         NUMERIC(12,2),           -- (호환 필드) 부가 값
    sale_price    NUMERIC(12,2),
    currency      VARCHAR(10)  DEFAULT 'KRW',
    status        VARCHAR(20)  DEFAULT 'PENDING',  -- PENDING, PROCESSED, ERROR, IGNORED
    error_message TEXT,
    processed_at  TIMESTAMP,
    created_at    TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_signage_cvt_tenant ON signage_pos_cvt(tenant_code, status);
CREATE INDEX IF NOT EXISTS idx_signage_cvt_store  ON signage_pos_cvt(store_id, status);

-- ── 알람 / 모니터링(디바이스 상태·장애) ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_alarm (
    id          BIGSERIAL PRIMARY KEY,
    tenant_code VARCHAR(20) NOT NULL REFERENCES signage_tenant(tenant_code),
    store_id    BIGINT REFERENCES signage_store(id),
    alarm_type  VARCHAR(50) NOT NULL,   -- DEVICE, NETWORK, DISPLAY, FIRMWARE, DEPLOY, SYSTEM
    alarm_code  VARCHAR(100),
    severity    VARCHAR(20) NOT NULL,   -- CRITICAL, HIGH, MEDIUM, LOW
    message     TEXT,
    status      VARCHAR(20) DEFAULT 'OPEN',  -- OPEN, ACKNOWLEDGED, RESOLVED
    resolved_by VARCHAR(100),
    resolution  TEXT,
    resolved_at TIMESTAMP,
    created_at  TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_signage_alarm_tenant   ON signage_alarm(tenant_code, status);
CREATE INDEX IF NOT EXISTS idx_signage_alarm_severity ON signage_alarm(severity, status);

INSERT INTO signage_alarm (tenant_code, store_id, alarm_type, alarm_code, severity, message, status)
SELECT 'ZIOINFO', s.id, 'DEVICE', 'ESB-OFFLINE', 'HIGH', '전자간판(ESB) 오프라인 감지 — 현장 점검 필요', 'OPEN'
FROM signage_store s WHERE s.store_code = '1000' LIMIT 1;

INSERT INTO signage_alarm (tenant_code, store_id, alarm_type, alarm_code, severity, message, status)
SELECT 'ZIOINFO', s.id, 'DISPLAY', 'IMG-STALE', 'MEDIUM', '간판 이미지 갱신 지연(30초 폴링 미확인)', 'OPEN'
FROM signage_store s WHERE s.store_code = '2000' LIMIT 1;

-- ── 게이트웨이(Gen1/Gen2/Gen2+ EMS)·허브·ESB 태그 디바이스 ───────────────────
CREATE TABLE IF NOT EXISTS signage_gateway (
    id               BIGSERIAL PRIMARY KEY,
    tenant_code      VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    store_id         BIGINT REFERENCES signage_store(id),
    device_type      VARCHAR(50)  NOT NULL,   -- GATEWAY, HUB, ESB_TAG
    gw_type          VARCHAR(30),             -- GEN1, GEN2, GEN2_PLUS_EMS
    device_id        VARCHAR(100) NOT NULL,
    ip_address       VARCHAR(50),
    mac_address      VARCHAR(50),
    firmware_version VARCHAR(50),
    status           VARCHAR(20)  DEFAULT 'ONLINE', -- ONLINE, OFFLINE, ERROR, UPDATING
    battery_level    INTEGER,
    signal_strength  VARCHAR(20),
    last_seen_at     TIMESTAMP DEFAULT NOW(),
    created_at       TIMESTAMP DEFAULT NOW(),
    UNIQUE (device_id)
);
CREATE INDEX IF NOT EXISTS idx_signage_gw_tenant ON signage_gateway(tenant_code, status);
CREATE INDEX IF NOT EXISTS idx_signage_gw_store  ON signage_gateway(store_id, status);

INSERT INTO signage_gateway (tenant_code, store_id, device_type, gw_type, device_id, ip_address, firmware_version, status)
SELECT 'ZIOINFO', s.id, 'GATEWAY', 'GEN2_PLUS_EMS', 'GW-1000-001', '192.168.10.1', 'GWFW-2.1.0', 'ONLINE'
FROM signage_store s WHERE s.store_code = '1000' LIMIT 1
ON CONFLICT (device_id) DO NOTHING;

INSERT INTO signage_gateway (tenant_code, store_id, device_type, gw_type, device_id, firmware_version, status, battery_level)
SELECT 'ZIOINFO', s.id, 'ESB_TAG', 'GEN2', 'ESB-1000-0001', 'ESBFW-1.4.2', 'ONLINE', 92
FROM signage_store s WHERE s.store_code = '1000' LIMIT 1
ON CONFLICT (device_id) DO NOTHING;

INSERT INTO signage_gateway (tenant_code, store_id, device_type, gw_type, device_id, firmware_version, status, battery_level)
SELECT 'ZIOINFO', s.id, 'ESB_TAG', 'GEN1', 'ESB-2000-0001', 'ESBFW-1.3.9', 'OFFLINE', 11
FROM signage_store s WHERE s.store_code = '2000' LIMIT 1
ON CONFLICT (device_id) DO NOTHING;

-- ── 작업 이력(설치/유지보수/펌웨어/점검) ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_work_history (
    id           BIGSERIAL PRIMARY KEY,
    tenant_code  VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    store_id     BIGINT REFERENCES signage_store(id),
    work_type    VARCHAR(50)  NOT NULL,  -- INSTALL, MAINTENANCE, FIRMWARE_UPDATE, REPLACEMENT, INSPECTION
    work_content TEXT,
    worker_name  VARCHAR(100),
    status       VARCHAR(20)  DEFAULT 'SCHEDULED',  -- SCHEDULED, IN_PROGRESS, COMPLETED, CANCELLED
    remarks      TEXT,
    started_at   TIMESTAMP,
    completed_at TIMESTAMP,
    created_at   TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_signage_work_tenant ON signage_work_history(tenant_code, status);

-- ── 펌웨어(eESB / GW / Tag FW 중앙배포) ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_firmware (
    id               BIGSERIAL PRIMARY KEY,
    tenant_code      VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    firmware_version VARCHAR(50)  NOT NULL,
    device_type      VARCHAR(50)  NOT NULL,   -- EESB, GW, TAG
    file_name        VARCHAR(300),
    file_path        VARCHAR(500),
    file_size        BIGINT,
    checksum         VARCHAR(200),
    description      TEXT,
    is_latest        BOOLEAN DEFAULT FALSE,
    created_at       TIMESTAMP DEFAULT NOW()
);

INSERT INTO signage_firmware (tenant_code, firmware_version, device_type, file_name, description, is_latest) VALUES
  ('ZIOINFO', 'GWFW-2.1.0',  'GW',   'gw_fw_2.1.0.bin',   'GW 게이트웨이 최신 펌웨어', true),
  ('ZIOINFO', 'ESBFW-1.4.2', 'EESB', 'eesb_fw_1.4.2.bin', 'eESB 본체 최신 펌웨어',     true),
  ('ZIOINFO', 'TAGFW-1.4.2', 'TAG',  'tag_fw_1.4.2.bin',  'Tag 최신 펌웨어',          true)
ON CONFLICT DO NOTHING;

-- ── 사용자(매장코드별 계정 중앙관리) ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_user (
    id            BIGSERIAL PRIMARY KEY,
    tenant_code   VARCHAR(20)  REFERENCES signage_tenant(tenant_code),
    store_code    VARCHAR(50),                   -- 소속 매장코드(NULL=본사/전역)
    username      VARCHAR(200) UNIQUE NOT NULL,
    password_hash VARCHAR(500) NOT NULL,
    role          VARCHAR(50)  DEFAULT 'USER',   -- ADMIN, MANAGER, USER
    email         VARCHAR(300),
    phone         VARCHAR(50),
    is_active     BOOLEAN DEFAULT TRUE,
    last_login_at TIMESTAMP,
    created_at    TIMESTAMP DEFAULT NOW()
);

-- 기본 관리자 (비밀번호: admin123 / BCrypt $2a$12$)
INSERT INTO signage_user (tenant_code, store_code, username, password_hash, role)
VALUES (NULL, NULL, 'admin', '$2a$12$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LnCWh7Vz.Xm', 'ADMIN')
ON CONFLICT (username) DO NOTHING;

INSERT INTO signage_user (tenant_code, store_code, username, password_hash, role)
VALUES ('ZIOINFO', '1000', 'seoul_manager', '$2a$12$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LnCWh7Vz.Xm', 'MANAGER')
ON CONFLICT (username) DO NOTHING;

-- ── 간판 콘텐츠(상품/가격 호환 도메인) ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_product (
    id           BIGSERIAL PRIMARY KEY,
    tenant_code  VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    store_id     BIGINT REFERENCES signage_store(id),
    product_code VARCHAR(100) NOT NULL,   -- 콘텐츠 코드
    product_name VARCHAR(300) NOT NULL,   -- 콘텐츠 명(상호 등)
    category     VARCHAR(100),
    price        NUMERIC(12,2),
    sale_price   NUMERIC(12,2),
    currency     VARCHAR(10)  DEFAULT 'KRW',
    is_active    BOOLEAN DEFAULT TRUE,
    updated_at   TIMESTAMP DEFAULT NOW(),
    created_at   TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_signage_content_store ON signage_product(store_id, product_code);

INSERT INTO signage_product (tenant_code, store_id, product_code, product_name, category)
SELECT 'ZIOINFO', s.id, 'C-1000-01', '강남 1호점 상호', '간판콘텐츠'
FROM signage_store s WHERE s.store_code = '1000' LIMIT 1
ON CONFLICT DO NOTHING;

-- ── 간판 이미지(상호/전화/문구 편집 산출물) ──────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_sign_image (
    id            BIGSERIAL PRIMARY KEY,
    tenant_code   VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    store_id      BIGINT REFERENCES signage_store(id),
    store_code    VARCHAR(50),
    image_code    VARCHAR(100) NOT NULL,
    biz_name      VARCHAR(200),            -- 상호
    phone         VARCHAR(50),             -- 전화번호
    message       TEXT,                    -- 문구
    template_code VARCHAR(50),
    width         INTEGER,
    height        INTEGER,
    image_path    VARCHAR(500),
    status        VARCHAR(20) DEFAULT 'DRAFT',  -- DRAFT, READY, DEPLOYED
    created_at    TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_code, image_code)
);
CREATE INDEX IF NOT EXISTS idx_signage_image_store ON signage_sign_image(store_id, status);

INSERT INTO signage_sign_image (tenant_code, store_id, store_code, image_code, biz_name, phone, message, template_code, status)
SELECT 'ZIOINFO', s.id, '1000', 'IMG-1000-01', '지오 카페 강남점', '02-123-4567', '신선한 원두 · 오늘의 커피', 'SIGN-STD', 'READY'
FROM signage_store s WHERE s.store_code = '1000' LIMIT 1
ON CONFLICT (tenant_code, image_code) DO NOTHING;

-- ── 전자간판(ESB) 태그 바인딩 — 디바이스↔콘텐츠↔현재표시 ─────────────────────
CREATE TABLE IF NOT EXISTS signage_signboard (
    id            BIGSERIAL PRIMARY KEY,
    tenant_code   VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    store_id      BIGINT       NOT NULL REFERENCES signage_store(id),
    device_id     VARCHAR(100) NOT NULL,              -- ESB_TAG device_id
    product_code  VARCHAR(100),                       -- 현재 표시 콘텐츠
    location_code VARCHAR(100),                       -- 설치 위치코드 (예: 외벽-정면)
    template_code VARCHAR(50),
    display_data  TEXT,                               -- 현재 화면 데이터(JSON)
    last_price    NUMERIC(12,2),
    last_updated_at TIMESTAMP,
    bind_status   VARCHAR(20) DEFAULT 'ACTIVE',       -- ACTIVE, UNBOUND, ERROR
    created_at    TIMESTAMP DEFAULT NOW(),
    UNIQUE (device_id)
);
CREATE INDEX IF NOT EXISTS idx_signage_board_store   ON signage_signboard(store_id, bind_status);
CREATE INDEX IF NOT EXISTS idx_signage_board_content ON signage_signboard(tenant_code, product_code);
CREATE INDEX IF NOT EXISTS idx_signage_board_loc     ON signage_signboard(store_id, location_code);

INSERT INTO signage_signboard (tenant_code, store_id, device_id, product_code, location_code, template_code)
SELECT 'ZIOINFO', s.id, 'ESB-1000-0001', 'C-1000-01', '외벽-정면', 'SIGN-STD'
FROM signage_store s WHERE s.store_code = '1000' LIMIT 1
ON CONFLICT (device_id) DO NOTHING;

-- ── 간판정보 중앙배포(CSV→zip+md5→REST, 30초 폴링) ──────────────────────────
-- 사업계획서 슬라이드20 계약: {header:{token}, body:{type,sub_type,code,path,md5,file_name,file_size}}
CREATE TABLE IF NOT EXISTS signage_sign_deploy (
    id               BIGSERIAL PRIMARY KEY,
    tenant_code      VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    store_code       VARCHAR(50)  NOT NULL,       -- 매장코드로 구분(예: 1000, 2000, 4001)
    deploy_type      VARCHAR(30)  NOT NULL DEFAULT 'SIGN_FILE', -- SIGN_FILE, POS_FILE, TEMPLATE, SW
    sub_type         VARCHAR(50),
    code             VARCHAR(50),
    file_name        VARCHAR(300),
    file_size        BIGINT,
    md5              VARCHAR(64),
    file_path        VARCHAR(500),
    status           VARCHAR(20)  DEFAULT 'PENDING', -- PENDING, SENT, CHECKED, APPLIED, FAILED
    poll_interval_sec INTEGER     DEFAULT 30,
    last_polled_at   TIMESTAMP,
    deployed_at      TIMESTAMP,
    created_at       TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_signage_deploy_store  ON signage_sign_deploy(store_code, status);
CREATE INDEX IF NOT EXISTS idx_signage_deploy_status ON signage_sign_deploy(status, created_at);

INSERT INTO signage_sign_deploy (tenant_code, store_code, deploy_type, code, file_name, file_size, md5, status)
VALUES ('ZIOINFO', '1000', 'SIGN_FILE', '1000', 'sign_20200710130354.csv', 42553, '9F515EFD02CCFD16454E76B93E7B34B4', 'PENDING')
ON CONFLICT DO NOTHING;

-- ── 배포 전파 큐(펌웨어/간판정보 태그 전송 추적) ─────────────────────────────
CREATE TABLE IF NOT EXISTS signage_update_queue (
    id              BIGSERIAL PRIMARY KEY,
    tenant_code     VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    store_id        BIGINT       REFERENCES signage_store(id),
    binding_id      BIGINT       REFERENCES signage_signboard(id),
    device_id       VARCHAR(100) NOT NULL,
    product_code    VARCHAR(100),
    new_price       NUMERIC(12,2),
    new_sale_price  NUMERIC(12,2),
    template_code   VARCHAR(50),
    render_data     TEXT,
    priority        INTEGER DEFAULT 5,
    status          VARCHAR(20) DEFAULT 'PENDING',    -- PENDING, SENDING, SENT, CONFIRMED, FAILED, CANCELLED
    retry_count     INTEGER DEFAULT 0,
    max_retry       INTEGER DEFAULT 3,
    error_message   TEXT,
    sent_at         TIMESTAMP,
    confirmed_at    TIMESTAMP,
    pos_cvt_id      BIGINT REFERENCES signage_pos_cvt(id),
    created_at      TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_signage_queue_device ON signage_update_queue(device_id, status);
CREATE INDEX IF NOT EXISTS idx_signage_queue_store  ON signage_update_queue(store_id, status, created_at);
CREATE INDEX IF NOT EXISTS idx_signage_queue_pend   ON signage_update_queue(status, priority, created_at) WHERE status = 'PENDING';

-- ── 백업/복구(일 1회 · 매장코드별) ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_backup (
    id           BIGSERIAL PRIMARY KEY,
    tenant_code  VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    store_code   VARCHAR(50)  NOT NULL,
    backup_type  VARCHAR(30)  DEFAULT 'FULL',   -- FULL, SIGN, CONFIG
    file_name    VARCHAR(300),
    file_size    BIGINT,
    md5          VARCHAR(64),
    file_path    VARCHAR(500),
    status       VARCHAR(20)  DEFAULT 'SUCCESS', -- SUCCESS, FAILED, RESTORING, RESTORED
    backup_at    TIMESTAMP DEFAULT NOW(),
    restored_at  TIMESTAMP,
    created_at   TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_signage_backup_store ON signage_backup(store_code, backup_at DESC);

INSERT INTO signage_backup (tenant_code, store_code, backup_type, file_name, file_size, status)
VALUES ('ZIOINFO', '1000', 'FULL', 'backup_1000_20200710.zip', 1048576, 'SUCCESS')
ON CONFLICT DO NOTHING;

-- ── 광고/스케줄(주기 광고 편성) ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_ad_schedule (
    id           BIGSERIAL PRIMARY KEY,
    tenant_code  VARCHAR(20)  NOT NULL REFERENCES signage_tenant(tenant_code),
    store_id     BIGINT REFERENCES signage_store(id),
    store_code   VARCHAR(50),
    ad_name      VARCHAR(200) NOT NULL,
    media_type   VARCHAR(20)  DEFAULT 'IMAGE',  -- IMAGE, VIDEO
    media_path   VARCHAR(500),
    template_code VARCHAR(50),
    start_at     TIMESTAMP,
    end_at       TIMESTAMP,
    daily_start  VARCHAR(5),                    -- HH:MM
    daily_end    VARCHAR(5),
    priority     INTEGER DEFAULT 5,
    status       VARCHAR(20) DEFAULT 'SCHEDULED', -- SCHEDULED, ACTIVE, ENDED, PAUSED
    created_at   TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_signage_ad_store  ON signage_ad_schedule(store_id, status);
CREATE INDEX IF NOT EXISTS idx_signage_ad_status ON signage_ad_schedule(status, start_at);

INSERT INTO signage_ad_schedule (tenant_code, store_id, store_code, ad_name, media_type, template_code, daily_start, daily_end, status)
SELECT 'ZIOINFO', s.id, '1000', '아침 특가 광고', 'IMAGE', 'SIGN-PROMO', '07:00', '11:00', 'ACTIVE'
FROM signage_store s WHERE s.store_code = '1000' LIMIT 1
ON CONFLICT DO NOTHING;

-- ── 감사 로그 ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_audit_log (
    id          BIGSERIAL PRIMARY KEY,
    tenant_code VARCHAR(20),
    username    VARCHAR(200),
    action      VARCHAR(200) NOT NULL,
    resource    VARCHAR(200),
    resource_id VARCHAR(100),
    detail      TEXT,
    ip_address  VARCHAR(50),
    user_agent  VARCHAR(500),
    success     BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_signage_audit_created ON signage_audit_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_signage_audit_tenant ON signage_audit_log(tenant_code, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_signage_audit_action ON signage_audit_log(action, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_signage_audit_user   ON signage_audit_log(username, created_at DESC);

-- ── 시스템 설정(admin 보조) ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS signage_setting (
    setting_key   VARCHAR(100) PRIMARY KEY,
    setting_value TEXT,
    category      VARCHAR(50) DEFAULT 'GENERAL',
    description   VARCHAR(300),
    updated_at    TIMESTAMP DEFAULT NOW()
);

INSERT INTO signage_setting (setting_key, setting_value, category, description) VALUES
  ('system.name',              'GUARDiA SIGNAGE', 'GENERAL', '시스템 표시명'),
  ('deploy.poll.interval.sec', '30',              'DEPLOY',  '간판정보 폴링 주기(초)'),
  ('deploy.retry.max',         '3',               'DEPLOY',  '배포 전송 실패 재시도 횟수'),
  ('backup.schedule.daily',    '03:00',           'BACKUP',  '매장코드별 일 1회 백업 시각'),
  ('alarm.retention.days',     '90',              'ALARM',   '알람 이력 보존 일수'),
  ('audit.retention.days',     '365',             'GENERAL', '감사 로그 보존 일수'),
  ('ai.ollama.text.model',     'llama3.2:1b',     'AI',      'Ollama 텍스트 모델'),
  ('ai.ollama.enabled',        'true',            'AI',      'AI 분석 기능 사용 여부')
ON CONFLICT (setting_key) DO NOTHING;
