CREATE DATABASE sisai_world_db default character set utf8mb4;
use sisai_world_db;

CREATE TABLE `accounts` (
  `account_id` int unsigned NOT NULL AUTO_INCREMENT,
  `account_email` varchar(320) NOT NULL,
  `account_pw` varchar(100) NOT NULL,
  `account_name` varchar(512), -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`account_id`),
  KEY `accounts_IDX1` (`created`),
  KEY `accounts_IDX2` (`deleted`),
  KEY `accounts_IDX3` (`account_email`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='accounts';

CREATE TABLE `registration_sessions` (
  `session_id` char(40) NOT NULL,
  `username` varchar(320) NOT NULL, -- can save email addresses
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`session_id`),
  KEY `registration_sessions_IDX1` (`created`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='registration sessions';

CREATE TABLE `space_templates` (
  `spacetemp_id` int unsigned NOT NULL AUTO_INCREMENT,
  `spacetemp_name` varchar(512) NOT NULL, -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `spacetemp_intro` varchar(1024) NOT NULL, -- JSON string, { "ja": "日本語", "en-US": "English" }...　vector search in the future
  `account_id` int unsigned NOT NULL, -- creator
  `shared` TINYINT default 0, -- 1: shared, 0: private
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`spacetemp_id`),
  KEY `space_templates_IDX1` (`spacetemp_name`),
  KEY `space_templates_IDX2` (`created`),
  KEY `space_templates_IDX3` (`deleted`),
  KEY `space_templates_IDX4` (`shared`),
  FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='space templates';

CREATE TABLE `spacetplt_info_blocks` (
  `info_block_id` int unsigned NOT NULL AUTO_INCREMENT,
  `spacetemp_id` int unsigned NOT NULL,
  `block_name` varchar(512) NOT NULL, -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `block_cmmt` varchar(1024), -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`info_block_id`),
  KEY `spacetplt_info_blocks_IDX1` (`created`),
  KEY `spacetplt_info_blocks_IDX2` (`deleted`),
  FOREIGN KEY (spacetemp_id) REFERENCES space_templates(spacetemp_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='space template info blocks';

CREATE TABLE `spacetplt_prompts` (
  `prompt_id` int unsigned NOT NULL AUTO_INCREMENT,
  `spacetemp_id` int unsigned NOT NULL,
  `prompt_head` varchar(1024), -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `prompt_hint` varchar(1024), -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `prompt_show` varchar(1024) NOT NULL, -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `prompt_foot` varchar(1024), -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`prompt_id`),
  KEY `spacetplt_info_blocks_IDX1` (`created`),
  KEY `spacetplt_info_blocks_IDX2` (`deleted`),
  FOREIGN KEY (spacetemp_id) REFERENCES space_templates(spacetemp_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='space template prompts';

CREATE TABLE `spaces` (
  `space_id` int unsigned NOT NULL AUTO_INCREMENT,
  `spacetemp_id` int unsigned NOT NULL,
  `space_name` varchar(512) NOT NULL, -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`space_id`),
  KEY `spaces_IDX1` (`created`),
  KEY `spaces_IDX2` (`deleted`),
  FOREIGN KEY (spacetemp_id) REFERENCES space_templates(spacetemp_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='spaces';

CREATE TABLE `account_permission` (
  `accpermission_id` int unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int unsigned NOT NULL,
  `space_id` int unsigned NOT NULL,
  `is_admin` bit(1) default 0, -- admin can delete space
  `permission` varchar(512), -- JSON string, { "info_id1": "0", "info_id2": "2" }...; 0: deny 1: view 2: read/write; info_id  is id in space template
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`accpermission_id`),
  KEY `account_permission_IDX1` (`created`),
  KEY `account_permission_IDX2` (`deleted`),
  FOREIGN KEY (space_id) REFERENCES spaces(space_id),
  FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='account permission';

CREATE TABLE `accspace_info` (
  `accspace_info_id` int unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int unsigned NOT NULL,
  `space_id` int unsigned NOT NULL,
  `info_id` varchar(512) NOT NULL, -- info_blocks ids of space templates
  `info` text NOT NULL, -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`accspace_info_id`),
  KEY `accspace_info_IDX1` (`created`),
  KEY `accspace_info_IDX2` (`deleted`),
  KEY `accspace_info_IDX3` (`account_id`, `space_id`),
  KEY `accspace_info_IDX4` (`info_id`),
  FOREIGN KEY (account_id) REFERENCES accounts(account_id),
  FOREIGN KEY (space_id) REFERENCES spaces(space_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='accspace_info';

CREATE TABLE `accspace_prompts` (
  `accspace_prompts_id` int unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int unsigned NOT NULL,
  `space_id` int unsigned NOT NULL,
  `prompt` text NOT NULL, -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`accspace_prompts_id`),
  KEY `accspace_prompts_IDX1` (`created`),
  KEY `accspace_prompts_IDX2` (`deleted`),
  KEY `accspace_prompts_IDX3` (`account_id`, `space_id`),
  FOREIGN KEY (account_id) REFERENCES accounts(account_id),
  FOREIGN KEY (space_id) REFERENCES spaces(space_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='account space prompts';

CREATE TABLE `accspace_sessions` (
  `accspace_sessions_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int unsigned NOT NULL,
  `space_id` int unsigned NOT NULL,
  `short_name` varchar(512) NOT NULL, -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `updated` datetime DEFAULT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`accspace_sessions_id`),
  KEY `accspace_sessions_IDX1` (`created`),
  KEY `accspace_sessions_IDX2` (`deleted`),
  KEY `accspace_sessions_IDX3` (`account_id`, `space_id`),
  KEY `accspace_sessions_IDX4` (`updated`),
  FOREIGN KEY (account_id) REFERENCES accounts(account_id),
  FOREIGN KEY (space_id) REFERENCES spaces(space_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='account space sessions';

CREATE TABLE `accspace_history` (
  `accspace_history_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int unsigned NOT NULL,
  `space_id` int unsigned NOT NULL,
  `accspace_sessions_id` bigint unsigned NOT NULL,
  `prompt` text NOT NULL, -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `answer` text NOT NULL, -- JSON string, { "ja": "日本語", "en-US": "English" }...
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`accspace_history_id`),
  KEY `accspace_history_IDX1` (`created`),
  KEY `accspace_history_IDX2` (`deleted`),
  KEY `accspace_history_IDX3` (`account_id`, `space_id`, `accspace_sessions_id`),
  FOREIGN KEY (accspace_sessions_id) REFERENCES accspace_sessions(accspace_sessions_id),
  FOREIGN KEY (account_id) REFERENCES accounts(account_id),
  FOREIGN KEY (space_id) REFERENCES spaces(space_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='account space history';

CREATE TABLE `invite_user_sessions` (
    `session_id` char(40) NOT NULL,
    `sender_id` int unsigned NOT NULL,
    `user_email` varchar(320) NOT NULL,
    `role` tinyint DEFAULT 0, -- 0: Normal user, 1: Mgr
    `space_id` int unsigned NOT NULL,
    `created` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`session_id`),
    KEY `invite_user_sessions_IDX1` (`created`),
    FOREIGN KEY (sender_id) REFERENCES accounts(account_id),
    FOREIGN KEY (space_id) REFERENCES spaces(space_id)
) ENGINE=InnoDB  DEFAULT CHARSET=ascii COMMENT='invite user sessions';

CREATE TABLE `space_users` (
  `spcuser_id` int unsigned NOT NULL AUTO_INCREMENT,
  `space_id` int unsigned NOT NULL,
  `account_id` int unsigned NOT NULL,
  `role` tinyint DEFAULT 0, -- 0: Normal user, 1: Mgr
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`spcuser_id`),
  KEY `spcusers_IDX1` (`space_id`),
  KEY `spcusers_IDX2` (`created`),
  KEY `spcusers_IDX3` (`deleted`),
  FOREIGN KEY (account_id) REFERENCES accounts(account_id),
  FOREIGN KEY (space_id) REFERENCES spaces(space_id),
  UNIQUE KEY `spcusers_IDX4` (`space_id`, `account_id`, `deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='space users';

-- init data

INSERT INTO `accounts` (`account_id`, `account_email`, `account_pw`) VALUES
(5, 'du@amipro.me', 'wuxiao1998');

INSERT INTO `space_templates` (`spacetemp_id`, `spacetemp_name`, `shared`, `account_id`, `spacetemp_intro`) VALUES
(5, '{"ja": "会社経営", "en-US": "Running Business"}', 1, 5, '{"ja": "小規模会社経営用スペース", "en-US": "Space for running small business"}');

INSERT INTO `spacetplt_info_blocks` (`info_block_id`, `spacetemp_id`, `block_name`) VALUES
(5, 5, '{"ja": "会社情報", "en-US": "Company Info"}'),
(6, 5, '{"ja": "従業員情報", "en-US": "Employee Info"}'),
(7, 5, '{"ja": "商品情報", "en-US": "Product Info"}'),
(8, 5, '{"ja": "顧客情報", "en-US": "Customer Info"}'),
(9, 5, '{"ja": "取引情報", "en-US": "Transaction Info"}');

update spacetplt_info_blocks set block_cmmt = '{"ja": "会社全体の情報、他人に説明する時のオーバービューの様な情報", "en-US": "Company-wide information, overview information for explaining to others"}' where info_block_id = 5;

INSERT INTO `spacetplt_prompts` (`prompt_id`, `spacetemp_id`, `prompt_head`, `prompt_hint`, `prompt_show`, `prompt_foot`) VALUES
(5, 5, '{"ja": "会社の経営者として", "en-US": "As a company executive"}', '{"ja": "質問の５０％を適当の数字へ変更してください", "en-US": "Change 50% of the question to an appropriate number"}', '{"ja": "利益を５０％増加できるために従業員が何人を増加雇用すべき？", "en-US": "How many more employees should be hired to increase profits by 50%?"}', '{"ja": "テーブルの形で以上の問題を回答して", "en-US": "Answer the above question in table form"}');

INSERT INTO `spacetplt_prompts` (`prompt_id`, `spacetemp_id`, `prompt_show`) VALUES
(6, 5, '{"ja": "セールス一人毎の平均売上は？", "en-US": "What is the average sale per salesperson?"}'),
(7, 5, '{"ja": "2024年５月の売上は", "en-US": "Sales in May 2024"}'),
(8, 5, '{"ja": "毎月レストランの売上比率", "en-US": "Monthly restaurant sales ratio"}');