CREATE TABLE review (
   `review_id`   bigint   NOT NULL   AUTO_INCREMENT PRIMARY KEY,
   `user_id`   bigint   NOT NULL,
   `program_id`   bigint   NOT NULL,
   `content`   VARCHAR(255)   NOT NULL,
   `rating`   double  NOT NULL,
   `genre`   VARCHAR(255)   NOT NULL,
   `like_counts` int NULL DEFAULT 0,
   `created_at` datetime NULL,
   `updated_at` datetime NULL
);

CREATE TABLE user (
   `user_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `social_type`   VARCHAR(255)   NOT NULL   COMMENT '"Naver", "Google"',
   `email`   VARCHAR(255)   NOT NULL,
   `nick_name`   VARCHAR(255)   NOT NULL,
   `profile_photo`   VARCHAR(255)   NULL,
   `average_rating`   double   NULL   DEFAULT 0.00,
   `grade`   VARCHAR(255)   NOT NULL   COMMENT '"general", "advanced"',
   `role`   VARCHAR(255)   NOT NULL   COMMENT '"guest", "user"',
   `code`   VARCHAR(255)   NULL,
   `review_counts` int NULL DEFAULT 0,
   `created_at` datetime NULL,
   `updated_at` datetime NULL
);

CREATE TABLE program (
   `program_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `title`   VARCHAR(255)   NOT NULL,
   `poster_path`   VARCHAR(255)   NULL,
   `average_rating`   double   NOT NULL   DEFAULT 0.00,
   `review_count`   int   NOT NULL DEFAULT 0,
   `tm_db_program_id`   bigint   NOT NULL,
   `type`   VARCHAR(255)   NOT NULL   COMMENT '"TV", "Movie"',
   `created_year` VARCHAR(10) NULL
);

CREATE TABLE ott (
   `ott_id`   bigint(255)   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `name`   VARCHAR(255)   NOT NULL,
   `logo_path`   VARCHAR(255)   NOT NULL,
   `subscribe_logo_path`   VARCHAR(255)   NOT NULL,
	 `tm_db_provider_id`   bigint(255)   NULL,
);

CREATE TABLE liked_program (
   `liked_program_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `user_id`   bigint   NOT NULL,
   `program_id`   bigint   NOT NULL,
);

CREATE TABLE review_review_tag (
   `review_review_tag_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `review_id`   bigint   NOT NULL,
   `review_tag_id`   bigint   NOT NULL,
);

CREATE TABLE liked_review (
   `liked_review_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `user_id`   bigint   NOT NULL,
   `review_id`   bigint   NOT NULL,
);

CREATE TABLE uninterested_program (
   `uninterested_program_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `user_id`   bigint   NOT NULL,
   `program_id`   bigint   NOT NULL,
);

CREATE TABLE user_subscribingott (
   `user_subscribingott_id`   bigint   NOT NULL AUTO_INCREMENT   PRIMARY KEY,
   `user_id`   bigint   NOT NULL,
   `ott_id`   bigint   NOT NULL,
);

CREATE TABLE genre (
   `genre_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `name`   VARCHAR(255)   NOT NULL,
   `tm_db_genre_id`   bigint   NOT NULL,
);

CREATE TABLE program_genre (
   `program_genre_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `program_id`   bigint   NOT NULL,
   `genre_id`   bigint   NOT NULL,
);

CREATE TABLE user_genre (
   `user_genre_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `user_id`   bigint   NOT NULL,
   `genre_id`   bigint   NOT NULL,
   `is_first` tinyint(1) NULL,
);

CREATE TABLE review_tag (
   `review_tag_id`   bigint   NOT NULL AUTO_INCREMENT   PRIMARY KEY,
   `name`   VARCHAR(255)   NOT NULL,
);

CREATE TABLE community (
   `community_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `user_id`   bigint   NOT NULL,
   `program_id`   bigint   NOT NULL,
   `title`   VARCHAR(255)   NOT NULL,
   `content`   VARCHAR(255)   NOT NULL,
   `image_url` VARCHAR(255) NULL,
	 `comment_count`   int   NOT NULL   DEFAULT 0,
	 `like_count`   int   NOT NULL   DEFAULT 0,
   `created_at` datetime NULL,
   `updated_at` datetime NULL
);

CREATE TABLE reply (
   `reply_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `community_id`   bigint   NOT NULL,
   `parent_id`   bigint   NOT NULL,
   `user_id`   bigint   NOT NULL,
   `content`   VARCHAR(255)   NOT NULL,
	 `like_count`   int   NOT NULL   DEFAULT 0,
   `created_at` datetime NULL,
   `updated_at` datetime NULL
);

CREATE TABLE liked_community (
   `liked_community_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `user_id`   bigint   NOT NULL,
   `community_id`   bigint   NOT NULL,
);

CREATE TABLE liked_reply (
   `liked_reply_id`   bigint   NOT NULL   AUTO_INCREMENT   PRIMARY KEY,
   `user_id`   bigint   NOT NULL,
   `reply_id`   bigint   NOT NULL,
   `community_id`   bigint   NOT NULL,
);

ALTER TABLE review ADD CONSTRAINT `FK_user_TO_review_1` FOREIGN KEY (
   `user_id`
)
REFERENCES user (
   `user_id`
);

ALTER TABLE review ADD CONSTRAINT `FK_program_TO_review_1` FOREIGN KEY (
   `program_id`
)
REFERENCES program (
   `program_id`
);

ALTER TABLE liked_program ADD CONSTRAINT `FK_user_TO_liked_program_1` FOREIGN KEY (
   `user_id`
)
REFERENCES user (
   `user_id`
);

ALTER TABLE liked_program ADD CONSTRAINT `FK_program_TO_liked_program_1` FOREIGN KEY (
   `program_id`
)
REFERENCES program (
   `program_id`
);

ALTER TABLE review_review_tag ADD CONSTRAINT `FK_review_TO_review_review_tag_1` FOREIGN KEY (
   `review_id`
)
REFERENCES review (
   `review_id`
);

ALTER TABLE review_review_tag ADD CONSTRAINT `FK_review_tag_TO_review_review_tag_1` FOREIGN KEY (
   `review_tag_id`
)
REFERENCES review_tag (
   `review_tag_id`
);

ALTER TABLE liked_review ADD CONSTRAINT `FK_user_TO_liked_review_1` FOREIGN KEY (
   `user_id`
)
REFERENCES user (
   `user_id`
);

ALTER TABLE liked_review ADD CONSTRAINT `FK_review_TO_liked_review_1` FOREIGN KEY (
   `review_id`
)
REFERENCES review (
   `review_id`
);

ALTER TABLE uninterested_program ADD CONSTRAINT `FK_user_TO_uninterested_program_1` FOREIGN KEY (
   `user_id`
)
REFERENCES user (
   `user_id`
);

ALTER TABLE uninterested_program ADD CONSTRAINT `FK_program_TO_uninterested_program_1` FOREIGN KEY (
   `program_id`
)
REFERENCES program (
   `program_id`
);

ALTER TABLE user_subscribingott ADD CONSTRAINT `FK_user_TO_user_subscribingott_1` FOREIGN KEY (
   `user_id`
)
REFERENCES user (
   `user_id`
);

ALTER TABLE user_subscribingott ADD CONSTRAINT `FK_ott_TO_user_subscribingott_1` FOREIGN KEY (
   `ott_id`
)
REFERENCES ott (
   `ott_id`
);

ALTER TABLE program_genre ADD CONSTRAINT `FK_program_TO_program_genre_1` FOREIGN KEY (
   `program_id`
)
REFERENCES program (
   `program_id`
);

ALTER TABLE program_genre ADD CONSTRAINT `FK_genre_TO_program_genre_1` FOREIGN KEY (
   `genre_id`
)
REFERENCES genre (
   `genre_id`
);

ALTER TABLE user_genre ADD CONSTRAINT `FK_user_TO_user_genre_1` FOREIGN KEY (
   `user_id`
)
REFERENCES user (
   `user_id`
);

ALTER TABLE user_genre ADD CONSTRAINT `FK_genre_TO_user_genre_1` FOREIGN KEY (
   `genre_id`
)
REFERENCES genre (
   `genre_id`
);

ALTER TABLE community ADD CONSTRAINT `FK_user_TO_community_1` FOREIGN KEY (
   `user_id`
)
REFERENCES user (
   `user_id`
);

ALTER TABLE community ADD CONSTRAINT `FK_program_TO_community_1` FOREIGN KEY (
   `program_id`
)
REFERENCES program (
   `program_id`
);

ALTER TABLE reply ADD CONSTRAINT `FK_community_TO_reply_1` FOREIGN KEY (
   `community_id`
)
REFERENCES community (
   `community_id`
);

ALTER TABLE liked_community ADD CONSTRAINT `FK_user_TO_liked_community_1` FOREIGN KEY (
   `user_id`
)
REFERENCES user (
   `user_id`
);

ALTER TABLE liked_community ADD CONSTRAINT `FK_community_TO_liked_community_1` FOREIGN KEY (
   `community_id`
)
REFERENCES community (
   `program_id`
);

ALTER TABLE liked_reply ADD CONSTRAINT `FK_user_TO_liked_reply_1` FOREIGN KEY (
   `user_id`
)
REFERENCES user (
   `user_id`
);

ALTER TABLE liked_reply ADD CONSTRAINT `FK_reply_TO_liked_reply_1` FOREIGN KEY (
   `reply_id`
)
REFERENCES reply (
   `reply_id`
);

ALTER TABLE liked_reply ADD CONSTRAINT `FK_reply_TO_liked_reply_2` FOREIGN KEY (
   `community_id`
)
REFERENCES reply (
   `community_id`
);