116 lines
6.7 KiB
SQL
116 lines
6.7 KiB
SQL
-- IMFestival SMS Dashboard — database schema
|
|
-- Runs automatically on first MariaDB container start.
|
|
|
|
CREATE DATABASE IF NOT EXISTS imf_sms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE imf_sms;
|
|
|
|
-- ─── Core attendee data ───────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS attendees (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
first_name VARCHAR(100) NOT NULL,
|
|
last_name VARCHAR(100) NOT NULL,
|
|
email VARCHAR(255) DEFAULT NULL,
|
|
mobile_number VARCHAR(30) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_mobile (mobile_number)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS workshop_sessions (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
workshop_name VARCHAR(255) NOT NULL,
|
|
workshop_time DATETIME DEFAULT NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS registrations (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
attendee_id INT UNSIGNED NOT NULL,
|
|
workshop_session_id INT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (id),
|
|
FOREIGN KEY (attendee_id) REFERENCES attendees(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (workshop_session_id) REFERENCES workshop_sessions(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ─── SMS templates ────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS sms_templates (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
name VARCHAR(255) NOT NULL,
|
|
body VARCHAR(160) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ─── Scheduled send rules ─────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS scheduled_rules (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
template_id INT UNSIGNED NOT NULL,
|
|
mode ENUM('relative','specific') NOT NULL,
|
|
offset_value INT DEFAULT NULL, -- used when mode=relative
|
|
offset_unit ENUM('minutes','hours','days') DEFAULT NULL, -- used when mode=relative
|
|
specific_datetime DATETIME DEFAULT NULL, -- used when mode=specific
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
FOREIGN KEY (template_id) REFERENCES sms_templates(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ─── Send jobs ────────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS send_jobs (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
template_id INT UNSIGNED DEFAULT NULL,
|
|
custom_body VARCHAR(160) DEFAULT NULL,
|
|
recipient_scope ENUM('all','workshop') NOT NULL DEFAULT 'all',
|
|
workshop_session_id INT UNSIGNED DEFAULT NULL,
|
|
scheduled_rule_id INT UNSIGNED DEFAULT NULL,
|
|
status ENUM('pending','sending','completed','failed') NOT NULL DEFAULT 'pending',
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
sent_at TIMESTAMP DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
FOREIGN KEY (template_id) REFERENCES sms_templates(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (workshop_session_id) REFERENCES workshop_sessions(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (scheduled_rule_id) REFERENCES scheduled_rules(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ─── Delivery log ─────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS delivery_log (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
send_job_id INT UNSIGNED NOT NULL,
|
|
attendee_id INT UNSIGNED DEFAULT NULL,
|
|
mobile_number VARCHAR(30) NOT NULL,
|
|
status ENUM('sent','delivered','failed') NOT NULL DEFAULT 'sent',
|
|
sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
delivered_at TIMESTAMP DEFAULT NULL,
|
|
error_message TEXT DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
FOREIGN KEY (send_job_id) REFERENCES send_jobs(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (attendee_id) REFERENCES attendees(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ─── System settings ──────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS `system` (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
setting_name VARCHAR(100) NOT NULL,
|
|
setting_value TEXT DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_setting_name (setting_name)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Seed default settings
|
|
INSERT IGNORE INTO `system` (setting_name, setting_value) VALUES
|
|
('clickatell_api_key', NULL),
|
|
('clickatell_cost_per_sms', '0.04849'),
|
|
('default_sender_id', 'IMFestival'),
|
|
('sandbox_mode', '0');
|
|
|
|
-- ─── Schema migrations (idempotent) ─────────────────────────────────────────
|
|
-- Safe to run on existing databases; ADD COLUMN IF NOT EXISTS is a no-op when
|
|
-- the column already exists.
|
|
ALTER TABLE delivery_log ADD COLUMN IF NOT EXISTS is_sandbox TINYINT(1) NOT NULL DEFAULT 0;
|