-- ============================================================================
-- Vexib Virtual Mailbox Database
-- File: sql/0001_create_vexib_mail.sql
-- Purpose: Create the initial MySQL database and tables for virtual mailboxes.
-- Target: Ubuntu 24.04, MySQL 8, Postfix, Dovecot
-- ============================================================================

CREATE DATABASE IF NOT EXISTS vexib_mail
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE vexib_mail;

-- --------------------------------------------------------------------------
-- Domains that this mail server accepts mail for.
-- Examples:
--   vexib.co.uk
--   photosite.vexib.co.uk
--   albumdb.vexib.co.uk
-- --------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS mail_domains (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    domain_name VARCHAR(255) NOT NULL,
    site_key VARCHAR(100) NULL,
    description VARCHAR(255) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_mail_domains_domain_name (domain_name),
    KEY idx_mail_domains_active (active),
    KEY idx_mail_domains_site_key (site_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Real mailboxes.
-- password_hash should contain a Dovecot-compatible password hash.
-- Recommended initial generation:
--   doveadm pw -s SHA512-CRYPT
-- --------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS mail_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    domain_id INT UNSIGNED NOT NULL,
    email VARCHAR(255) NOT NULL,
    local_part VARCHAR(191) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(255) NULL,
    quota_mb INT UNSIGNED NOT NULL DEFAULT 1024,
    active TINYINT(1) NOT NULL DEFAULT 1,
    force_password_change TINYINT(1) NOT NULL DEFAULT 0,
    last_login_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_mail_users_domain
        FOREIGN KEY (domain_id)
        REFERENCES mail_domains(id)
        ON DELETE CASCADE,

    UNIQUE KEY uq_mail_users_email (email),
    UNIQUE KEY uq_mail_users_domain_local_part (domain_id, local_part),
    KEY idx_mail_users_domain_id (domain_id),
    KEY idx_mail_users_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Aliases / forwards.
-- source_email is the incoming address.
-- destination_email may be another local mailbox or an external address.
-- Multiple rows with the same source_email allow one-to-many forwarding.
-- --------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS mail_aliases (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    domain_id INT UNSIGNED NOT NULL,
    source_email VARCHAR(255) NOT NULL,
    source_local_part VARCHAR(191) NOT NULL,
    destination_email VARCHAR(255) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_mail_aliases_domain
        FOREIGN KEY (domain_id)
        REFERENCES mail_domains(id)
        ON DELETE CASCADE,

    UNIQUE KEY uq_mail_aliases_source_destination (source_email, destination_email),
    KEY idx_mail_aliases_source_email (source_email),
    KEY idx_mail_aliases_domain_id (domain_id),
    KEY idx_mail_aliases_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Audit log for future web administration.
-- This is not required by Postfix/Dovecot but will be useful once the Vexib
-- Mail Admin interface is built.
-- --------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS mail_audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor VARCHAR(255) NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NOT NULL,
    entity_id VARCHAR(100) NULL,
    details JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    KEY idx_mail_audit_log_action (action),
    KEY idx_mail_audit_log_entity (entity_type, entity_id),
    KEY idx_mail_audit_log_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Database user for Postfix/Dovecot lookups.
-- Change the password before running on a real server.
--
-- Recommended:
--   1. Replace CHANGE_THIS_STRONG_PASSWORD before running.
--   2. Store the same password in the Postfix/Dovecot MySQL config files.
-- --------------------------------------------------------------------------

CREATE USER IF NOT EXISTS 'vmail_lookup'@'localhost'
    IDENTIFIED BY 'CHANGE_THIS_STRONG_PASSWORD';

GRANT SELECT ON vexib_mail.mail_domains TO 'vmail_lookup'@'localhost';
GRANT SELECT ON vexib_mail.mail_users TO 'vmail_lookup'@'localhost';
GRANT SELECT ON vexib_mail.mail_aliases TO 'vmail_lookup'@'localhost';

FLUSH PRIVILEGES;

-- --------------------------------------------------------------------------
-- Seed domains.
-- These are safe to insert repeatedly due to ON DUPLICATE KEY UPDATE.
-- --------------------------------------------------------------------------

INSERT INTO mail_domains (domain_name, site_key, description, active)
VALUES
    ('vexib.co.uk', 'vexib', 'Primary Vexib domain', 1),
    ('photosite.vexib.co.uk', 'photosite', 'Photosite application mail domain', 1),
    ('albumdb.vexib.co.uk', 'albumdb', 'AlbumDB application mail domain', 1)
ON DUPLICATE KEY UPDATE
    site_key = VALUES(site_key),
    description = VALUES(description),
    active = VALUES(active),
    updated_at = CURRENT_TIMESTAMP;

-- --------------------------------------------------------------------------
-- Seed mailboxes.
-- IMPORTANT:
-- Replace {SHA512-CRYPT}PLACEHOLDER_CHANGE_ME with real hashes generated by:
--   doveadm pw -s SHA512-CRYPT
-- before using these accounts.
-- --------------------------------------------------------------------------

SET @placeholder_hash = '{SHA512-CRYPT}PLACEHOLDER_CHANGE_ME';

INSERT INTO mail_users (domain_id, email, local_part, password_hash, display_name, quota_mb, active, force_password_change)
SELECT id, 'info@vexib.co.uk', 'info', @placeholder_hash, 'Vexib Information', 1024, 1, 1
FROM mail_domains WHERE domain_name = 'vexib.co.uk'
ON DUPLICATE KEY UPDATE
    display_name = VALUES(display_name),
    quota_mb = VALUES(quota_mb),
    active = VALUES(active),
    force_password_change = VALUES(force_password_change),
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO mail_users (domain_id, email, local_part, password_hash, display_name, quota_mb, active, force_password_change)
SELECT id, 'admin@vexib.co.uk', 'admin', @placeholder_hash, 'Vexib Admin', 1024, 1, 1
FROM mail_domains WHERE domain_name = 'vexib.co.uk'
ON DUPLICATE KEY UPDATE
    display_name = VALUES(display_name),
    quota_mb = VALUES(quota_mb),
    active = VALUES(active),
    force_password_change = VALUES(force_password_change),
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO mail_users (domain_id, email, local_part, password_hash, display_name, quota_mb, active, force_password_change)
SELECT id, 'info@photosite.vexib.co.uk', 'info', @placeholder_hash, 'Photosite Information', 1024, 1, 1
FROM mail_domains WHERE domain_name = 'photosite.vexib.co.uk'
ON DUPLICATE KEY UPDATE
    display_name = VALUES(display_name),
    quota_mb = VALUES(quota_mb),
    active = VALUES(active),
    force_password_change = VALUES(force_password_change),
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO mail_users (domain_id, email, local_part, password_hash, display_name, quota_mb, active, force_password_change)
SELECT id, 'admin@photosite.vexib.co.uk', 'admin', @placeholder_hash, 'Photosite Admin', 1024, 1, 1
FROM mail_domains WHERE domain_name = 'photosite.vexib.co.uk'
ON DUPLICATE KEY UPDATE
    display_name = VALUES(display_name),
    quota_mb = VALUES(quota_mb),
    active = VALUES(active),
    force_password_change = VALUES(force_password_change),
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO mail_users (domain_id, email, local_part, password_hash, display_name, quota_mb, active, force_password_change)
SELECT id, 'noreply@photosite.vexib.co.uk', 'noreply', @placeholder_hash, 'Photosite No Reply', 256, 1, 1
FROM mail_domains WHERE domain_name = 'photosite.vexib.co.uk'
ON DUPLICATE KEY UPDATE
    display_name = VALUES(display_name),
    quota_mb = VALUES(quota_mb),
    active = VALUES(active),
    force_password_change = VALUES(force_password_change),
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO mail_users (domain_id, email, local_part, password_hash, display_name, quota_mb, active, force_password_change)
SELECT id, 'info@albumdb.vexib.co.uk', 'info', @placeholder_hash, 'AlbumDB Information', 1024, 1, 1
FROM mail_domains WHERE domain_name = 'albumdb.vexib.co.uk'
ON DUPLICATE KEY UPDATE
    display_name = VALUES(display_name),
    quota_mb = VALUES(quota_mb),
    active = VALUES(active),
    force_password_change = VALUES(force_password_change),
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO mail_users (domain_id, email, local_part, password_hash, display_name, quota_mb, active, force_password_change)
SELECT id, 'admin@albumdb.vexib.co.uk', 'admin', @placeholder_hash, 'AlbumDB Admin', 1024, 1, 1
FROM mail_domains WHERE domain_name = 'albumdb.vexib.co.uk'
ON DUPLICATE KEY UPDATE
    display_name = VALUES(display_name),
    quota_mb = VALUES(quota_mb),
    active = VALUES(active),
    force_password_change = VALUES(force_password_change),
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO mail_users (domain_id, email, local_part, password_hash, display_name, quota_mb, active, force_password_change)
SELECT id, 'noreply@albumdb.vexib.co.uk', 'noreply', @placeholder_hash, 'AlbumDB No Reply', 256, 1, 1
FROM mail_domains WHERE domain_name = 'albumdb.vexib.co.uk'
ON DUPLICATE KEY UPDATE
    display_name = VALUES(display_name),
    quota_mb = VALUES(quota_mb),
    active = VALUES(active),
    force_password_change = VALUES(force_password_change),
    updated_at = CURRENT_TIMESTAMP;

-- --------------------------------------------------------------------------
-- Optional seed aliases.
-- These can be changed later via the Vexib Mail Admin interface.
-- --------------------------------------------------------------------------

INSERT INTO mail_aliases (domain_id, source_email, source_local_part, destination_email, active)
SELECT id, 'postmaster@vexib.co.uk', 'postmaster', 'admin@vexib.co.uk', 1
FROM mail_domains WHERE domain_name = 'vexib.co.uk'
ON DUPLICATE KEY UPDATE
    active = VALUES(active),
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO mail_aliases (domain_id, source_email, source_local_part, destination_email, active)
SELECT id, 'webmaster@vexib.co.uk', 'webmaster', 'admin@vexib.co.uk', 1
FROM mail_domains WHERE domain_name = 'vexib.co.uk'
ON DUPLICATE KEY UPDATE
    active = VALUES(active),
    updated_at = CURRENT_TIMESTAMP;

-- --------------------------------------------------------------------------
-- Verification queries.
-- --------------------------------------------------------------------------

SELECT 'mail_domains' AS table_name, COUNT(*) AS row_count FROM mail_domains
UNION ALL
SELECT 'mail_users' AS table_name, COUNT(*) AS row_count FROM mail_users
UNION ALL
SELECT 'mail_aliases' AS table_name, COUNT(*) AS row_count FROM mail_aliases;
