-- ============================================================
-- vibration_drive – Database Migrations
-- All tables prefixed with vibration_
-- Engine: InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- ============================================================


-- ------------------------------------------------------------
-- 1. vibration_admin_user
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_admin_user` (
    `id`              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `email`           VARCHAR(255)    NOT NULL UNIQUE,
    `password_hash`   VARCHAR(255)    NOT NULL,
    `display_name`    VARCHAR(100)    NOT NULL DEFAULT '',
    `is_active`       TINYINT(1)      NOT NULL DEFAULT 1,
    `last_login_at`   DATETIME        NULL,
    `login_attempts`  TINYINT         NOT NULL DEFAULT 0,
    `locked_until`    DATETIME        NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. vibration_admin_session
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_admin_session` (
    `id`            VARCHAR(128)    NOT NULL,
    `admin_id`      INT UNSIGNED    NOT NULL,
    `ip_address`    VARCHAR(45)     NOT NULL,
    `user_agent`    TEXT            NULL,
    `payload`       MEDIUMTEXT      NULL,
    `expires_at`    DATETIME        NOT NULL,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_admin_session_admin` (`admin_id`),
    CONSTRAINT `fk_admin_session_user` FOREIGN KEY (`admin_id`) REFERENCES `vibration_admin_user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. vibration_workspace
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_workspace` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `public_token`  VARCHAR(64)     NOT NULL UNIQUE,
    `name`          VARCHAR(255)    NOT NULL,
    `description`   TEXT            NULL,
    `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_workspace_token` (`public_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. vibration_workspace_settings
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_workspace_settings` (
    `id`                            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `workspace_id`                  INT UNSIGNED    NOT NULL UNIQUE,
    -- Quotas (0 = illimité)
    `max_file_size_bytes`           BIGINT UNSIGNED NOT NULL DEFAULT 0,             -- 0 = no limit
    `max_storage_bytes`             BIGINT UNSIGNED NOT NULL DEFAULT 0,             -- 0 = no limit
    `daily_upload_limit_per_ip`     BIGINT UNSIGNED NOT NULL DEFAULT 0,             -- 0 = no limit
    `uploads_per_minute_per_ip`     INT UNSIGNED    NOT NULL DEFAULT 10,
    `chunk_size_bytes`              INT UNSIGNED    NOT NULL DEFAULT 10485760,       -- 10 MB
    -- Security
    `allowed_extensions`            TEXT            NULL COMMENT 'JSON array, NULL = use defaults',
    `require_scan_before_visible`   TINYINT(1)      NOT NULL DEFAULT 1,
    `scan_policy`                   ENUM('auto','manual') NOT NULL DEFAULT 'manual',
    `captcha_enabled`               TINYINT(1)      NOT NULL DEFAULT 0,
    -- Public permissions
    `allow_public_upload`           TINYINT(1)      NOT NULL DEFAULT 1,
    `allow_public_tag_assign`       TINYINT(1)      NOT NULL DEFAULT 0,
    `allow_public_folder_create`    TINYINT(1)      NOT NULL DEFAULT 0,
    `updated_at`                    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ws_settings_workspace` FOREIGN KEY (`workspace_id`) REFERENCES `vibration_workspace`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. vibration_folder
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_folder` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `workspace_id`  INT UNSIGNED    NOT NULL,
    `parent_id`     INT UNSIGNED    NULL DEFAULT NULL,
    `name`          VARCHAR(255)    NOT NULL,
    `slug`          VARCHAR(255)    NOT NULL,
    `description`   TEXT            NULL,
    `sort_order`    INT             NOT NULL DEFAULT 0,
    `is_deleted`    TINYINT(1)      NOT NULL DEFAULT 0,
    `deleted_at`    DATETIME        NULL,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_folder_workspace` (`workspace_id`),
    INDEX `idx_folder_parent` (`parent_id`),
    INDEX `idx_folder_slug` (`workspace_id`, `slug`),
    CONSTRAINT `fk_folder_workspace` FOREIGN KEY (`workspace_id`) REFERENCES `vibration_workspace`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_folder_parent` FOREIGN KEY (`parent_id`) REFERENCES `vibration_folder`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 6. vibration_file
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_file` (
    `id`                INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `uuid`              CHAR(36)        NOT NULL UNIQUE,
    `workspace_id`      INT UNSIGNED    NOT NULL,
    `folder_id`         INT UNSIGNED    NULL DEFAULT NULL,
    `original_name`     VARCHAR(500)    NOT NULL,
    `stored_name`       VARCHAR(255)    NOT NULL COMMENT 'UUID-based filename on disk',
    `extension`         VARCHAR(20)     NOT NULL,
    `mime_type`         VARCHAR(127)    NOT NULL,
    `size_bytes`        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `sha256`            CHAR(64)        NULL,
    `title`             VARCHAR(500)    NULL,
    `description`       TEXT            NULL,
    `scan_status`       ENUM('pending_scan','clean','quarantined','rejected') NOT NULL DEFAULT 'pending_scan',
    `scan_note`         TEXT            NULL,
    `scanned_at`        DATETIME        NULL,
    `download_count`    INT UNSIGNED    NOT NULL DEFAULT 0,
    `preview_count`     INT UNSIGNED    NOT NULL DEFAULT 0,
    `upload_ip`         VARCHAR(45)     NULL,
    `uploader_ua`       TEXT            NULL,
    `version`           SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    `is_deleted`        TINYINT(1)      NOT NULL DEFAULT 0,
    `deleted_at`        DATETIME        NULL,
    `created_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_file_uuid` (`uuid`),
    INDEX `idx_file_workspace` (`workspace_id`),
    INDEX `idx_file_folder` (`folder_id`),
    INDEX `idx_file_scan` (`scan_status`),
    INDEX `idx_file_deleted` (`is_deleted`),
    FULLTEXT INDEX `ft_file_search` (`original_name`, `title`, `description`),
    CONSTRAINT `fk_file_workspace` FOREIGN KEY (`workspace_id`) REFERENCES `vibration_workspace`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_file_folder` FOREIGN KEY (`folder_id`) REFERENCES `vibration_folder`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. vibration_file_version
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_file_version` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `file_id`       INT UNSIGNED    NOT NULL,
    `version`       SMALLINT UNSIGNED NOT NULL,
    `stored_name`   VARCHAR(255)    NOT NULL,
    `original_name` VARCHAR(500)    NOT NULL,
    `size_bytes`    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `sha256`        CHAR(64)        NULL,
    `mime_type`     VARCHAR(127)    NOT NULL,
    `upload_ip`     VARCHAR(45)     NULL,
    `note`          TEXT            NULL,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_fv_file` (`file_id`),
    UNIQUE KEY `uq_fv_file_version` (`file_id`, `version`),
    CONSTRAINT `fk_fv_file` FOREIGN KEY (`file_id`) REFERENCES `vibration_file`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. vibration_upload_session
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_upload_session` (
    `id`                VARCHAR(64)     NOT NULL,
    `workspace_id`      INT UNSIGNED    NOT NULL,
    `original_name`     VARCHAR(500)    NOT NULL,
    `extension`         VARCHAR(20)     NOT NULL,
    `total_size`        BIGINT UNSIGNED NOT NULL,
    `total_chunks`      INT UNSIGNED    NOT NULL,
    `received_chunks`   INT UNSIGNED    NOT NULL DEFAULT 0,
    `received_bytes`    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `chunk_size`        INT UNSIGNED    NOT NULL DEFAULT 10485760,
    `folder_id`         INT UNSIGNED    NULL,
    `upload_ip`         VARCHAR(45)     NULL,
    `uploader_ua`       TEXT            NULL,
    `status`            ENUM('active','assembling','complete','failed','expired') NOT NULL DEFAULT 'active',
    `file_id`           INT UNSIGNED    NULL COMMENT 'Set after assembly',
    `expires_at`        DATETIME        NOT NULL,
    `created_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_us_workspace` (`workspace_id`),
    INDEX `idx_us_status` (`status`),
    CONSTRAINT `fk_us_workspace` FOREIGN KEY (`workspace_id`) REFERENCES `vibration_workspace`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 9. vibration_upload_chunk
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_upload_chunk` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `session_id`    VARCHAR(64)     NOT NULL,
    `chunk_index`   INT UNSIGNED    NOT NULL,
    `size_bytes`    INT UNSIGNED    NOT NULL,
    `stored_path`   VARCHAR(500)    NOT NULL,
    `received_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_chunk_session_idx` (`session_id`, `chunk_index`),
    CONSTRAINT `fk_chunk_session` FOREIGN KEY (`session_id`) REFERENCES `vibration_upload_session`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 10. vibration_tag
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_tag` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `workspace_id`  INT UNSIGNED    NOT NULL,
    `name`          VARCHAR(100)    NOT NULL,
    `slug`          VARCHAR(120)    NOT NULL,
    `color`         VARCHAR(7)      NOT NULL DEFAULT '#6366f1',
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_tag_workspace_slug` (`workspace_id`, `slug`),
    CONSTRAINT `fk_tag_workspace` FOREIGN KEY (`workspace_id`) REFERENCES `vibration_workspace`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 11. vibration_file_tag
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_file_tag` (
    `file_id`   INT UNSIGNED    NOT NULL,
    `tag_id`    INT UNSIGNED    NOT NULL,
    PRIMARY KEY (`file_id`, `tag_id`),
    CONSTRAINT `fk_ft_file` FOREIGN KEY (`file_id`) REFERENCES `vibration_file`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ft_tag`  FOREIGN KEY (`tag_id`)  REFERENCES `vibration_tag`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 12. vibration_category
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_category` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `workspace_id`  INT UNSIGNED    NOT NULL,
    `name`          VARCHAR(150)    NOT NULL,
    `slug`          VARCHAR(170)    NOT NULL,
    `description`   TEXT            NULL,
    `color`         VARCHAR(7)      NOT NULL DEFAULT '#10b981',
    `sort_order`    INT             NOT NULL DEFAULT 0,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_cat_workspace_slug` (`workspace_id`, `slug`),
    CONSTRAINT `fk_cat_workspace` FOREIGN KEY (`workspace_id`) REFERENCES `vibration_workspace`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 13. vibration_file_category
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_file_category` (
    `file_id`       INT UNSIGNED    NOT NULL,
    `category_id`   INT UNSIGNED    NOT NULL,
    PRIMARY KEY (`file_id`, `category_id`),
    CONSTRAINT `fk_fc_file` FOREIGN KEY (`file_id`)     REFERENCES `vibration_file`(`id`)     ON DELETE CASCADE,
    CONSTRAINT `fk_fc_cat`  FOREIGN KEY (`category_id`) REFERENCES `vibration_category`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 14. vibration_article
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_article` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `workspace_id`  INT UNSIGNED    NOT NULL,
    `slug`          VARCHAR(300)    NOT NULL,
    `title`         VARCHAR(500)    NOT NULL,
    `excerpt`       TEXT            NULL,
    `content`       LONGTEXT        NOT NULL,
    `is_published`  TINYINT(1)      NOT NULL DEFAULT 0,
    `published_at`  DATETIME        NULL,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_article_ws_slug` (`workspace_id`, `slug`),
    INDEX `idx_article_workspace` (`workspace_id`),
    FULLTEXT INDEX `ft_article_search` (`title`, `content`, `excerpt`),
    CONSTRAINT `fk_article_workspace` FOREIGN KEY (`workspace_id`) REFERENCES `vibration_workspace`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 15. vibration_article_file
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_article_file` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `article_id`    INT UNSIGNED    NOT NULL,
    `file_id`       INT UNSIGNED    NOT NULL,
    `role`          ENUM('primary','attachment','reference') NOT NULL DEFAULT 'attachment',
    `sort_order`    INT             NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_af_article_file` (`article_id`, `file_id`),
    CONSTRAINT `fk_af_article` FOREIGN KEY (`article_id`) REFERENCES `vibration_article`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_af_file`    FOREIGN KEY (`file_id`)    REFERENCES `vibration_file`(`id`)    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 16. vibration_media
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_media` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `workspace_id`  INT UNSIGNED    NOT NULL,
    `title`         VARCHAR(500)    NOT NULL,
    `type`          ENUM('audio','video') NOT NULL,
    `source`        ENUM('local','external') NOT NULL DEFAULT 'local',
    `file_id`       INT UNSIGNED    NULL COMMENT 'For local source',
    `external_url`  TEXT            NULL COMMENT 'For external source',
    `description`   TEXT            NULL,
    `sort_order`    INT             NOT NULL DEFAULT 0,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_media_workspace` (`workspace_id`),
    CONSTRAINT `fk_media_workspace` FOREIGN KEY (`workspace_id`) REFERENCES `vibration_workspace`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_media_file`      FOREIGN KEY (`file_id`)      REFERENCES `vibration_file`(`id`)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 17. vibration_transcription
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_transcription` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `media_id`      INT UNSIGNED    NOT NULL,
    `language`      VARCHAR(10)     NOT NULL DEFAULT 'fr',
    `format`        ENUM('plain','vtt','srt') NOT NULL DEFAULT 'plain',
    `content`       LONGTEXT        NOT NULL,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_trans_media` (`media_id`),
    FULLTEXT INDEX `ft_trans_content` (`content`),
    CONSTRAINT `fk_trans_media` FOREIGN KEY (`media_id`) REFERENCES `vibration_media`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 18. vibration_audit_log
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_audit_log` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id`  INT UNSIGNED    NULL,
    `event_type`    VARCHAR(60)     NOT NULL,
    `object_type`   VARCHAR(40)     NULL,
    `object_id`     VARCHAR(36)     NULL,
    `actor_type`    ENUM('public','admin') NOT NULL DEFAULT 'public',
    `actor_id`      INT UNSIGNED    NULL,
    `ip_address`    VARCHAR(45)     NULL,
    `user_agent`    TEXT            NULL,
    `metadata_json` JSON            NULL,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_al_workspace` (`workspace_id`),
    INDEX `idx_al_event`     (`event_type`),
    INDEX `idx_al_object`    (`object_type`, `object_id`),
    INDEX `idx_al_created`   (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 19. vibration_security_event
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_security_event` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id`  INT UNSIGNED    NULL,
    `event_type`    VARCHAR(60)     NOT NULL,
    `severity`      ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
    `ip_address`    VARCHAR(45)     NULL,
    `user_agent`    TEXT            NULL,
    `detail`        TEXT            NULL,
    `metadata_json` JSON            NULL,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_se_workspace` (`workspace_id`),
    INDEX `idx_se_event`     (`event_type`),
    INDEX `idx_se_severity`  (`severity`),
    INDEX `idx_se_ip`        (`ip_address`),
    INDEX `idx_se_created`   (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 20. vibration_download_log
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_download_log` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `file_id`       INT UNSIGNED    NOT NULL,
    `workspace_id`  INT UNSIGNED    NOT NULL,
    `ip_address`    VARCHAR(45)     NULL,
    `user_agent`    TEXT            NULL,
    `bytes_sent`    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `is_preview`    TINYINT(1)      NOT NULL DEFAULT 0,
    `range_request` TINYINT(1)      NOT NULL DEFAULT 0,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_dl_file`      (`file_id`),
    INDEX `idx_dl_workspace` (`workspace_id`),
    INDEX `idx_dl_ip`        (`ip_address`),
    INDEX `idx_dl_created`   (`created_at`),
    CONSTRAINT `fk_dl_file`  FOREIGN KEY (`file_id`) REFERENCES `vibration_file`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 21. vibration_rate_limit (IP rate limiting)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vibration_rate_limit` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ip_address`    VARCHAR(45)     NOT NULL,
    `action`        VARCHAR(40)     NOT NULL,
    `workspace_id`  INT UNSIGNED    NULL,
    `count`         INT UNSIGNED    NOT NULL DEFAULT 1,
    `window_start`  DATETIME        NOT NULL,
    `bytes_today`   BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `date_today`    DATE            NOT NULL,
    `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_rl_ip_action_ws_window` (`ip_address`, `action`, `workspace_id`, `window_start`),
    INDEX `idx_rl_ip_date` (`ip_address`, `date_today`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- Initial data
-- ============================================================

-- Default workspace (token must be rotated via admin)
INSERT INTO `vibration_workspace` (`public_token`, `name`, `description`) VALUES
('CHANGE_THIS_TOKEN_32CHARS_MINIMUM', 'Workspace Principal', 'Espace de partage principal');

-- Default settings for workspace 1
INSERT INTO `vibration_workspace_settings` (`workspace_id`) VALUES (1);
