-- 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;

-- 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
);
