CREATE TABLE payload (
    pid CHAR(22) NOT NULL PRIMARY KEY ,
    title VARCHAR(100) NOT NULL ,
    subtitle VARCHAR(100) ,
    body VARCHAR(255) NOT NULL ,
    alert_yn BOOLEAN NOT NULL DEFAULT TRUE,
    category CHAR(4) NOT NULL ,
    content TEXT
);


CREATE TABLE push_cabinet (
    uid CHAR(6) NOT NULL ,
    pid CHAR(22) NOT NULL ,
    send_date DATETIME NOT NULL ,
    check_yn BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (uid, pid),
    CONSTRAINT fk_pushcabinet_uid_to_user FOREIGN KEY (uid)
        REFERENCES user(uid)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_pushcabinet_pid_to_payload FOREIGN KEY (pid)
        REFERENCES payload(pid)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);



INSERT INTO payload(pid, title, subtitle, body, category, content)
VALUES (
        'AP202503054Q81114002W7',
        'PUSH 확인 TEST',
        'PUSH 확인 TEST - SUB',
        'PUSH를 테스트하기 위해서 작성된 내용입니다. TEST 메시지입나다.',
        'PC00',
        NULL
       );



INSERT INTO payload(pid, title, subtitle, body, category, content)
VALUES (
           'AP202503051S41516009J9',
           'PUSH 확인 TEST2',
           NULL,
           'PUSH를 테스트하기 위해서 작성된 두번째 내용입니다. 두번째 TEST 메시지입나다.',
           'PC00',
           NULL
       );



ALTER TABLE push_cabinet DROP FOREIGN KEY fk_pushcabinet_uid_to_user;

ALTER TABLE push_cabinet
    ADD CONSTRAINT fk_pushcabinet_uid_to_user
        FOREIGN KEY (uid) REFERENCES user(uid)
            ON UPDATE CASCADE ON DELETE CASCADE;


CREATE TABLE log_push (
    bid CHAR(6) NOT NULL ,
    pid CHAR(22) NOT NULL ,
    create_date DATETIME NOT NULL ,
    modify_date DATETIME NOT NULL ,
    create_uid VARCHAR(70) NOT NULL ,
    modify_uid VARCHAR(70) NOT NULL ,
    PRIMARY KEY (bid, pid),
    CONSTRAINT  fk_logpush_to_payload FOREIGN KEY (bid, pid)
        REFERENCES payload(bid, pid)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);




SELECT cabinet.uid, cabinet.pid, cabinet.send_date, payload.title
FROM
    (select push_cabinet.pid, push_cabinet.uid, push_cabinet.send_date FROM push_cabinet where uid = 'AMsean.kim20241201') As cabinet
inner JOIN
    (select payload.title, payload.pid FROM payload) as payload
on cabinet.pid = payload.pid
order by send_date
;


CREATE TABLE push_cabinet
(
    id         INT AUTO_INCREMENT PRIMARY KEY,  -- 대리 키로 추가
    uid        VARCHAR(70)                 NOT NULL,
    bid        CHAR(6)     DEFAULT 'AA0000' NOT NULL,
    pid        CHAR(22)                    NOT NULL,
    send_date  DATETIME                    NOT NULL,
    check_yn   TINYINT(1)  DEFAULT 0        NOT NULL,
    content    TEXT,                             -- 커스텀 이벤트 내용 저장 (필요 시 TEXT나 VARCHAR로 조정)

    CONSTRAINT fk_pushcabinet_bid_to_academy
        FOREIGN KEY (bid) REFERENCES academy (bid)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_pushcabinet_payload
        FOREIGN KEY (bid, pid) REFERENCES payload (bid, pid)
            ON UPDATE CASCADE ON DELETE CASCADE
);

-- 조회 성능 향상을 위해 pid 컬럼에 대한 인덱스 추가 (필요 시 uid, send_date 등의 인덱스도 고려)
CREATE INDEX idx_pushcabinet_pid ON push_cabinet (pid);

ALTER TABLE push_cabinet
ADD CONSTRAINT fk_pushcabinet_bid_to_academy
    FOREIGN KEY (bid) REFERENCES academy (bid)
        ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE push_cabinet
    ADD CONSTRAINT fk_pushcabinet_payload
        FOREIGN KEY (bid, pid) REFERENCES payload (bid, pid)
            ON UPDATE CASCADE ON DELETE CASCADE;