-- Run once: mysql -u root -p < schema.sql

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

USE email_hygiene;

-- Users table with role column
CREATE TABLE IF NOT EXISTS users (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(120)            NOT NULL,
    email      VARCHAR(255)            NOT NULL UNIQUE,
    password   VARCHAR(255)            NOT NULL,
    role       ENUM('admin', 'user')   NOT NULL DEFAULT 'user',
    created_at DATETIME                DEFAULT CURRENT_TIMESTAMP
);

-- Upload history table
CREATE TABLE IF NOT EXISTS upload_history (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    user_id          INT          NOT NULL,
    filename         VARCHAR(255) NOT NULL,
    total_emails     INT          DEFAULT 0,
    safe_count       INT          DEFAULT 0,
    valid_count      INT          DEFAULT 0,
    risky_count      INT          DEFAULT 0,
    disposable_count INT          DEFAULT 0,
    spam_trap_count  INT          DEFAULT 0,
    invalid_count    INT          DEFAULT 0,
    uploaded_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create a default admin account
-- Password: Admin@1234  (change after first login)
INSERT IGNORE INTO users (name, email, password, role)
VALUES (
    'Admin',
    'admin@example.com',
    'pbkdf2:sha256:600000$rJ8KpVQz$e3b7c4a2d1f6e8a9b0c3d5e7f2a4b6c8d0e2f4a6b8c0d2e4f6a8b0c2d4e6f8a0',
    'admin'
);