CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 login VARCHAR(190) NOT NULL UNIQUE,
 display_name VARCHAR(190) NOT NULL,
 password_hash VARCHAR(255) NOT NULL,
 recovery_email VARCHAR(254) NULL,
 recovery_email_verified_at DATETIME NULL,
 role ENUM('USER','MANAGER','ADMIN') NOT NULL DEFAULT 'USER',
 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_groups (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL UNIQUE,
 description VARCHAR(500) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE group_users (
 group_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(group_id,user_id),
 FOREIGN KEY(group_id) REFERENCES user_groups(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE mailboxes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 email VARCHAR(254) NOT NULL UNIQUE,
 label VARCHAR(190) NOT NULL,
 mailbox_type ENUM('PERSONAL','SHARED') NOT NULL,
 ovh_domain VARCHAR(190) NOT NULL,
 ovh_account VARCHAR(190) NOT NULL,
 owner_user_id BIGINT UNSIGNED NULL,
 encrypted_password TEXT NULL,
 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,
 FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_mailboxes (
 user_id BIGINT UNSIGNED NOT NULL,
 mailbox_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(user_id,mailbox_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE group_mailboxes (
 group_id BIGINT UNSIGNED NOT NULL,
 mailbox_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(group_id,mailbox_id),
 FOREIGN KEY(group_id) REFERENCES user_groups(id) ON DELETE CASCADE,
 FOREIGN KEY(mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE recovery_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 purpose ENUM('PORTAL_PASSWORD_RESET','RECOVERY_EMAIL_VERIFY') NOT NULL,
 token_hash CHAR(64) NOT NULL UNIQUE,
 expires_at DATETIME NOT NULL,
 used_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE webmail_bridge_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 token_hash CHAR(64) NOT NULL UNIQUE,
 user_id BIGINT UNSIGNED NOT NULL,
 mailbox_id BIGINT UNSIGNED NOT NULL,
 expires_at DATETIME NOT NULL,
 used_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE,
 INDEX(expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_log (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 event VARCHAR(100) NOT NULL,
 ip VARCHAR(45) NULL,
 user_agent VARCHAR(255) NULL,
 metadata_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(created_at), INDEX(event), INDEX(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE rate_limit_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 event_key CHAR(64) NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX(event_key,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
