-- kiri-mail-server 数据库初始化脚本
-- 运行: mysql -u root -p < migrations/001_init.sql

CREATE TABLE IF NOT EXISTS mail_domains (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    domain      VARCHAR(255) NOT NULL UNIQUE,
    description VARCHAR(500) DEFAULT '',
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    max_users   INT NOT NULL DEFAULT 100,
    max_quota   BIGINT NOT NULL DEFAULT 0 COMMENT '0=unlimited, bytes',
    created_at  INT NOT NULL,
    updated_at  INT NOT NULL,
    INDEX idx_domain (domain),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mail_users (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(320) NOT NULL UNIQUE,
    domain_id   INT NOT NULL,
    local_part  VARCHAR(128) NOT NULL,
    password    VARCHAR(255) NOT NULL COMMENT 'bcrypt/argon2 hashed',
    display_name VARCHAR(255) DEFAULT '',
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    quota       BIGINT NOT NULL DEFAULT 0 COMMENT '0=unlimited, bytes',
    created_at  INT NOT NULL,
    updated_at  INT NOT NULL,
    INDEX idx_email (email),
    INDEX idx_domain_id (domain_id),
    FOREIGN KEY (domain_id) REFERENCES mail_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mail_aliases (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    source_email    VARCHAR(320) NOT NULL,
    destination_email VARCHAR(320) NOT NULL,
    domain_id       INT NOT NULL,
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    created_at      INT NOT NULL,
    updated_at      INT NOT NULL,
    INDEX idx_source (source_email),
    INDEX idx_domain (domain_id),
    FOREIGN KEY (domain_id) REFERENCES mail_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mail_quotas (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL UNIQUE,
    used_bytes  BIGINT NOT NULL DEFAULT 0,
    message_count INT NOT NULL DEFAULT 0,
    updated_at  INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES mail_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;