crm_ecosplay/docker/dovecot/init-esymail.sql

-- Création de la base esymail si elle n'existe pas
SELECT 'CREATE DATABASE esymail OWNER app'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'esymail')\gexec

\connect esymail

-- Table des domaines
CREATE TABLE IF NOT EXISTS domain (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    max_mailboxes INTEGER NOT NULL DEFAULT 50,
    default_quota_mb INTEGER NOT NULL DEFAULT 5120,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_domain_name ON domain (name);
CREATE INDEX IF NOT EXISTS idx_domain_active ON domain (is_active);

-- Table des boites mail
CREATE TABLE IF NOT EXISTS mailbox (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    domain VARCHAR(255) NOT NULL REFERENCES domain(name) ON DELETE CASCADE,
    display_name VARCHAR(255) DEFAULT NULL,
    quota_mb INTEGER NOT NULL DEFAULT 5120,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NULL
);

CREATE INDEX IF NOT EXISTS idx_mailbox_email ON mailbox (email);
CREATE INDEX IF NOT EXISTS idx_mailbox_domain ON mailbox (domain);
CREATE INDEX IF NOT EXISTS idx_mailbox_active ON mailbox (is_active);

-- Table des alias
CREATE TABLE IF NOT EXISTS alias (
    id SERIAL PRIMARY KEY,
    source VARCHAR(255) NOT NULL,
    destination VARCHAR(255) NOT NULL,
    domain VARCHAR(255) NOT NULL REFERENCES domain(name) ON DELETE CASCADE,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE(source, destination)
);

CREATE INDEX IF NOT EXISTS idx_alias_source ON alias (source);
CREATE INDEX IF NOT EXISTS idx_alias_domain ON alias (domain);

-- Domaine de test dev
INSERT INTO domain (name) VALUES ('siteconseil.fr') ON CONFLICT (name) DO NOTHING;
INSERT INTO domain (name) VALUES ('esy-web.dev') ON CONFLICT (name) DO NOTHING;

-- Boite de test dev
INSERT INTO mailbox (email, password, domain, display_name)
VALUES (
    'test@siteconseil.fr',
    '$2y$12$LJ3m4yPnMDCE1xPKm5VwS.YNbKH7JQXZ8VmYD5PJT5dKzJDkPmyG',
    'siteconseil.fr',
    'Compte Test'
) ON CONFLICT (email) DO NOTHING;