database.sql

CREATE DATABASE IF NOT EXISTS conference_db;
USE conference_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    role ENUM('Admin', 'Registration Desk', 'Accommodation Officer') NOT NULL
);

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    venue VARCHAR(255),
    description TEXT,
    start_date DATE,
    end_date DATE,
    total_days INT,
    status ENUM('Upcoming', 'Active', 'Completed') DEFAULT 'Upcoming'
);

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT,
    category_id INT,
    full_name VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    national_id VARCHAR(50),
    gender ENUM('Male', 'Female'),
    church VARCHAR(255),
    location VARCHAR(255),
    email VARCHAR(100),
    sleeping_onsite ENUM('Yes', 'No') DEFAULT 'No',
    room_number VARCHAR(50),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    participant_id INT,
    amount_paid DECIMAL(10,2) DEFAULT 0,
    balance DECIMAL(10,2) DEFAULT 0,
    status ENUM('Paid', 'Partial', 'Unpaid') DEFAULT 'Unpaid',
    FOREIGN KEY (participant_id) REFERENCES participants(id) ON DELETE CASCADE
);

CREATE TABLE attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    participant_id INT,
    event_id INT,
    day_number INT,
    status ENUM('Present', 'Absent') DEFAULT 'Absent',
    check_in_time DATETIME,
    FOREIGN KEY (participant_id) REFERENCES participants(id) ON DELETE CASCADE
);

INSERT INTO users (username, password, full_name, role) VALUES 
('admin', '$2y$10$WnFIAX8h/qtkE0OqDBil0.a6uf2B/nZNsOGpLeQN1U6oa9t/1R4gu', 'System Admin', 'Admin');

INSERT INTO categories (name) VALUES ('Host'), ('Delegate'), ('Facilitator');