CREATE DATABASE IF NOT EXISTS event_management_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE event_management_system;
SET NAMES utf8mb4;

CREATE TABLE roles (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, role_name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, role_id BIGINT UNSIGNED NOT NULL, first_name VARCHAR(100) NOT NULL, middle_name VARCHAR(100) NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE, username VARCHAR(100) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, profile_photo VARCHAR(255) NULL, status ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active', last_login_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id) ON UPDATE CASCADE ON DELETE RESTRICT) ENGINE=InnoDB;
CREATE TABLE maintenance_categories (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_code VARCHAR(50) NOT NULL UNIQUE, category_name VARCHAR(100) NOT NULL, has_parent TINYINT(1) NOT NULL DEFAULT 0, parent_category_id BIGINT UNSIGNED NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_maintenance_categories_parent FOREIGN KEY (parent_category_id) REFERENCES maintenance_categories(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE maintenance_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id BIGINT UNSIGNED NOT NULL, parent_id BIGINT UNSIGNED NULL, item_code VARCHAR(50) NULL, item_name VARCHAR(150) NOT NULL, item_description VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_maintenance_items_category FOREIGN KEY (category_id) REFERENCES maintenance_categories(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_maintenance_items_parent FOREIGN KEY (parent_id) REFERENCES maintenance_items(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE attendees (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, participant_no VARCHAR(50) NULL UNIQUE, participant_type_id BIGINT UNSIGNED NULL, student_no VARCHAR(50) NULL UNIQUE, first_name VARCHAR(100) NOT NULL, middle_name VARCHAR(100) NULL, last_name VARCHAR(100) NOT NULL, suffix VARCHAR(20) NULL, sex ENUM('male','female','other') NULL, sex_id BIGINT UNSIGNED NULL, birth_date DATE NULL, email VARCHAR(150) NULL, contact_no VARCHAR(30) NULL, organization_id BIGINT UNSIGNED NULL, unit_id BIGINT UNSIGNED NULL, designation_id BIGINT UNSIGNED NULL, participant_group_id BIGINT UNSIGNED NULL, school VARCHAR(150) NULL, department VARCHAR(150) NULL, year_level VARCHAR(50) NULL, year_level_id BIGINT UNSIGNED NULL, address TEXT NULL, photo VARCHAR(255) NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_attendees_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_attendees_pt FOREIGN KEY (participant_type_id) REFERENCES maintenance_items(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_attendees_sex FOREIGN KEY (sex_id) REFERENCES maintenance_items(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_attendees_org FOREIGN KEY (organization_id) REFERENCES maintenance_items(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_attendees_unit FOREIGN KEY (unit_id) REFERENCES maintenance_items(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_attendees_des FOREIGN KEY (designation_id) REFERENCES maintenance_items(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_attendees_pg FOREIGN KEY (participant_group_id) REFERENCES maintenance_items(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_attendees_yl FOREIGN KEY (year_level_id) REFERENCES maintenance_items(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE speakers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(150) NOT NULL, designation VARCHAR(150) NULL, organization VARCHAR(150) NULL, email VARCHAR(150) NULL, contact_no VARCHAR(30) NULL, photo VARCHAR(255) NULL, signature_image VARCHAR(255) NULL, bio TEXT NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE certificate_templates (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, template_name VARCHAR(150) NOT NULL, page_size VARCHAR(20) NOT NULL DEFAULT 'A4', orientation ENUM('portrait','landscape') NOT NULL DEFAULT 'landscape', background_image VARCHAR(255) NULL, body_html LONGTEXT NULL, default_signature_image VARCHAR(255) NULL, qr_enabled TINYINT(1) NOT NULL DEFAULT 1, serial_prefix VARCHAR(20) NOT NULL DEFAULT 'CERT', status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_certificate_templates_user FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE events (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_code VARCHAR(50) NOT NULL UNIQUE, title VARCHAR(200) NOT NULL, description TEXT NULL, event_type VARCHAR(100) NULL, event_type_id BIGINT UNSIGNED NULL, venue VARCHAR(200) NULL, banner_image VARCHAR(255) NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, organizer_id BIGINT UNSIGNED NOT NULL, certificate_template_id BIGINT UNSIGNED NULL, attendance_mode ENUM('whole_event','per_day','per_session') NOT NULL DEFAULT 'per_session', minimum_attendance_percent DECIMAL(5,2) NOT NULL DEFAULT 100.00, require_time_out TINYINT(1) NOT NULL DEFAULT 0, registration_open_at DATETIME NULL, registration_close_at DATETIME NULL, public_registration_enabled TINYINT(1) NOT NULL DEFAULT 1, registration_required TINYINT(1) NOT NULL DEFAULT 1, audience_mode ENUM('all','restricted') NOT NULL DEFAULT 'all', badge_enabled TINYINT(1) NOT NULL DEFAULT 1, certificate_enabled TINYINT(1) NOT NULL DEFAULT 1, status ENUM('draft','published','ongoing','completed','cancelled') NOT NULL DEFAULT 'draft', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_events_user FOREIGN KEY (organizer_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_events_ct FOREIGN KEY (certificate_template_id) REFERENCES certificate_templates(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_events_event_type FOREIGN KEY (event_type_id) REFERENCES maintenance_items(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE event_allowed_participant_types (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id BIGINT UNSIGNED NOT NULL, participant_type_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_event_allowed_participant_types (event_id,participant_type_id), CONSTRAINT fk_eapt_event FOREIGN KEY (event_id) REFERENCES events(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_eapt_participant_type FOREIGN KEY (participant_type_id) REFERENCES maintenance_items(id) ON UPDATE CASCADE ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE event_speakers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id BIGINT UNSIGNED NOT NULL, speaker_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_event_speakers (event_id,speaker_id), CONSTRAINT fk_event_speakers_event FOREIGN KEY (event_id) REFERENCES events(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_event_speakers_speaker FOREIGN KEY (speaker_id) REFERENCES speakers(id) ON UPDATE CASCADE ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE event_sessions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id BIGINT UNSIGNED NOT NULL, session_code VARCHAR(50) NOT NULL UNIQUE, session_title VARCHAR(200) NOT NULL, session_date DATE NOT NULL, start_time TIME NOT NULL, end_time TIME NOT NULL, grace_minutes INT NOT NULL DEFAULT 15, attendance_required TINYINT(1) NOT NULL DEFAULT 1, certificate_countable TINYINT(1) NOT NULL DEFAULT 1, session_status ENUM('scheduled','open','closed','cancelled') NOT NULL DEFAULT 'scheduled', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_event_sessions_event FOREIGN KEY (event_id) REFERENCES events(id) ON UPDATE CASCADE ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE event_registrations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id BIGINT UNSIGNED NOT NULL, attendee_id BIGINT UNSIGNED NOT NULL, registration_type ENUM('online','manual','imported') NOT NULL DEFAULT 'online', reference_no VARCHAR(50) NOT NULL UNIQUE, registration_status ENUM('pending','approved','rejected','cancelled','attended') NOT NULL DEFAULT 'pending', approved_by BIGINT UNSIGNED NULL, approved_at DATETIME NULL, registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, remarks VARCHAR(255) NULL, UNIQUE KEY uq_event_registrations (event_id,attendee_id), CONSTRAINT fk_er_event FOREIGN KEY (event_id) REFERENCES events(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_er_attendee FOREIGN KEY (attendee_id) REFERENCES attendees(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_er_user FOREIGN KEY (approved_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE rfid_tags (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, attendee_id BIGINT UNSIGNED NOT NULL, rfid_uid VARCHAR(100) NOT NULL UNIQUE, tag_status ENUM('active','inactive','lost','replaced') NOT NULL DEFAULT 'active', assigned_by BIGINT UNSIGNED NULL, assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, unassigned_at DATETIME NULL, notes VARCHAR(255) NULL, CONSTRAINT fk_rfid_tags_attendee FOREIGN KEY (attendee_id) REFERENCES attendees(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_rfid_tags_user FOREIGN KEY (assigned_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE rfid_scan_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id BIGINT UNSIGNED NOT NULL, session_id BIGINT UNSIGNED NOT NULL, attendee_id BIGINT UNSIGNED NULL, rfid_uid VARCHAR(100) NOT NULL, scan_type ENUM('time_in','time_out') NOT NULL DEFAULT 'time_in', scan_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, result_status ENUM('success','duplicate','invalid_tag','not_registered','session_closed','already_checked_out','error') NOT NULL DEFAULT 'success', result_message VARCHAR(255) NULL, scanner_user_id BIGINT UNSIGNED NULL, ip_address VARCHAR(45) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_scan_event FOREIGN KEY (event_id) REFERENCES events(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_scan_session FOREIGN KEY (session_id) REFERENCES event_sessions(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_scan_attendee FOREIGN KEY (attendee_id) REFERENCES attendees(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_scan_user FOREIGN KEY (scanner_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE attendance_records (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id BIGINT UNSIGNED NOT NULL, session_id BIGINT UNSIGNED NOT NULL, attendee_id BIGINT UNSIGNED NOT NULL, check_in_time DATETIME NULL, check_out_time DATETIME NULL, attendance_status ENUM('present','late','absent','excused') NOT NULL DEFAULT 'present', attendance_percent DECIMAL(5,2) NOT NULL DEFAULT 100.00, remarks VARCHAR(255) NULL, marked_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_attendance_records (session_id,attendee_id), CONSTRAINT fk_ar_event FOREIGN KEY (event_id) REFERENCES events(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ar_session FOREIGN KEY (session_id) REFERENCES event_sessions(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ar_attendee FOREIGN KEY (attendee_id) REFERENCES attendees(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_ar_user FOREIGN KEY (marked_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE certificates (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id BIGINT UNSIGNED NOT NULL, attendee_id BIGINT UNSIGNED NOT NULL, certificate_template_id BIGINT UNSIGNED NULL, certificate_no VARCHAR(100) NOT NULL UNIQUE, verification_code VARCHAR(100) NOT NULL UNIQUE, qr_image_path VARCHAR(255) NULL, pdf_path VARCHAR(255) NULL, eligibility_status ENUM('eligible','not_eligible') NOT NULL DEFAULT 'not_eligible', issued_by BIGINT UNSIGNED NULL, issued_at DATETIME NULL, emailed_at DATETIME NULL, revoked_at DATETIME NULL, revoke_reason VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_certificates_event_attendee (event_id,attendee_id), CONSTRAINT fk_cert_event FOREIGN KEY (event_id) REFERENCES events(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_cert_attendee FOREIGN KEY (attendee_id) REFERENCES attendees(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_cert_template FOREIGN KEY (certificate_template_id) REFERENCES certificate_templates(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_cert_user FOREIGN KEY (issued_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;

CREATE TABLE badges (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id BIGINT UNSIGNED NOT NULL, attendee_id BIGINT UNSIGNED NOT NULL, badge_no VARCHAR(100) NOT NULL UNIQUE, badge_template_name VARCHAR(150) NULL, qr_code_path VARCHAR(255) NULL, printed_at DATETIME NULL, printed_by BIGINT UNSIGNED NULL, status ENUM('generated','printed','reprinted') NOT NULL DEFAULT 'generated', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_badges_event_attendee (event_id,attendee_id), KEY idx_badges_event_id (event_id), KEY idx_badges_attendee_id (attendee_id), KEY idx_badges_printed_by (printed_by), CONSTRAINT fk_badges_event FOREIGN KEY (event_id) REFERENCES events(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_badges_attendee FOREIGN KEY (attendee_id) REFERENCES attendees(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_badges_printed_by FOREIGN KEY (printed_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE email_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id BIGINT UNSIGNED NULL, attendee_id BIGINT UNSIGNED NULL, certificate_id BIGINT UNSIGNED NULL, email_to VARCHAR(150) NOT NULL, email_type ENUM('registration','certificate','reminder','notice') NOT NULL, subject VARCHAR(255) NOT NULL, body_excerpt TEXT NULL, sent_status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued', error_message TEXT NULL, sent_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_el_event FOREIGN KEY (event_id) REFERENCES events(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_el_attendee FOREIGN KEY (attendee_id) REFERENCES attendees(id) ON UPDATE CASCADE ON DELETE SET NULL, CONSTRAINT fk_el_certificate FOREIGN KEY (certificate_id) REFERENCES certificates(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;
INSERT INTO roles (role_name,description) VALUES ('super_admin','Full system access'),('organizer','Can manage own events'),('staff','Can scan attendance and assist'),('attendee','Portal access');
INSERT INTO maintenance_categories (category_code,category_name,has_parent,sort_order) VALUES ('event_type','Event Type',0,1),('participant_type','Participant Type',0,2),('sex','Sex',0,3),('organization','Organization / College / Office',0,4),('unit','Department / Unit / Program',1,5),('designation','Designation / Position',0,6),('participant_group','Participant Group',0,7),('year_level','Year Level',0,8);
UPDATE maintenance_categories SET parent_category_id=(SELECT id FROM (SELECT id FROM maintenance_categories WHERE category_code='organization' LIMIT 1) x) WHERE category_code='unit';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'seminar','Seminar',1 FROM maintenance_categories WHERE category_code='event_type';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'workshop','Workshop',2 FROM maintenance_categories WHERE category_code='event_type';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'meeting','Meeting',3 FROM maintenance_categories WHERE category_code='event_type';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'student','Student',1 FROM maintenance_categories WHERE category_code='participant_type';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'teaching_personnel','Teaching Personnel',2 FROM maintenance_categories WHERE category_code='participant_type';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'non_teaching_personnel','Non-Teaching Personnel',3 FROM maintenance_categories WHERE category_code='participant_type';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'university_official','University Official',4 FROM maintenance_categories WHERE category_code='participant_type';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'guest','Guest',5 FROM maintenance_categories WHERE category_code='participant_type';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'male','Male',1 FROM maintenance_categories WHERE category_code='sex';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'female','Female',2 FROM maintenance_categories WHERE category_code='sex';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'other','Other',3 FROM maintenance_categories WHERE category_code='sex';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'students','Students',1 FROM maintenance_categories WHERE category_code='participant_group';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'faculty','Faculty',2 FROM maintenance_categories WHERE category_code='participant_group';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'staff','Staff',3 FROM maintenance_categories WHERE category_code='participant_group';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'officials','Officials',4 FROM maintenance_categories WHERE category_code='participant_group';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'1','1st Year',1 FROM maintenance_categories WHERE category_code='year_level';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'2','2nd Year',2 FROM maintenance_categories WHERE category_code='year_level';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'coe','College of Engineering',1 FROM maintenance_categories WHERE category_code='organization';
INSERT INTO maintenance_items (category_id,item_code,item_name,sort_order) SELECT id,'registrar','Registrar''s Office',2 FROM maintenance_categories WHERE category_code='organization';
INSERT INTO maintenance_items (category_id,parent_id,item_code,item_name,sort_order) SELECT uc.id, oi.id,'cpe','Computer Engineering Department',1 FROM maintenance_categories uc JOIN maintenance_categories oc ON oc.category_code='organization' JOIN maintenance_items oi ON oi.category_id=oc.id AND oi.item_code='coe' WHERE uc.category_code='unit';
INSERT INTO maintenance_items (category_id,parent_id,item_code,item_name,sort_order) SELECT uc.id, oi.id,'records','Records Section',2 FROM maintenance_categories uc JOIN maintenance_categories oc ON oc.category_code='organization' JOIN maintenance_items oi ON oi.category_id=oc.id AND oi.item_code='registrar' WHERE uc.category_code='unit';

CREATE TABLE attendance_kiosks (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, kiosk_name VARCHAR(150) NOT NULL, event_id BIGINT UNSIGNED NOT NULL, session_id BIGINT UNSIGNED NOT NULL, scan_type ENUM('time_in','time_out') NOT NULL DEFAULT 'time_in', access_token VARCHAR(64) NOT NULL UNIQUE, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED NULL, last_accessed_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_attendance_kiosks_event_id (event_id), KEY idx_attendance_kiosks_session_id (session_id), KEY idx_attendance_kiosks_active (is_active), CONSTRAINT fk_attendance_kiosks_event FOREIGN KEY (event_id) REFERENCES events(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_attendance_kiosks_session FOREIGN KEY (session_id) REFERENCES event_sessions(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_attendance_kiosks_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL) ENGINE=InnoDB;