CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('super','admin') NOT NULL DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE scratchcards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(190) NOT NULL,
    slug VARCHAR(190) NOT NULL UNIQUE,
    public_url VARCHAR(255) NOT NULL,
    background_image VARCHAR(255) DEFAULT NULL,
    background_width INT DEFAULT NULL,
    background_height INT DEFAULT NULL,
    overlay_image VARCHAR(255) DEFAULT NULL,
    lose_image_path VARCHAR(255) DEFAULT NULL,
    reveal_percent INT DEFAULT 35,
    collect_player_details TINYINT(1) NOT NULL DEFAULT 0,
    require_gdpr_consent TINYINT(1) NOT NULL DEFAULT 0,
    send_result_email TINYINT(1) NOT NULL DEFAULT 0,
    email_from_name VARCHAR(150) DEFAULT NULL,
    email_from_address VARCHAR(190) DEFAULT NULL,
    email_reply_to VARCHAR(190) DEFAULT NULL,
    email_subject VARCHAR(190) DEFAULT NULL,
    email_body_html MEDIUMTEXT DEFAULT NULL,
    email_header_html MEDIUMTEXT DEFAULT NULL,
    email_footer_html MEDIUMTEXT DEFAULT NULL,
    email_header_image VARCHAR(255) DEFAULT NULL,
    email_footer_image VARCHAR(255) DEFAULT NULL,
    scratch_x INT DEFAULT NULL,
    scratch_y INT DEFAULT NULL,
    scratch_width INT DEFAULT NULL,
    scratch_height INT DEFAULT NULL,
    status ENUM('draft', 'active', 'paused') NOT NULL DEFAULT 'draft',
    max_plays_per_ip INT NOT NULL DEFAULT 1,
    play_limit_interval ENUM('none','hour','day','week','month') NOT NULL DEFAULT 'none',
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES admins(id)
);

CREATE TABLE prizes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scratchcard_id INT NOT NULL,
    name VARCHAR(190) NOT NULL,
    probability DECIMAL(8,4) NOT NULL DEFAULT 0,
    stock INT NOT NULL DEFAULT 0,
    won_count INT NOT NULL DEFAULT 0,
    image_path VARCHAR(255) DEFAULT NULL,
    prize_type ENUM('instant_win','draw_entry') NOT NULL DEFAULT 'instant_win',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (scratchcard_id) REFERENCES scratchcards(id) ON DELETE CASCADE
);

CREATE TABLE plays (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    scratchcard_id INT NOT NULL,
    prize_id INT DEFAULT NULL,
    ip_address VARCHAR(64) NOT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    session_token VARCHAR(64) NOT NULL,
    play_signature CHAR(64) DEFAULT NULL,
    player_name VARCHAR(150) DEFAULT NULL,
    player_email VARCHAR(190) DEFAULT NULL,
    player_name_encrypted TEXT DEFAULT NULL,
    player_email_encrypted TEXT DEFAULT NULL,
    player_email_hash CHAR(64) DEFAULT NULL,
    gdpr_consent_at TIMESTAMP NULL DEFAULT NULL,
    gdpr_consent_text TEXT DEFAULT NULL,
    anonymized_at TIMESTAMP NULL DEFAULT NULL,
    geo_country_code VARCHAR(8) DEFAULT NULL,
    geo_country_name VARCHAR(120) DEFAULT NULL,
    geo_region VARCHAR(120) DEFAULT NULL,
    geo_city VARCHAR(120) DEFAULT NULL,
    geo_latitude DECIMAL(10,6) DEFAULT NULL,
    geo_longitude DECIMAL(10,6) DEFAULT NULL,
    result ENUM('win', 'lose') NOT NULL,
    draw_winner TINYINT(1) NOT NULL DEFAULT 0,
    draw_selected_at TIMESTAMP NULL DEFAULT NULL,
    played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (scratchcard_id) REFERENCES scratchcards(id) ON DELETE CASCADE,
    FOREIGN KEY (prize_id) REFERENCES prizes(id) ON DELETE SET NULL
);


CREATE TABLE email_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    scratchcard_id INT NOT NULL,
    play_id BIGINT DEFAULT NULL,
    recipient_email VARCHAR(190) NOT NULL,
    recipient_email_hash CHAR(64) DEFAULT NULL,
    subject VARCHAR(190) NOT NULL,
    delivery_status ENUM('sent','failed','test_sent','test_failed') NOT NULL DEFAULT 'sent',
    error_message TEXT DEFAULT NULL,
    sent_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (scratchcard_id) REFERENCES scratchcards(id) ON DELETE CASCADE,
    FOREIGN KEY (play_id) REFERENCES plays(id) ON DELETE SET NULL
);