-- =========================================================
-- Online Event Invitation Management System - DB Schema
-- MySQL 8+ / MariaDB 10.4+
-- =========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------
-- users  (Super Administrator, Event Organizer)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    phone VARCHAR(30) DEFAULT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin','organizer') NOT NULL DEFAULT 'organizer',
    status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
    avatar VARCHAR(255) DEFAULT NULL,
    failed_login_attempts INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME DEFAULT NULL,
    remember_token VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- password_resets
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS password_resets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL,
    token VARCHAR(100) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (email),
    INDEX (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- events
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organizer_id INT UNSIGNED NOT NULL,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    type ENUM('Wedding','Birthday','Conference','Seminar','Church Event','Graduation','Corporate Event','Meeting','Party','Other') NOT NULL DEFAULT 'Other',
    description TEXT,
    event_date DATE NOT NULL,
    start_time TIME DEFAULT NULL,
    end_time TIME DEFAULT NULL,
    venue VARCHAR(190) DEFAULT NULL,
    address VARCHAR(255) DEFAULT NULL,
    map_url VARCHAR(500) DEFAULT NULL,
    contact_phone VARCHAR(30) DEFAULT NULL,
    contact_email VARCHAR(190) DEFAULT NULL,
    dress_code VARCHAR(150) DEFAULT NULL,
    cover_image VARCHAR(255) DEFAULT NULL,
    logo VARCHAR(255) DEFAULT NULL,
    max_guests INT UNSIGNED DEFAULT NULL,
    rsvp_deadline DATE DEFAULT NULL,
    status ENUM('draft','published','unpublished','archived') NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (organizer_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX (status), INDEX (event_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- guests
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS guests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) DEFAULT '',
    gender ENUM('Male','Female','Other','Unspecified') DEFAULT 'Unspecified',
    email VARCHAR(190) DEFAULT NULL,
    phone VARCHAR(30) DEFAULT NULL,
    address VARCHAR(255) DEFAULT NULL,
    category ENUM('VIP','Family','Friend','Staff','Client','Church Member','Sponsor','General Guest','Other') NOT NULL DEFAULT 'General Guest',
    allowed_guests INT UNSIGNED NOT NULL DEFAULT 1,
    table_number VARCHAR(20) DEFAULT NULL,
    dietary_requirement VARCHAR(255) DEFAULT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    INDEX (event_id), INDEX (category),
    FULLTEXT KEY ft_guest_name (first_name, last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- invitation_templates
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS invitation_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    style ENUM('Elegant','Modern','Wedding','Corporate','Church/Seminar') NOT NULL DEFAULT 'Modern',
    description VARCHAR(255) DEFAULT NULL,
    preview_image VARCHAR(255) DEFAULT NULL,
    template_data JSON DEFAULT NULL, -- colors, fonts, layout, background
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- event_programmes
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS event_programmes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    activity VARCHAR(190) NOT NULL,
    start_time TIME DEFAULT NULL,
    end_time TIME DEFAULT NULL,
    description VARCHAR(255) DEFAULT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    INDEX (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- invitations
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS invitations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    guest_id INT UNSIGNED NOT NULL,
    template_id INT UNSIGNED DEFAULT NULL,
    token VARCHAR(40) NOT NULL UNIQUE,
    qr_code VARCHAR(255) DEFAULT NULL,
    status ENUM('draft','ready','sent','delivered','opened','confirmed','declined','expired') NOT NULL DEFAULT 'draft',
    sent_at DATETIME DEFAULT NULL,
    opened_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES invitation_templates(id) ON DELETE SET NULL,
    UNIQUE KEY uniq_event_guest (event_id, guest_id),
    INDEX (token), INDEX (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- rsvps
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS rsvps (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invitation_id INT UNSIGNED NOT NULL UNIQUE,
    response ENUM('yes','no','maybe') NOT NULL,
    number_of_guests INT UNSIGNED NOT NULL DEFAULT 1,
    guest_names TEXT,
    phone VARCHAR(30) DEFAULT NULL,
    dietary_requirement VARCHAR(255) DEFAULT NULL,
    message TEXT,
    confirmation_number VARCHAR(20) DEFAULT NULL,
    responded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invitation_id) REFERENCES invitations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- attendance
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS attendance (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    guest_id INT UNSIGNED NOT NULL,
    invitation_id INT UNSIGNED DEFAULT NULL,
    checked_in TINYINT(1) NOT NULL DEFAULT 0,
    check_in_time DATETIME DEFAULT NULL,
    checked_in_by INT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE,
    FOREIGN KEY (invitation_id) REFERENCES invitations(id) ON DELETE SET NULL,
    FOREIGN KEY (checked_in_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY uniq_event_guest_att (event_id, guest_id),
    INDEX (checked_in)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- messages  (email / sms / whatsapp log)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS messages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    guest_id INT UNSIGNED DEFAULT NULL,
    channel ENUM('email','sms','whatsapp') NOT NULL DEFAULT 'email',
    subject VARCHAR(190) DEFAULT NULL,
    message TEXT,
    status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
    sent_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE,
    INDEX (event_id), INDEX (channel), INDEX (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- reminders (scheduling config per event)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS reminders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    days_before INT NOT NULL, -- 0 = event morning
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    last_run_at DATETIME DEFAULT NULL,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- activity_logs
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    action VARCHAR(100) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX (user_id), INDEX (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- settings (key/value)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (setting_key, setting_value) VALUES
    ('system_name', 'EventInvite Pro'),
    ('admin_email', 'admin@example.com'),
    ('contact_phone', ''),
    ('sms_provider', ''),
    ('sms_api_key', ''),
    ('smtp_host', ''),
    ('smtp_port', '587'),
    ('smtp_username', ''),
    ('smtp_password', ''),
    ('smtp_from_email', 'no-reply@example.com'),
    ('smtp_from_name', 'EventInvite Pro'),
    ('whatsapp_enabled', '1'),
    ('default_template_id', '1'),
    ('default_timezone', 'Africa/Dar_es_Salaam'),
    ('date_format', 'd M Y'),
    ('currency', 'TZS'),
    ('notifications_enabled', '1'),
    ('logo', ''),
    ('favicon', '')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- ---------------------------------------------------------
-- Seed: default invitation templates
-- ---------------------------------------------------------
INSERT INTO invitation_templates (name, style, description, template_data, status) VALUES
('Elegant Classic', 'Elegant', 'Refined serif type on soft cream background', '{"background":"#FBF7F0","font":"Playfair Display","font_size":"16","text_color":"#3B2F2F","button_color":"#B8860B","layout":"centered"}', 'active'),
('Modern Minimal', 'Modern', 'Clean sans-serif with bold accent color', '{"background":"#FFFFFF","font":"Inter","font_size":"16","text_color":"#1A1A2E","button_color":"#4F46E5","layout":"split"}', 'active'),
('Wedding Bliss', 'Wedding', 'Romantic florals with script accents', '{"background":"#FFF5F7","font":"Great Vibes","font_size":"18","text_color":"#5C374C","button_color":"#D96C8C","layout":"centered"}', 'active'),
('Corporate Blue', 'Corporate', 'Professional layout for conferences & meetings', '{"background":"#F4F7FB","font":"Roboto","font_size":"15","text_color":"#12263A","button_color":"#0B5ED7","layout":"banner"}', 'active'),
('Church & Seminar', 'Church/Seminar', 'Warm, welcoming design for church & seminar events', '{"background":"#FFFDF6","font":"Merriweather","font_size":"16","text_color":"#2E2A1F","button_color":"#8C6D1F","layout":"centered"}', 'active')
ON DUPLICATE KEY UPDATE name = VALUES(name);

SET FOREIGN_KEY_CHECKS = 1;
