[Aqqle] SQL

-- shop.stock definition

CREATE TABLE `goods_tmon` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `keyword` varchar(200) DEFAULT NULL,
  `name` varchar(500) NOT NULL,
  `price` bigint NOT NULL,
  `weight` float NOT NULL,
  `popular` float NOT NULL,
  `image` varchar(400) DEFAULT NULL,
  `feature_vector` text NOT NULL,
  `type` varchar(50) NOT NULL,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `updated_time_index` (`updated_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci



CREATE TABLE `members` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` varchar(200) NOT NULL,
  `name` varchar(200) NOT NULL,
  `use_yn` enum('Y','N') NOT NULL DEFAULT 'Y',
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` varchar(200) NOT NULL,
  `name` varchar(200) NOT NULL,
  `use_yn` enum('Y','N') NOT NULL DEFAULT 'Y',
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


CREATE TABLE `keywords` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `keyword` varchar(200) NOT NULL,
  `use_yn` enum('Y','N') NOT NULL DEFAULT 'Y',
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `keyword_index` (`keyword`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



CREATE TABLE `aqqle_goods` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `keyword` varchar(200) DEFAULT NULL,
  `brand` varchar(500) DEFAULT NULL,
  `category` varchar(2000) DEFAULT NULL,
  `category1` varchar(200) DEFAULT NULL,
  `category2` varchar(200) DEFAULT NULL,
  `category3` varchar(200) DEFAULT NULL,
  `category4` varchar(200) DEFAULT NULL,
  `category5` varchar(200) DEFAULT NULL,
  `name` varchar(500) NOT NULL,
  `price` bigint NOT NULL,
  `weight` float NOT NULL,
  `popular` float NOT NULL,
  `image` varchar(400) DEFAULT NULL,
  `feature_vector` text NOT NULL,
  `type` varchar(50) NOT NULL,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `updated_time_index` (`updated_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `investment` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company` varchar(200) NOT NULL,
  `exchange` varchar(200) NOT NULL,
  `type` varchar(20) NOT NULL,
  `price` bigint NOT NULL default 0,
  `quantity` bigint NOT NULL default 0,  
  `use_yn` enum('Y','N') NOT NULL DEFAULT 'Y',
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_index` (`company`,`exchange` )
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- TRUNCATE table stock 
CREATE TABLE `stock` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company` varchar(100) NOT NULL,
  `company_code` varchar(20) NOT NULL,
  `exchange` varchar(30) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,    
  `type` varchar(20) NOT NULL,
  `use_yn` enum('Y','N') NOT NULL DEFAULT 'Y',
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_code_index` (`company_code` )
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `stock_data` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company` varchar(100) NOT NULL,
  `company_code` varchar(20) NOT NULL,
  `trading_date` date NOT NULL,
  `open` float NULL,
  `high` float NULL,
  `low` float NULL,
  `close` float NULL,
  `adj_close` float NULL,
  `volume` bigint NULL,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`company_code`, `trading_date`),
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;




CREATE TABLE `stock_data_partition` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company` varchar(100) NOT NULL,
  `company_code` varchar(20) NOT NULL,
  `trading_date` date NOT NULL,
  `open` float DEFAULT NULL,
  `high` float DEFAULT NULL,
  `low` float DEFAULT NULL,
  `close` float DEFAULT NULL,
  `adj_close` float DEFAULT NULL,
  `volume` bigint DEFAULT NULL,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`company_code`,`trading_date`),
  UNIQUE KEY `unique_id` (`id`, `trading_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (YEAR(trading_date)) (
    PARTITION p0 VALUES LESS THAN (2021),
    PARTITION p1 VALUES LESS THAN (2022),
    PARTITION p2 VALUES LESS THAN (2023),
    PARTITION p3 VALUES LESS THAN (2024),
    PARTITION p4 VALUES LESS THAN (2025),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
);