56 lines
2.3 KiB
SQL
56 lines
2.3 KiB
SQL
CREATE TABLE IF NOT EXISTS `settings` (
|
|
`setting_key` VARCHAR(50) PRIMARY KEY,
|
|
`setting_value` TEXT NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES ('google_drive_folder_id', '');
|
|
|
|
CREATE TABLE IF NOT EXISTS `meeting_minutes` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`meeting_date` DATE NOT NULL,
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`details` TEXT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `meeting_attachments` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`meeting_id` INT NOT NULL,
|
|
`file_name` VARCHAR(255) NOT NULL,
|
|
`gdrive_file_id` VARCHAR(255),
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`meeting_id`) REFERENCES `meeting_minutes`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `competitions` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`details` TEXT,
|
|
`rank_result` VARCHAR(50),
|
|
`year_th` INT NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `competition_authors` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`competition_id` INT NOT NULL,
|
|
`author_name` VARCHAR(255) NOT NULL,
|
|
FOREIGN KEY (`competition_id`) REFERENCES `competitions`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `competition_attachments` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`competition_id` INT NOT NULL,
|
|
`file_name` VARCHAR(255) NOT NULL,
|
|
`gdrive_file_id` VARCHAR(255),
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`competition_id`) REFERENCES `competitions`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `activity_logs` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`action_type` VARCHAR(50) NOT NULL,
|
|
`action_details` TEXT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|