-- Create tables

SET NAMES utf8mb4;

CREATE TABLE `admin_users`
(
    `admin_id`    int(10) unsigned    NOT NULL AUTO_INCREMENT,
    `username`    varchar(255)        NOT NULL,
    `password`    varchar(255)        NOT NULL,
    `is_active`   tinyint(3) unsigned NOT NULL DEFAULT 1,
    `created_at`  datetime            NOT NULL DEFAULT NOW(),
    `modified_at` datetime            NOT NULL DEFAULT NOW(),
    PRIMARY KEY (`admin_id`),
    UNIQUE KEY `username` (`username`)
) DEFAULT CHARSET = utf8mb4;

-- Create initial admin user for development (password is 'admin')
INSERT INTO `admin_users`
    (`username`, `password`, `is_active`, `created_at`, `modified_at`)
VALUES ('admin', '$2y$10$zaNOBUk4PBlhDZD40h35CeyUxiqixi9LTrxlAxnrckXd95hcCctl6', '1', NOW(), NOW());

CREATE TABLE `mail_users`
(
    `user_id`     int(10) unsigned    NOT NULL AUTO_INCREMENT,
    `username`    varchar(255)        NOT NULL,
    `password`    varchar(255)        NOT NULL,
    `is_active`   tinyint(3) unsigned NOT NULL DEFAULT 1,
    `home_dir`    varchar(255)        NOT NULL,
    `memo`        text                         DEFAULT NULL,
    `created_at`  datetime            NOT NULL DEFAULT NOW(),
    `modified_at` datetime            NOT NULL DEFAULT NOW(),
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `username` (`username`)
) DEFAULT CHARSET = utf8mb4;

CREATE TABLE `mail_aliases`
(
    `alias_id`     int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id`      int(10) unsigned NOT NULL,
    `mail_address` varchar(255)     NOT NULL,
    `created_at`   datetime         NOT NULL DEFAULT NOW(),
    `modified_at`  datetime         NOT NULL DEFAULT NOW(),
    PRIMARY KEY (`alias_id`),
    UNIQUE KEY `mail_address` (`mail_address`),
    KEY `user_id` (`user_id`),
    CONSTRAINT `mail_aliases_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `mail_users` (`user_id`)
) DEFAULT CHARSET = utf8mb4;