create table academy
(
    bid              char(6)      not null
        primary key,
    business_name    varchar(100) not null,
    business_owner   varchar(50)  not null,
    business_number  varchar(20)  not null,
    business_date    datetime     not null,
    business_address varchar(200) not null,
    business_contact varchar(15)  not null,
    uid              varchar(70)  not null
);

create table payload
(
    pid      char(22)             not null
        primary key,
    title    varchar(100)         not null,
    subtitle varchar(100)         null,
    body     varchar(255)         not null,
    alert_yn tinyint(1) default 1 not null,
    category char(4)              not null,
    content  text                 null
);

create table push_cabinet
(
    uid       varchar(70)          not null,
    pid       char(22)             not null,
    send_date datetime             not null,
    check_yn  tinyint(1) default 0 not null,
    primary key (uid, pid),
    constraint fk_pushcabinet_pid_to_payload
        foreign key (pid) references payload (pid)
            on update cascade on delete cascade,
    constraint fk_pushcabinet_uid_to_user
        foreign key (uid) references user (uid)
            on update cascade on delete cascade
);


ALTER TABLE payload
    ADD COLUMN bid CHAR(6) NOT NULL DEFAULT '000000' AFTER pid
;

ALTER TABLE payload
    DROP PRIMARY KEY ,
    ADD PRIMARY KEY (bid, pid)
;

ALTER TABLE payload
    ADD CONSTRAINT fk_payload_bid_to_academy
        FOREIGN KEY (bid) REFERENCES academy(bid)
            ON UPDATE CASCADE ON DELETE CASCADE
;


# //------//
ALTER TABLE push_cabinet
    ADD COLUMN bid CHAR(6) NOT NULL DEFAULT 'AA0000' AFTER uid
;

ALTER TABLE push_cabinet
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (bid, uid, pid)
;


ALTER TABLE push_cabinet
    DROP FOREIGN KEY fk_pushcabinet_pid_to_payload
;

ALTER TABLE push_cabinet
    DROP FOREIGN KEY fk_pushcabinet_uid_to_user
;

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


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