CREATE TABLE brands (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL, slug VARCHAR(190) NOT NULL UNIQUE,
 company_name VARCHAR(190) NOT NULL, logo_path VARCHAR(255) NULL, favicon_path VARCHAR(255) NULL,
 website_url VARCHAR(255) NULL, primary_colour VARCHAR(20) NOT NULL DEFAULT '#0d6efd', secondary_colour VARCHAR(20) NOT NULL DEFAULT '#6c757d',
 background_image VARCHAR(255) NULL, header_html TEXT NULL, footer_html TEXT NULL, custom_css MEDIUMTEXT NULL,
 social_json JSON NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 email VARCHAR(190) NOT NULL UNIQUE, name VARCHAR(190) NOT NULL, password_hash VARCHAR(255) NOT NULL,
 role ENUM('super_admin','manager','reporter') NOT NULL DEFAULT 'manager', is_active TINYINT(1) NOT NULL DEFAULT 1,
 totp_secret VARCHAR(64) NULL, two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0,
 recovery_codes_json JSON NULL,
 failed_logins INT NOT NULL DEFAULT 0, locked_until DATETIME NULL, last_login_at DATETIME NULL,
 created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE password_resets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL UNIQUE,
 expires_at DATETIME NOT NULL, used_at DATETIME NULL, created_at DATETIME NOT NULL,
 CONSTRAINT fk_reset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE competitions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, brand_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(190) NOT NULL, slug VARCHAR(190) NOT NULL, status ENUM('draft','scheduled','active','closed','archived') NOT NULL DEFAULT 'draft',
 starts_at DATETIME NULL, ends_at DATETIME NULL, timezone VARCHAR(64) NOT NULL DEFAULT 'Pacific/Auckland',
 intro_html MEDIUMTEXT NULL, fields_json JSON NOT NULL, rules_json JSON NULL,
 terms_html MEDIUMTEXT NULL, privacy_html MEDIUMTEXT NULL, terms_version VARCHAR(50) NOT NULL DEFAULT '1.0',
 confirmation_json JSON NULL, email_json JSON NULL, retention_days INT NULL,
 turnstile_enabled TINYINT(1) NOT NULL DEFAULT 0, turnstile_site_key VARCHAR(255) NULL, turnstile_secret_key VARCHAR(255) NULL,
 smtp_json JSON NULL, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_brand_slug (brand_id, slug),
 CONSTRAINT fk_comp_brand FOREIGN KEY (brand_id) REFERENCES brands(id),
 CONSTRAINT fk_comp_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE entries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, competition_id BIGINT UNSIGNED NOT NULL,
 reference_code VARCHAR(32) NOT NULL UNIQUE, first_name VARCHAR(190) NOT NULL, last_name VARCHAR(190) NOT NULL, email VARCHAR(190) NOT NULL,
 data_json JSON NOT NULL, consent_json JSON NULL, terms_version VARCHAR(50) NULL,
 ip_hash CHAR(64) NULL, ip_encrypted TEXT NULL, user_agent VARCHAR(500) NULL,
 status ENUM('valid','invalid','winner','deleted','anonymised') NOT NULL DEFAULT 'valid', submitted_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL, deleted_at DATETIME NULL,
 KEY idx_comp_email (competition_id,email), KEY idx_comp_time (competition_id,submitted_at),
 CONSTRAINT fk_entry_comp FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE entry_files (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, entry_id BIGINT UNSIGNED NOT NULL, field_name VARCHAR(190) NOT NULL,
 original_name VARCHAR(255) NOT NULL, stored_name VARCHAR(255) NOT NULL, mime_type VARCHAR(120) NOT NULL,
 file_size BIGINT UNSIGNED NOT NULL, sha256 CHAR(64) NOT NULL, created_at DATETIME NOT NULL,
 CONSTRAINT fk_file_entry FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE privacy_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, entry_id BIGINT UNSIGNED NULL, email VARCHAR(190) NOT NULL,
 request_type ENUM('access','correction','deletion','export') NOT NULL, status ENUM('open','in_progress','completed','rejected') NOT NULL DEFAULT 'open',
 notes TEXT NULL, requested_at DATETIME NOT NULL, completed_at DATETIME NULL, handled_by BIGINT UNSIGNED NULL,
 CONSTRAINT fk_priv_entry FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE SET NULL,
 CONSTRAINT fk_priv_user FOREIGN KEY (handled_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE draws (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, competition_id BIGINT UNSIGNED NOT NULL, entry_id BIGINT UNSIGNED NOT NULL,
 eligible_count INT NOT NULL, draw_token CHAR(64) NOT NULL, draw_number INT NOT NULL DEFAULT 1,
 supersedes_draw_id BIGINT UNSIGNED NULL, status ENUM('current','superseded','cancelled') NOT NULL DEFAULT 'current',
 reason TEXT NULL, notes TEXT NULL, certificate_no VARCHAR(64) NULL, drawn_by BIGINT UNSIGNED NULL, drawn_at DATETIME NOT NULL,
 CONSTRAINT fk_draw_comp FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE,
 CONSTRAINT fk_draw_entry FOREIGN KEY (entry_id) REFERENCES entries(id),
 CONSTRAINT fk_draw_user FOREIGN KEY (drawn_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_draw_previous FOREIGN KEY (supersedes_draw_id) REFERENCES draws(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE backups (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, filename VARCHAR(255) NOT NULL, file_size BIGINT UNSIGNED NOT NULL,
 sha256 CHAR(64) NOT NULL, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL,
 CONSTRAINT fk_backup_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, action VARCHAR(100) NOT NULL,
 entity_type VARCHAR(100) NULL, entity_id BIGINT UNSIGNED NULL, details_json JSON NULL, ip_hash CHAR(64) NULL, created_at DATETIME NOT NULL,
 KEY idx_audit_time (created_at), CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;