CREATE TABLE `category` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `update_dt` DATETIME NOT NULL,
  `is_available` BOOLEAN NOT NULL,
  `order` INTEGER NOT NULL
);

CREATE TABLE `detail` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `key` VARCHAR(255) NOT NULL
);

CREATE TABLE `event` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `detail` LONGTEXT NOT NULL,
  `start_date` DATETIME NOT NULL,
  `end_date` DATETIME NOT NULL,
  `status` INTEGER NOT NULL,
  `is_regular` BOOLEAN NOT NULL
);

CREATE TABLE `role` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL
);

CREATE TABLE `user` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `user_name` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `update_dt` DATETIME NOT NULL
);

CREATE TABLE `announcement` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `user` INTEGER NOT NULL,
  `detail` LONGTEXT NOT NULL,
  `update_dt` DATETIME NOT NULL
);

CREATE INDEX `idx_announcement__user` ON `announcement` (`user`);

ALTER TABLE `announcement` ADD CONSTRAINT `fk_announcement__user` FOREIGN KEY (`user`) REFERENCES `user` (`id`);

CREATE TABLE `member` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `user` INTEGER NOT NULL,
  `status` INTEGER NOT NULL,
  `update_dt` DATETIME NOT NULL
);

CREATE INDEX `idx_member__user` ON `member` (`user`);

ALTER TABLE `member` ADD CONSTRAINT `fk_member__user` FOREIGN KEY (`user`) REFERENCES `user` (`id`);

CREATE TABLE `cart` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `member` INTEGER NOT NULL,
  `user` INTEGER NOT NULL,
  `general_total` DOUBLE NOT NULL,
  `discount` DOUBLE NOT NULL,
  `total` DOUBLE NOT NULL,
  `update_dt` DATETIME NOT NULL
);

CREATE INDEX `idx_cart__member` ON `cart` (`member`);

CREATE INDEX `idx_cart__user` ON `cart` (`user`);

ALTER TABLE `cart` ADD CONSTRAINT `fk_cart__member` FOREIGN KEY (`member`) REFERENCES `member` (`id`);

ALTER TABLE `cart` ADD CONSTRAINT `fk_cart__user` FOREIGN KEY (`user`) REFERENCES `user` (`id`);

CREATE TABLE `event_comment` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `event` INTEGER NOT NULL,
  `member` INTEGER NOT NULL,
  `detail` LONGTEXT NOT NULL,
  `update_dt` DATETIME NOT NULL
);

CREATE INDEX `idx_event_comment__event` ON `event_comment` (`event`);

CREATE INDEX `idx_event_comment__member` ON `event_comment` (`member`);

ALTER TABLE `event_comment` ADD CONSTRAINT `fk_event_comment__event` FOREIGN KEY (`event`) REFERENCES `event` (`id`);

ALTER TABLE `event_comment` ADD CONSTRAINT `fk_event_comment__member` FOREIGN KEY (`member`) REFERENCES `member` (`id`);

CREATE TABLE `event_member` (
  `event` INTEGER NOT NULL,
  `member` INTEGER NOT NULL,
  PRIMARY KEY (`event`, `member`)
);

CREATE INDEX `idx_event_member` ON `event_member` (`member`);

ALTER TABLE `event_member` ADD CONSTRAINT `fk_event_member__event` FOREIGN KEY (`event`) REFERENCES `event` (`id`);

ALTER TABLE `event_member` ADD CONSTRAINT `fk_event_member__member` FOREIGN KEY (`member`) REFERENCES `member` (`id`);

CREATE TABLE `event_photo` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `event` INTEGER NOT NULL,
  `member` INTEGER NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `update_dt` DATETIME NOT NULL
);

CREATE INDEX `idx_event_photo__event` ON `event_photo` (`event`);

CREATE INDEX `idx_event_photo__member` ON `event_photo` (`member`);

ALTER TABLE `event_photo` ADD CONSTRAINT `fk_event_photo__event` FOREIGN KEY (`event`) REFERENCES `event` (`id`);

ALTER TABLE `event_photo` ADD CONSTRAINT `fk_event_photo__member` FOREIGN KEY (`member`) REFERENCES `member` (`id`);

CREATE TABLE `member_detail` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `member` INTEGER NOT NULL,
  `detail` INTEGER NOT NULL,
  `value` VARCHAR(255) NOT NULL,
  `privilege` INTEGER NOT NULL
);

CREATE INDEX `idx_member_detail__detail` ON `member_detail` (`detail`);

CREATE INDEX `idx_member_detail__member` ON `member_detail` (`member`);

ALTER TABLE `member_detail` ADD CONSTRAINT `fk_member_detail__detail` FOREIGN KEY (`detail`) REFERENCES `detail` (`id`);

ALTER TABLE `member_detail` ADD CONSTRAINT `fk_member_detail__member` FOREIGN KEY (`member`) REFERENCES `member` (`id`);

CREATE TABLE `producer` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `user` INTEGER NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `location` VARCHAR(255) NOT NULL,
  `description` LONGTEXT NOT NULL,
  `update_dt` DATETIME NOT NULL
);

CREATE INDEX `idx_producer__user` ON `producer` (`user`);

ALTER TABLE `producer` ADD CONSTRAINT `fk_producer__user` FOREIGN KEY (`user`) REFERENCES `user` (`id`);

CREATE TABLE `producer_photo` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `producer` INTEGER NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `is_valid` BOOLEAN,
  `update_dt` DATETIME NOT NULL
);

CREATE INDEX `idx_producer_photo__producer` ON `producer_photo` (`producer`);

ALTER TABLE `producer_photo` ADD CONSTRAINT `fk_producer_photo__producer` FOREIGN KEY (`producer`) REFERENCES `producer` (`id`);

CREATE TABLE `product` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `category` INTEGER NOT NULL,
  `producer` INTEGER NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` LONGTEXT NOT NULL,
  `price` DOUBLE NOT NULL,
  `stock` DOUBLE NOT NULL,
  `quantity_type` INTEGER NOT NULL,
  `status` INTEGER NOT NULL,
  `update_dt` DATETIME NOT NULL
);

CREATE INDEX `idx_product__category` ON `product` (`category`);

CREATE INDEX `idx_product__producer` ON `product` (`producer`);

ALTER TABLE `product` ADD CONSTRAINT `fk_product__category` FOREIGN KEY (`category`) REFERENCES `category` (`id`);

ALTER TABLE `product` ADD CONSTRAINT `fk_product__producer` FOREIGN KEY (`producer`) REFERENCES `producer` (`id`);

CREATE TABLE `cart_item` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `cart` INTEGER NOT NULL,
  `product` INTEGER NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `quantity` DOUBLE NOT NULL,
  `price` DOUBLE NOT NULL
);

CREATE INDEX `idx_cart_item__cart` ON `cart_item` (`cart`);

CREATE INDEX `idx_cart_item__product` ON `cart_item` (`product`);

ALTER TABLE `cart_item` ADD CONSTRAINT `fk_cart_item__cart` FOREIGN KEY (`cart`) REFERENCES `cart` (`id`);

ALTER TABLE `cart_item` ADD CONSTRAINT `fk_cart_item__product` FOREIGN KEY (`product`) REFERENCES `product` (`id`);

CREATE TABLE `product_comment` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `product` INTEGER NOT NULL,
  `member` INTEGER NOT NULL,
  `detail` LONGTEXT NOT NULL,
  `update_dt` DATETIME NOT NULL
);

CREATE INDEX `idx_product_comment__member` ON `product_comment` (`member`);

CREATE INDEX `idx_product_comment__product` ON `product_comment` (`product`);

ALTER TABLE `product_comment` ADD CONSTRAINT `fk_product_comment__member` FOREIGN KEY (`member`) REFERENCES `member` (`id`);

ALTER TABLE `product_comment` ADD CONSTRAINT `fk_product_comment__product` FOREIGN KEY (`product`) REFERENCES `product` (`id`);

CREATE TABLE `product_extra_detail` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `product` INTEGER NOT NULL,
  `key` VARCHAR(255) NOT NULL,
  `value` VARCHAR(255) NOT NULL
);

CREATE INDEX `idx_product_extra_detail__product` ON `product_extra_detail` (`product`);

ALTER TABLE `product_extra_detail` ADD CONSTRAINT `fk_product_extra_detail__product` FOREIGN KEY (`product`) REFERENCES `product` (`id`);

CREATE TABLE `product_photo` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `product` INTEGER NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `is_valid` BOOLEAN
);

CREATE INDEX `idx_product_photo__product` ON `product_photo` (`product`);

ALTER TABLE `product_photo` ADD CONSTRAINT `fk_product_photo__product` FOREIGN KEY (`product`) REFERENCES `product` (`id`);

CREATE TABLE `product_rate` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `product` INTEGER NOT NULL,
  `member` INTEGER NOT NULL,
  `value` INTEGER NOT NULL,
  `update_dt` DATETIME NOT NULL
);

CREATE INDEX `idx_product_rate__member` ON `product_rate` (`member`);

CREATE INDEX `idx_product_rate__product` ON `product_rate` (`product`);

ALTER TABLE `product_rate` ADD CONSTRAINT `fk_product_rate__member` FOREIGN KEY (`member`) REFERENCES `member` (`id`);

ALTER TABLE `product_rate` ADD CONSTRAINT `fk_product_rate__product` FOREIGN KEY (`product`) REFERENCES `product` (`id`);

CREATE TABLE `user_role` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `role` INTEGER NOT NULL
);

CREATE INDEX `idx_user_role__role` ON `user_role` (`role`);

ALTER TABLE `user_role` ADD CONSTRAINT `fk_user_role__role` FOREIGN KEY (`role`) REFERENCES `role` (`id`);

CREATE TABLE `user_user_role` (
  `user` INTEGER NOT NULL,
  `user_role` INTEGER NOT NULL,
  PRIMARY KEY (`user`, `user_role`)
);

CREATE INDEX `idx_user_user_role` ON `user_user_role` (`user_role`);

ALTER TABLE `user_user_role` ADD CONSTRAINT `fk_user_user_role__user` FOREIGN KEY (`user`) REFERENCES `user` (`id`);

ALTER TABLE `user_user_role` ADD CONSTRAINT `fk_user_user_role__user_role` FOREIGN KEY (`user_role`) REFERENCES `user_role` (`id`);

CREATE TABLE `work_event` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `description` LONGTEXT NOT NULL,
  `slot` INTEGER NOT NULL,
  `work_date` DATETIME NOT NULL,
  `name_surname` VARCHAR(255) NOT NULL,
  `update_dt` DATETIME NOT NULL
);

CREATE TABLE `user_work_event` (
  `user` INTEGER NOT NULL,
  `work_event` INTEGER NOT NULL,
  PRIMARY KEY (`user`, `work_event`)
);

CREATE INDEX `idx_user_work_event` ON `user_work_event` (`work_event`);

ALTER TABLE `user_work_event` ADD CONSTRAINT `fk_user_work_event__user` FOREIGN KEY (`user`) REFERENCES `user` (`id`);

ALTER TABLE `user_work_event` ADD CONSTRAINT `fk_user_work_event__work_event` FOREIGN KEY (`work_event`) REFERENCES `work_event` (`id`)