-- Corporate Affairs Commission — Leadership Strategy and Artificial Intelligence Development – Database Schema
-- Create the database first:  CREATE DATABASE cac_leadership_ai; USE cac_leadership_ai;

CREATE TABLE participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    middle_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(150) UNIQUE,
    phone_number VARCHAR(20) UNIQUE,
    gender VARCHAR(20),
    designation VARCHAR(150),
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE attendance_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    participant_id INT,
    participant_full_name VARCHAR(255),
    participant_email VARCHAR(255),
    date DATE,
    sign_in_time DATETIME,
    sign_out_time DATETIME,
    FOREIGN KEY (participant_id) REFERENCES participants(id)
);

-- If you already created the participants table WITHOUT designation, run:
-- ALTER TABLE participants ADD COLUMN designation VARCHAR(150) AFTER gender;