CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(80) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS login_throttle (
 identity_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
 attempts INT UNSIGNED NOT NULL DEFAULT 0,
 window_started DATETIME NOT NULL,
 blocked_until DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS import_batches (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 filename VARCHAR(250) NOT NULL,
 total_rows INT UNSIGNED NOT NULL DEFAULT 0,
 extracted INT UNSIGNED NOT NULL DEFAULT 0,
 new_contacts INT UNSIGNED NOT NULL DEFAULT 0,
 duplicate_contacts INT UNSIGNED NOT NULL DEFAULT 0,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS contacts (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 mobile CHAR(11) CHARACTER SET ascii COLLATE ascii_bin NOT NULL UNIQUE,
 context TEXT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS contact_sources (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 contact_id BIGINT UNSIGNED NOT NULL,
 batch_id BIGINT UNSIGNED NOT NULL,
 sheet_name VARCHAR(190) NOT NULL,
 row_number INT UNSIGNED NOT NULL,
 INDEX idx_source_contact(contact_id),
 INDEX idx_source_batch(batch_id),
 CONSTRAINT fk_sources_contacts FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
 CONSTRAINT fk_sources_batches FOREIGN KEY (batch_id) REFERENCES import_batches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS campaigns (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(160) NOT NULL,
 template_id INT UNSIGNED NOT NULL,
 code_mode ENUM('random','id') NOT NULL DEFAULT 'random',
 params_json TEXT NOT NULL,
 expected_sender VARCHAR(40) NOT NULL DEFAULT '',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS campaign_recipients (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 campaign_id BIGINT UNSIGNED NOT NULL,
 contact_id BIGINT UNSIGNED NOT NULL,
 code CHAR(6) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
 status ENUM('pending','sending','accepted','failed','unknown') NOT NULL DEFAULT 'pending',
 reference_id VARCHAR(120) DEFAULT NULL,
 actual_sender VARCHAR(80) DEFAULT NULL,
 last_error VARCHAR(400) DEFAULT NULL,
 attempt_token CHAR(32) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
 attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
 claimed_at DATETIME DEFAULT NULL,
 sent_at DATETIME DEFAULT NULL,
 UNIQUE KEY uniq_campaign_contact(campaign_id,contact_id),
 KEY idx_campaign_status(campaign_id,status,id),
 KEY idx_claimed_status(status,claimed_at),
 CONSTRAINT fk_cr_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
 CONSTRAINT fk_cr_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS send_logs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 recipient_id BIGINT UNSIGNED NOT NULL,
 attempt_token CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL UNIQUE,
 status ENUM('sending','accepted','failed','unknown') NOT NULL,
 reference_id VARCHAR(120) DEFAULT NULL,
 sender VARCHAR(80) DEFAULT NULL,
 error_message VARCHAR(400) DEFAULT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 completed_at DATETIME DEFAULT NULL,
 KEY idx_log_recipient(recipient_id,id),
 KEY idx_log_stale(status,created_at),
 CONSTRAINT fk_log_recipient FOREIGN KEY (recipient_id) REFERENCES campaign_recipients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
