-- LeoneMC Meeting Notes - Database Schema
-- Import via phpMyAdmin or: mysql -u user -p dbname < schema.sql
-- Character set: utf8mb4 for full Unicode support

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS `app_users` (
  `discord_id`          VARCHAR(32)   NOT NULL,
  `username`            VARCHAR(100)  NOT NULL DEFAULT '',
  `role`                ENUM('admin','manager','viewer','pending') NOT NULL DEFAULT 'pending',
  `added_by_discord_id` VARCHAR(32)   NULL DEFAULT NULL,
  `created_at`          DATETIME      NOT NULL,
  `updated_at`          DATETIME      NOT NULL,
  PRIMARY KEY (`discord_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `meetings` (
  `id`                    INT           NOT NULL AUTO_INCREMENT,
  `title`                 VARCHAR(200)  NOT NULL,
  `meeting_date`          DATE          NOT NULL,
  `attendees`             VARCHAR(500)  NOT NULL DEFAULT '',
  `summary`               TEXT          NULL,
  `file_path`             VARCHAR(255)  NOT NULL,
  `original_filename`     VARCHAR(255)  NOT NULL,
  `mime_type`             VARCHAR(100)  NOT NULL DEFAULT '',
  `file_size`             INT           NOT NULL DEFAULT 0,
  `uploaded_by_discord_id` VARCHAR(32)  NOT NULL,
  `created_at`            DATETIME      NOT NULL,
  `is_public`             TINYINT(1)    NOT NULL DEFAULT 0,
  `public_token`          VARCHAR(64)   DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_public_token` (`public_token`),
  KEY `idx_meeting_date` (`meeting_date`),
  KEY `idx_uploaded_by` (`uploaded_by_discord_id`),
  FULLTEXT KEY `ft_search` (`title`, `attendees`, `summary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `meeting_roles` (
  `meeting_id` INT          NOT NULL,
  `role_id`    VARCHAR(32)  NOT NULL,
  PRIMARY KEY (`meeting_id`, `role_id`),
  CONSTRAINT `fk_mr_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `meeting_users` (
  `meeting_id` INT          NOT NULL,
  `user_id`    VARCHAR(32)  NOT NULL,
  PRIMARY KEY (`meeting_id`, `user_id`),
  CONSTRAINT `fk_mu_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `audit_log` (
  `id`               INT          NOT NULL AUTO_INCREMENT,
  `actor_discord_id` VARCHAR(32)  NOT NULL,
  `action`           VARCHAR(40)  NOT NULL,
  `meeting_id`       INT          NULL DEFAULT NULL,
  `detail`           VARCHAR(255) NOT NULL DEFAULT '',
  `created_at`       DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_actor` (`actor_discord_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `meeting_resources` (
  `id`                     INT          NOT NULL AUTO_INCREMENT,
  `meeting_id`             INT          NOT NULL,
  `label`                  VARCHAR(200) NOT NULL DEFAULT '',
  `original_filename`      VARCHAR(255) NOT NULL,
  `file_path`              VARCHAR(255) NOT NULL,
  `mime_type`              VARCHAR(100) NOT NULL DEFAULT '',
  `file_size`              INT          NOT NULL DEFAULT 0,
  `uploaded_by_discord_id` VARCHAR(32)  NOT NULL,
  `created_at`             DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_res_meeting` (`meeting_id`),
  CONSTRAINT `fk_res_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `groups` (
  `id`                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`                  VARCHAR(100) NOT NULL,
  `description`           VARCHAR(500) NOT NULL DEFAULT '',
  `created_by_discord_id` VARCHAR(32)  NOT NULL DEFAULT '',
  `created_at`            DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_group_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `group_members` (
  `group_id` INT UNSIGNED NOT NULL,
  `user_id`  VARCHAR(32)  NOT NULL,
  PRIMARY KEY (`group_id`, `user_id`),
  CONSTRAINT `fk_gm_group` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `meeting_groups` (
  `meeting_id` INT          NOT NULL,
  `group_id`   INT UNSIGNED NOT NULL,
  PRIMARY KEY (`meeting_id`, `group_id`),
  CONSTRAINT `fk_mg_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `meeting_views` (
  `meeting_id`      INT         NOT NULL,
  `user_discord_id` VARCHAR(32) NOT NULL,
  `viewed_at`       DATETIME    NOT NULL,
  PRIMARY KEY (`meeting_id`, `user_discord_id`),
  CONSTRAINT `fk_mv_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;