-- vl_ddl_0002.sql - 정합성 DBIO 스키마/뷰 정의 (medium)
-- 런타임 PostgreSQL 에 적용. 컴파일에는 불필요.

CREATE TABLE IF NOT EXISTS vl_ledger (
    key       VARCHAR(15)  NOT NULL,
    merch_id  VARCHAR(15)  NOT NULL,
    card_no   VARCHAR(19)  NOT NULL,
    amount    BIGINT       NOT NULL DEFAULT 0,
    biz_date  CHAR(8)      NOT NULL,
    status    CHAR(1)      NOT NULL DEFAULT 'R',
    fee       BIGINT       NOT NULL DEFAULT 0,
    reg_ts    TIMESTAMP    NOT NULL DEFAULT now(),
    upd_ts    TIMESTAMP,
    CONSTRAINT pk_vl_ledger PRIMARY KEY (key)
);

CREATE INDEX IF NOT EXISTS ix_vl_ddl_0002_date_status
    ON vl_ledger (biz_date, status);

CREATE OR REPLACE VIEW vl_ddl_0002_daily_v AS
    SELECT biz_date, status, count(*) AS cnt, sum(amount) AS total
    FROM vl_ledger
    GROUP BY biz_date, status;
