delivery-platform.pdf

delivery-platform.sql

CREATE TABLE `p_users` (
  `username` varchar(100) PRIMARY KEY,
  `nickname` varchar(100),
  `email` varchar(255) UNIQUE NOT NULL,
  `password` varchar(255),
  `role` varchar(50),
  `is_public` boolean,
  `created_at` timestamp,
  `created_by` varchar(100),
  `updated_at` timestamp,
  `updated_by` varchar(100),
  `deleted_at` timestamp,
  `deleted_by` varchar(100)
);

CREATE TABLE `p_role_type` (
  `id` varchar(50) PRIMARY KEY,
  `name` varchar(50) UNIQUE NOT NULL,
  `description` TEXT
);

CREATE TABLE `p_area` (
  `id` varchar(50) PRIMARY KEY,
  `city` varchar(50),
  `district` varchar(50),
  `street` varchar(50)
);

CREATE TABLE `p_store` (
  `id` varchar(50) PRIMARY KEY,
  `name` varchar(100),
  `area_id` varchar(100),
  `detail_address` varchar(200),
  `owner_name` varchar(100),
  `created_at` timestamp,
  `created_by` varchar(100),
  `updated_at` timestamp,
  `updated_by` varchar(100),
  `deleted_at` timestamp,
  `deleted_by` varchar(100)
);

CREATE TABLE `p_ai_request` (
  `id` varchar(50) PRIMARY KEY,
  `request` TEXT,
  `response` TEXT,
  `store_id` varchar(50),
  `created_at` timestamp,
  `created_by` varchar(100),
  `updated_at` timestamp,
  `updated_by` varchar(100),
  `deleted_at` timestamp,
  `deleted_by` varchar(100)
);

CREATE TABLE `p_menu` (
  `id` varchar(50) PRIMARY KEY,
  `name` varchar(100),
  `description` TEXT,
  `price` int,
  `expose_status` varchar(30),
  `store_id` varchar(50),
  `created_at` timestamp,
  `created_by` varchar(100),
  `updated_at` timestamp,
  `updated_by` varchar(100),
  `deleted_at` timestamp,
  `deleted_by` varchar(100)
);

CREATE TABLE `p_image` (
  `id` varchar(50) PRIMARY KEY,
  `sequence` int,
  `url` varchar(200),
  `menu_id` varchar(50),
  `option_id` varchar(50),
  `created_at` timestamp,
  `created_by` varchar(100),
  `updated_at` timestamp,
  `updated_by` varchar(100),
  `deleted_at` timestamp,
  `deleted_by` varchar(100)
);

CREATE TABLE `p_menu_option` (
  `id` varchar(50) PRIMARY KEY,
  `name` varchar(100),
  `description` TEXT,
  `price` int,
  `expose_status` varchar(30),
  `menu_id` varchar(50),
  `created_at` timestamp,
  `created_by` varchar(100),
  `updated_at` timestamp,
  `updated_by` varchar(100),
  `deleted_at` timestamp,
  `deleted_by` varchar(100)
);

CREATE TABLE `p_menu_order` (
  `id` bigint PRIMARY KEY AUTO_INCREMENT,
  `menu_id` varchar(50),
  `menu_count` int,
  `order_id` varchar(50)
);

CREATE TABLE `p_menu_option_order` (
  `id` bigint PRIMARY KEY AUTO_INCREMENT,
  `menu_option_id` varchar(50),
  `menu_option_count` int,
  `order_id` varchar(50)
);

CREATE TABLE `p_order` (
  `id` varchar(50) PRIMARY KEY,
  `total_price` int,
  `customer_name` varchar(100),
  `order_type` varchar(50),
  `created_at` timestamp,
  `created_by` varchar(100),
  `updated_at` timestamp,
  `updated_by` varchar(100),
  `deleted_at` timestamp,
  `deleted_by` varchar(100)
);

CREATE TABLE `p_delivery` (
  `id` varchar(50) PRIMARY KEY,
  `order_id` varchar(50),
  `address` varchar(100),
  `status` varchar(50),
  `created_at` timestamp,
  `created_by` varchar(100),
  `updated_at` timestamp,
  `updated_by` varchar(100),
  `deleted_at` timestamp,
  `deleted_by` varchar(100)
);

CREATE TABLE `p_payment` (
  `id` varchar(50) PRIMARY KEY,
  `order_id` varchar(50),
  `payment_agent_id` varchar(50),
  `payment_pid` varchar(200) UNIQUE NOT NULL,
  `status` varchar(20),
  `created_at` timestamp,
  `created_by` varchar(100),
  `updated_at` timestamp,
  `updated_by` varchar(100),
  `deleted_at` timestamp,
  `deleted_by` varchar(100)
);

CREATE TABLE `p_payment_agent` (
  `id` varchar(50) PRIMARY KEY,
  `name` varchar(100) UNIQUE NOT NULL
);

CREATE TABLE `p_category` (
  `id` varchar(50) PRIMARY KEY,
  `name` varchar(100) UNIQUE NOT NULL,
  `created_at` timestamp,
  `created_by` varchar(100),
  `updated_at` timestamp,
  `updated_by` varchar(100),
  `deleted_at` timestamp,
  `deleted_by` varchar(100)
);

CREATE TABLE `p_category_store` (
  `id` bigint PRIMARY KEY AUTO_INCREMENT,
  `category_id` varchar(50),
  `store_id` varchar(50)
);

CREATE TABLE `p_review` (
  `id` bigint PRIMARY KEY AUTO_INCREMENT,
  `order_id` varchar(50),
  `rate` int,
  `contents` text,
  `created_at` timestamp,
  `created_by` varchar(100),
  `updated_at` timestamp,
  `updated_by` varchar(100),
  `deleted_at` timestamp,
  `deleted_by` varchar(100)
);

ALTER TABLE `p_users` ADD FOREIGN KEY (`role`) REFERENCES `p_role_type` (`name`);

ALTER TABLE `p_users` ADD FOREIGN KEY (`created_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_users` ADD FOREIGN KEY (`updated_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_users` ADD FOREIGN KEY (`deleted_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_store` ADD FOREIGN KEY (`area_id`) REFERENCES `p_area` (`id`);

ALTER TABLE `p_store` ADD FOREIGN KEY (`owner_name`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_store` ADD FOREIGN KEY (`created_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_store` ADD FOREIGN KEY (`updated_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_store` ADD FOREIGN KEY (`deleted_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_ai_request` ADD FOREIGN KEY (`store_id`) REFERENCES `p_store` (`id`);

ALTER TABLE `p_ai_request` ADD FOREIGN KEY (`created_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_ai_request` ADD FOREIGN KEY (`updated_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_ai_request` ADD FOREIGN KEY (`deleted_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_menu` ADD FOREIGN KEY (`store_id`) REFERENCES `p_store` (`id`);

ALTER TABLE `p_menu` ADD FOREIGN KEY (`created_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_menu` ADD FOREIGN KEY (`updated_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_menu` ADD FOREIGN KEY (`deleted_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_image` ADD FOREIGN KEY (`menu_id`) REFERENCES `p_menu` (`id`);

ALTER TABLE `p_image` ADD FOREIGN KEY (`option_id`) REFERENCES `p_menu_option` (`id`);

ALTER TABLE `p_image` ADD FOREIGN KEY (`created_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_image` ADD FOREIGN KEY (`updated_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_image` ADD FOREIGN KEY (`deleted_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_menu_option` ADD FOREIGN KEY (`menu_id`) REFERENCES `p_menu` (`id`);

ALTER TABLE `p_menu_option` ADD FOREIGN KEY (`created_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_menu_option` ADD FOREIGN KEY (`updated_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_menu_option` ADD FOREIGN KEY (`deleted_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_menu_order` ADD FOREIGN KEY (`menu_id`) REFERENCES `p_menu` (`id`);

ALTER TABLE `p_menu_order` ADD FOREIGN KEY (`order_id`) REFERENCES `p_order` (`id`);

ALTER TABLE `p_menu_option_order` ADD FOREIGN KEY (`menu_option_id`) REFERENCES `p_menu_option` (`id`);

ALTER TABLE `p_menu_option_order` ADD FOREIGN KEY (`order_id`) REFERENCES `p_order` (`id`);

ALTER TABLE `p_order` ADD FOREIGN KEY (`customer_name`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_order` ADD FOREIGN KEY (`created_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_order` ADD FOREIGN KEY (`updated_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_order` ADD FOREIGN KEY (`deleted_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_delivery` ADD FOREIGN KEY (`order_id`) REFERENCES `p_order` (`id`);

ALTER TABLE `p_delivery` ADD FOREIGN KEY (`created_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_delivery` ADD FOREIGN KEY (`updated_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_delivery` ADD FOREIGN KEY (`deleted_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_payment` ADD FOREIGN KEY (`order_id`) REFERENCES `p_order` (`id`);

ALTER TABLE `p_payment` ADD FOREIGN KEY (`payment_agent_id`) REFERENCES `p_payment_agent` (`name`);

ALTER TABLE `p_payment` ADD FOREIGN KEY (`created_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_payment` ADD FOREIGN KEY (`updated_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_payment` ADD FOREIGN KEY (`deleted_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_category` ADD FOREIGN KEY (`created_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_category` ADD FOREIGN KEY (`updated_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_category` ADD FOREIGN KEY (`deleted_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_category_store` ADD FOREIGN KEY (`category_id`) REFERENCES `p_category` (`id`);

ALTER TABLE `p_category_store` ADD FOREIGN KEY (`store_id`) REFERENCES `p_store` (`id`);

ALTER TABLE `p_review` ADD FOREIGN KEY (`order_id`) REFERENCES `p_order` (`id`);

ALTER TABLE `p_review` ADD FOREIGN KEY (`created_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_review` ADD FOREIGN KEY (`updated_by`) REFERENCES `p_users` (`username`);

ALTER TABLE `p_review` ADD FOREIGN KEY (`deleted_by`) REFERENCES `p_users` (`username`);