CREATE DATABASE primavera DEFAULT CHARACTER SET utf8mb4;

CREATE USER 'primavera'@'localhost' IDENTIFIED BY 'primavera';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON primavera.* TO 'primavera'@'localhost';

CREATE TABLE IF NOT EXISTS USER
(
    ID       BIGINT(20)   NOT NULL AUTO_INCREMENT,
    EMAIL    VARCHAR(50)  NOT NULL,
    PASSWORD VARCHAR(100) NOT NULL,
    NICKNAME VARCHAR(45)  NOT NULL,
    STATUS   CHAR(1)      NOT NULL DEFAULT 'A',
    REG_DT   DATETIME     NOT NULL,
    MOD_DT   DATETIME              DEFAULT NULL,
    PRIMARY KEY (ID),
    UNIQUE KEY EMAIL_UNIQUE (EMAIL)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS USER_CONNECTION
(
    ID           BIGINT(20)   NOT NULL AUTO_INCREMENT,
    EMAIL        VARCHAR(50)  NOT NULL,
    PROVIDER     TINYINT(11)  NOT NULL,
    PROVIDER_ID  VARCHAR(45)   NOT NULL,
    DISPLAY_NAME VARCHAR(45)  DEFAULT NULL,
    PROFILE_URL  VARCHAR(200) DEFAULT NULL,
    IMAGE_URL    VARCHAR(200) DEFAULT NULL,
    ACCESS_TOKEN VARCHAR(200) NOT NULL,
    EXPIRE_TIME  BIGINT(20)   DEFAULT NULL,
    PRIMARY KEY (ID)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS USER_ROLE
(
    USER_ID BIGINT(20) NOT NULL,
    ROLE_ID INT(11)    NOT NULL,
    PRIMARY KEY (USER_ID, ROLE_ID)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS ROLE
(
    ID   INT(11)    NOT NULL AUTO_INCREMENT,
    TYPE TINYINT(3) NOT NULL,
    PRIMARY KEY (ID)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS POST
(
    ID        BIGINT(20)   NOT NULL AUTO_INCREMENT,
    WRITER_ID BIGINT(20)   NOT NULL,
    SUBJECT   VARCHAR(200) NOT NULL,
    CONTENTS  TEXT         NOT NULL,
    STATUS    TINYINT(3)   NOT NULL,
    REG_DT    DATETIME DEFAULT NULL,
    MOD_DT    DATETIME DEFAULT NULL,
    PRIMARY KEY (ID),
    KEY FK_WRITER_ID (WRITER_ID),
    CONSTRAINT FK_POST_WRITER_ID FOREIGN KEY (WRITER_ID) REFERENCES USER (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS ARTICLE
(
    ID BIGINT(20) NOT NULL AUTO_INCREMENT,
    P_ID BIGINT(20) NOT NULL DEFAULT 0,
    REFERENCE BIGINT(20) NOT NULL,
    STEP INT(11) NOT NULL,
    LEVEL INT(11) NOT NULL,
    AUTHOR BIGINT(20) NOT NULL,
    SUBJECT VARCHAR(200) NOT NULL,
    STATUS TINYINT(3) NOT NULL,
    HIT BIGINT(20) NOT NULL DEFAULT 0,
    RECOMMEND BIGINT(20) NOT NULL DEFAULT 0,
    DISAPPROVE BIGINT(20) NOT NULL DEFAULT 0,
    REG_DT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
    MOD_DT TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (ID),
  KEY FK_WRITER_ID_IDX (AUTHOR),
  CONSTRAINT FK_ARTICLE_AUTHOR_ID FOREIGN KEY (AUTHOR) REFERENCES USER (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ARTICLE_ATTACHMENT (
  ID BIGINT(20) NOT NULL AUTO_INCREMENT,
  ARTICLE_ID BIGINT(20) NOT NULL,
  NAME VARCHAR(100) NOT NULL,
  PATH VARCHAR(200) NOT NULL,
  SIZE INT(11) NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE  IF NOT EXISTS ARTICLE_COMMENT 
(
  ID BIGINT(20) NOT NULL AUTO_INCREMENT,
  ARTICLE_ID BIGINT(20) NOT NULL,
  LEVEL INT(11) NOT NULL,
  STEP INT(11) NOT NULL,
  COMMENT TEXT NOT NULL,
  AUTHOR VARCHAR(45) NOT NULL,
  STATUS TINYINT(3) NOT NULL,
  REG_DT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
  MOD_DT TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ARTICLE_CONTENT
(
    ID         BIGINT(20) NOT NULL AUTO_INCREMENT,
    ARTICLE_ID BIGINT(20) DEFAULT NULL,
    CONTENTS   text       DEFAULT NULL,
    PRIMARY KEY (ID),
    KEY FK_AUTHOR_IDX (ARTICLE_ID),
    CONSTRAINT FK_ARTICLE_ID FOREIGN KEY (ARTICLE_ID) REFERENCES ARTICLE (ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS WINNER 
(
  ID int(11) NOT NULL AUTO_INCREMENT,
  USER_ID int(45) NOT NULL,
  WINNER enum('WINNER','LOSER') NOT NULL,
  REG_DT datetime NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO ROLE (TYPE)
VALUES (1);
INSERT INTO ROLE (TYPE)
VALUES (2);
INSERT INTO ROLE (TYPE)
VALUES (3);

INSERT INTO `USER`(EMAIL, PASSWORD, NICKNAME, STATUS, REG_DT)
VALUES ('Genius Choi', '{bcrypt}$2a$10$7UEHLpn1r4gZY2qxiZFJ5.7wa3Hdz8IXgxUtFogy0Ac10fh7TG4V.', 'Genius', 1, NOW());
INSERT INTO `USER_CONNECTION`(EMAIL, PROVIDER, PROVIDER_ID, DISPLAY_NAME, PROFILE_URL, IMAGE_URL, ACCESS_TOKEN, EXPIRE_TIME)
VALUES ('Genius Choi', 1, 123456789, 'Genius', 'PROFILE', 'IMAGE', '1234567890', -1);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (1, 1);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (1, 2);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (1, 3);

INSERT INTO `USER`(`EMAIL`, `PASSWORD`, `NICKNAME`, `STATUS`, `REG_DT`)
VALUES ('Son Heung-min', '{bcrypt}$2a$10$7UEHLpn1r4gZY2qxiZFJ5.7wa3Hdz8IXgxUtFogy0Ac10fh7TG4V.', 'Son', 1, NOW());
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (2, 1);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (2, 2);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (2, 3);

INSERT INTO `USER`(`EMAIL`, `PASSWORD`, `NICKNAME`, `STATUS`, `REG_DT`)
VALUES ('Lionel Messi', '{bcrypt}$2a$10$7UEHLpn1r4gZY2qxiZFJ5.7wa3Hdz8IXgxUtFogy0Ac10fh7TG4V.', 'Messi', 1, NOW());
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (3, 1);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (3, 2);

INSERT INTO `USER`(`EMAIL`, `PASSWORD`, `NICKNAME`, `STATUS`, `REG_DT`)
VALUES ('Cristiano Ronaldo', '{bcrypt}$2a$10$7UEHLpn1r4gZY2qxiZFJ5.7wa3Hdz8IXgxUtFogy0Ac10fh7TG4V.', 'Ronaldo', 1,NOW());
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (4, 1);