-- XChange Registration Database Schema
-- Run this SQL query to create all necessary tables

-- Main registrations table
CREATE TABLE registrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    registration_number VARCHAR(50) UNIQUE NOT NULL,
    
    -- Pass selection data
    delegate_type ENUM('earlyBird', 'standard') NOT NULL,
    delegate_count INT NOT NULL,
    has_standard_table BOOLEAN DEFAULT FALSE,
    has_double_table BOOLEAN DEFAULT FALSE,
    
    -- Contact/University information (single set per registration)
    contact_salutation VARCHAR(20),
    contact_full_name VARCHAR(255) NOT NULL,
    contact_badge_name VARCHAR(100) NOT NULL,
    contact_designation VARCHAR(255) NOT NULL,
    contact_department VARCHAR(255) NOT NULL,
    contact_email VARCHAR(255) NOT NULL,
    contact_mobile VARCHAR(50) NOT NULL,
    university_name VARCHAR(255) NOT NULL,
    country VARCHAR(100) NOT NULL,
    
    -- Payment information
    payment_method ENUM('gateway', 'tt') NOT NULL,
    payment_status ENUM('pending', 'paid', 'failed', 'expired') DEFAULT 'pending',
    payment_reference VARCHAR(100),
    total_amount DECIMAL(10, 2) NOT NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    payment_deadline DATE,
    
    -- Additional metadata
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    INDEX idx_registration_number (registration_number),
    INDEX idx_contact_email (contact_email),
    INDEX idx_payment_status (payment_status),
    INDEX idx_created_at (created_at)
);

-- Individual delegates table (one record per delegate)
CREATE TABLE delegates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    registration_id INT NOT NULL,
    delegate_number INT NOT NULL,
    
    -- Delegate personal information
    full_name VARCHAR(255) NOT NULL,
    badge_name VARCHAR(100) NOT NULL,
    designation VARCHAR(255) NOT NULL,
    dietary_requirements VARCHAR(100),
    special_assistance TEXT,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (registration_id) REFERENCES registrations(id) ON DELETE CASCADE,
    INDEX idx_registration_id (registration_id),
    INDEX idx_delegate_number (delegate_number)
);

-- Payment transactions table (for tracking payment history)
CREATE TABLE payment_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    registration_id INT DEFAULT NULL,
    registration_number VARCHAR(50) NOT NULL,
    
    -- Transaction details
    transaction_type ENUM('gateway', 'bank_transfer', 'refund', 'admin_update') NOT NULL,
    transaction_status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    transaction_reference VARCHAR(100),
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    
    -- Gateway specific fields
    gateway_transaction_id VARCHAR(255),
    gateway_response TEXT,
    
    -- Bank transfer specific fields
    bank_reference VARCHAR(100),
    transfer_date DATE,
    bank_confirmation TEXT,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_registration_id (registration_id),
    INDEX idx_registration_number (registration_number),
    INDEX idx_transaction_status (transaction_status),
    INDEX idx_transaction_reference (transaction_reference)
);

-- Admin users table (RBAC system)
CREATE TABLE admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'manager', 'viewer') NOT NULL DEFAULT 'viewer',
    is_active TINYINT(1) DEFAULT 1,
    last_login TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT DEFAULT NULL,
    
    INDEX idx_username (username),
    INDEX idx_role (role),
    INDEX idx_is_active (is_active),
    FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL
);

-- Admin activity log table (audit trail)
CREATE TABLE admin_activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    target_type VARCHAR(50) DEFAULT NULL,
    target_id INT DEFAULT NULL,
    details TEXT DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_admin_user_id (admin_user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
);

-- Admin settings table (for storing configuration)
CREATE TABLE admin_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NOT NULL,
    setting_type ENUM('string', 'number', 'boolean', 'json', 'file', 'template') DEFAULT 'string',
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by VARCHAR(100),
    
    INDEX idx_setting_key (setting_key)
);

-- Database views for reporting and analytics
CREATE VIEW registration_stats AS
SELECT 
    COUNT(*) AS total_registrations,
    SUM(delegate_count) AS total_delegates,
    SUM(CASE WHEN has_standard_table = 1 THEN 1 ELSE 0 END) AS standard_tables,
    SUM(CASE WHEN has_double_table = 1 THEN 1 ELSE 0 END) AS double_tables,
    SUM(total_amount) AS total_revenue,
    COUNT(CASE WHEN payment_status = 'paid' THEN 1 END) AS paid_registrations,
    COUNT(CASE WHEN payment_status = 'pending' THEN 1 END) AS pending_payments,
    COUNT(CASE WHEN delegate_type = 'earlyBird' THEN 1 END) AS early_bird_registrations,
    COUNT(CASE WHEN delegate_type = 'standard' THEN 1 END) AS standard_registrations
FROM registrations;

CREATE VIEW dietary_summary AS
SELECT 
    d.dietary_requirements,
    COUNT(*) AS count
FROM delegates d
JOIN registrations r ON d.registration_id = r.id
WHERE d.dietary_requirements IS NOT NULL AND d.dietary_requirements != ''
GROUP BY d.dietary_requirements
ORDER BY COUNT(*) DESC;

CREATE VIEW special_assistance_summary AS
SELECT 
    r.registration_number,
    r.contact_full_name,
    r.contact_email,
    d.full_name AS delegate_name,
    d.special_assistance
FROM delegates d
JOIN registrations r ON d.registration_id = r.id
WHERE d.special_assistance IS NOT NULL AND d.special_assistance != ''
ORDER BY r.created_at ASC;

-- Insert admin users (from production database)
INSERT INTO admin_users (id, username, password_hash, full_name, email, role, is_active, last_login, created_at, updated_at, created_by) VALUES
(1, 'admin', '$2y$10$gyFpsCqoO8KSmrzf43sE3ucb972kNQkAY665lbXBdWIuAnadmKWLK', 'System Administrator', 'admin@xchange2025.com', 'super_admin', 1, '2025-08-13 03:14:46', '2025-08-12 13:18:32', '2025-08-13 03:14:46', NULL),
(2, 'roshel', '$2y$10$U3C5QbCtvgMXq.JUW6Gu0u0OWIMWIztvYo4ZRur.kXID92V0rVMP.', 'Roshel', 'roshel@dyits.com.my', 'manager', 1, '2025-08-13 01:37:44', '2025-08-13 01:37:17', '2025-08-13 01:37:44', 1),
(3, 'hansi', '$2y$10$Rf6x7UAmNrRId1nUX0.ao.0oytuVH0W4rFKvnu63M3Ztm5VJNLnWi', 'Hansi', 'hansi@dyits.com.my', 'viewer', 1, '2025-08-13 01:39:14', '2025-08-13 01:39:04', '2025-08-13 01:39:14', 1);

-- Insert default admin settings
INSERT INTO admin_settings (setting_key, setting_value, setting_type, description) VALUES
('early_bird_enabled', 'enabled', 'boolean', 'Enable/disable early bird pricing'),
('early_bird_deadline', '2025-08-31T23:59:59', 'string', 'Early bird registration deadline'),
('early_bird_price', '500', 'number', 'Early bird delegate pass price in USD'),
('standard_price', '550', 'number', 'Standard delegate pass price in USD'),
('standard_table_price', '550', 'number', 'Standard table presentation price in USD'),
('double_table_price', '850', 'number', 'Double table presentation price in USD'),
('max_delegates', '50', 'number', 'Maximum delegates per registration'),
('min_delegates', '1', 'number', 'Minimum delegates per registration'),
('event_name', 'AppliedHE Xchange 2025', 'string', 'Event name'),
('event_dates', '27–28 November 2025', 'string', 'Event dates'),
('event_venue', 'Sunway University, Malaysia', 'string', 'Event venue'),
('bank_name', 'DBS Bank Ltd Singapore', 'string', 'Bank name for transfers'),
('account_name', 'AppliedHE Pte. Ltd.', 'string', 'Bank account name'),
('account_number', '015-905678-3', 'string', 'Bank account number'),
('swift_code', 'DBSSSGSG', 'string', 'Bank SWIFT code'),
('reference_prefix', 'XCHANGE2025', 'string', 'Payment reference prefix'),
('dietaryOptions', '["None","Vegetarian","Vegan","Halal","Nut Allergy","Other"]', 'json', 'Available dietary requirement options');

-- Dietary requirements summary view
CREATE VIEW dietary_summary AS
SELECT 
    d.dietary_requirements,
    COUNT(*) as count
FROM delegates d
JOIN registrations r ON d.registration_id = r.id
WHERE d.dietary_requirements IS NOT NULL AND d.dietary_requirements != ''
GROUP BY d.dietary_requirements
ORDER BY count DESC;

-- Special assistance summary view  
CREATE VIEW special_assistance_summary AS
SELECT 
    r.registration_number,
    r.contact_full_name,
    r.contact_email,
    d.full_name as delegate_name,
    d.special_assistance
FROM delegates d
JOIN registrations r ON d.registration_id = r.id
WHERE d.special_assistance IS NOT NULL AND d.special_assistance != ''
ORDER BY r.created_at;

-- Admin users table for role-based access control
CREATE TABLE admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'manager', 'viewer') NOT NULL DEFAULT 'viewer',
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT,
    
    INDEX idx_username (username),
    INDEX idx_role (role),
    INDEX idx_is_active (is_active),
    FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL
);

-- Insert default super admin user (password: XChange2025!Admin)
INSERT INTO admin_users (username, password_hash, full_name, email, role, is_active) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'admin@xchange2025.com', 'super_admin', TRUE);

-- Admin activity log table for audit trail
CREATE TABLE admin_activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    target_type VARCHAR(50),
    target_id INT,
    details TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_admin_user_id (admin_user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
);
