-- UnixClub Database Schema for `unixgoap_unixclub`
-- In phpMyAdmin, click on your database `unixgoap_unixclub` first, then run this SQL.

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(191) NOT NULL UNIQUE,
  `username` VARCHAR(100) DEFAULT NULL,
  `university` VARCHAR(255) DEFAULT NULL,
  `gender` VARCHAR(50) DEFAULT NULL,
  `full_name` VARCHAR(191) DEFAULT NULL,
  `matric_id` VARCHAR(100) DEFAULT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `faculty` VARCHAR(191) DEFAULT NULL,
  `student_card_url` TEXT DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- If your table already exists, run these queries to add the event registration columns:
-- ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `username` VARCHAR(100) NULL AFTER `email`;
-- ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `university` VARCHAR(255) NULL AFTER `username`;
-- ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `gender` VARCHAR(50) NULL AFTER `university`;
-- ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `full_name` VARCHAR(191) NULL AFTER `gender`;
-- ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `matric_id` VARCHAR(100) NULL AFTER `full_name`;
-- ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `phone` VARCHAR(50) NULL AFTER `matric_id`;
-- ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `faculty` VARCHAR(191) NULL AFTER `phone`;
-- ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `student_card_url` TEXT NULL AFTER `faculty`;
-- ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `role` VARCHAR(50) DEFAULT 'student' AFTER `password`;

-- 2. Clubs Table
CREATE TABLE IF NOT EXISTS `clubs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(191) NOT NULL,
  `slug` VARCHAR(191) NOT NULL UNIQUE,
  `category` VARCHAR(100) NOT NULL,
  `club_type` VARCHAR(50) DEFAULT 'official',
  `university` VARCHAR(255) DEFAULT 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)',
  `faculty` VARCHAR(191) DEFAULT NULL,
  `short_description` TEXT DEFAULT NULL,
  `full_description` TEXT DEFAULT NULL,
  `logo_url` TEXT DEFAULT NULL,
  `banner_url` TEXT DEFAULT NULL,
  `objectives` TEXT DEFAULT NULL,
  `mission` TEXT DEFAULT NULL,
  `established_date` VARCHAR(50) DEFAULT NULL,
  `meeting_schedule` VARCHAR(255) DEFAULT NULL,
  `meeting_location` VARCHAR(255) DEFAULT NULL,
  `contact_email` VARCHAR(191) DEFAULT NULL,
  `contact_phone` VARCHAR(50) DEFAULT NULL,
  `website_url` VARCHAR(255) DEFAULT NULL,
  `social_media_links` TEXT DEFAULT NULL,
  `membership_requirements` TEXT DEFAULT NULL,
  `member_count` INT DEFAULT 0,
  `verification_status` VARCHAR(50) DEFAULT 'verified',
  `publication_status` VARCHAR(50) DEFAULT 'published',
  `is_suspended` TINYINT(1) DEFAULT 0,
  `suspension_reason` TEXT DEFAULT NULL,
  `primary_organizer_id` INT DEFAULT NULL,
  `primary_organizer_name` VARCHAR(191) DEFAULT NULL,
  `created_by_admin` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Club Organizers & Committee Members Table
CREATE TABLE IF NOT EXISTS `club_organizers` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `club_id` INT NOT NULL,
  `user_id` INT DEFAULT NULL,
  `user_name` VARCHAR(191) NOT NULL,
  `user_email` VARCHAR(191) DEFAULT NULL,
  `matric_id` VARCHAR(100) DEFAULT NULL,
  `role` VARCHAR(50) NOT NULL DEFAULT 'Club Organizer',
  `department_or_position` VARCHAR(100) DEFAULT NULL,
  `assigned_by` VARCHAR(100) DEFAULT 'admin',
  `assigned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_club_id` (`club_id`),
  INDEX `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Verification Requests Table
CREATE TABLE IF NOT EXISTS `verification_requests` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `club_id` INT NOT NULL,
  `club_name` VARCHAR(191) NOT NULL,
  `club_logo` TEXT DEFAULT NULL,
  `club_category` VARCHAR(100) NOT NULL,
  `club_type` VARCHAR(50) DEFAULT 'official',
  `applicant_user_id` INT DEFAULT NULL,
  `applicant_name` VARCHAR(191) NOT NULL,
  `applicant_email` VARCHAR(191) NOT NULL,
  `applicant_matric` VARCHAR(100) DEFAULT NULL,
  `faculty` VARCHAR(191) DEFAULT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `supporting_documents` TEXT DEFAULT NULL,
  `submission_notes` TEXT DEFAULT NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'pending',
  `reviewed_by` VARCHAR(100) DEFAULT NULL,
  `reviewed_at` DATETIME DEFAULT NULL,
  `decision_notes` TEXT DEFAULT NULL,
  `rejection_reason` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_ver_club_id` (`club_id`),
  INDEX `idx_ver_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Organizer Access Requests Table
CREATE TABLE IF NOT EXISTS `organizer_access_requests` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `club_id` INT NOT NULL,
  `club_name` VARCHAR(191) NOT NULL,
  `user_id` INT DEFAULT NULL,
  `applicant_name` VARCHAR(191) NOT NULL,
  `applicant_email` VARCHAR(191) NOT NULL,
  `applicant_matric` VARCHAR(100) DEFAULT NULL,
  `requested_role` VARCHAR(50) NOT NULL DEFAULT 'Club Organizer',
  `reason` TEXT DEFAULT NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'pending',
  `reviewed_by` VARCHAR(100) DEFAULT NULL,
  `reviewed_at` DATETIME DEFAULT NULL,
  `decision_reason` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_acc_club_id` (`club_id`),
  INDEX `idx_acc_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Club & Community Events Table
CREATE TABLE IF NOT EXISTS `club_events` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `club_id` INT DEFAULT NULL,
  `organizer_user_id` INT DEFAULT NULL,
  `organizer_name` VARCHAR(191) DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `cover_image_url` VARCHAR(500) DEFAULT NULL,
  `event_date` VARCHAR(50) DEFAULT NULL,
  `start_time` VARCHAR(50) DEFAULT '09:00',
  `end_time` VARCHAR(50) DEFAULT '17:00',
  `location` VARCHAR(255) DEFAULT NULL,
  `category` VARCHAR(100) DEFAULT 'General',
  `event_type` VARCHAR(50) DEFAULT 'official',
  `capacity` INT DEFAULT 100,
  `reg_open_date` VARCHAR(50) DEFAULT NULL,
  `reg_close_date` VARCHAR(50) DEFAULT NULL,
  `adab_points` INT DEFAULT 0,
  `objectives` TEXT DEFAULT NULL,
  `eligibility` VARCHAR(255) DEFAULT 'All UMPSA Students',
  `contact_person` VARCHAR(191) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `attendees_count` INT DEFAULT 0,
  `status` VARCHAR(50) DEFAULT 'published',
  `qr_attendance_token` VARCHAR(191) DEFAULT NULL,
  `qr_token_expires_at` VARCHAR(50) DEFAULT NULL,
  `configurable_fields` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_ev_club_id` (`club_id`),
  INDEX `idx_ev_type` (`event_type`),
  INDEX `idx_ev_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Event Registrations Table
CREATE TABLE IF NOT EXISTS `event_registrations` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `event_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `student_name` VARCHAR(191) NOT NULL,
  `student_email` VARCHAR(191) NOT NULL,
  `matric_id` VARCHAR(100) DEFAULT NULL,
  `faculty` VARCHAR(191) DEFAULT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `custom_answers` TEXT DEFAULT NULL,
  `status` VARCHAR(50) DEFAULT 'registered',
  `registered_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_ev_user` (`event_id`, `user_id`),
  INDEX `idx_reg_ev` (`event_id`),
  INDEX `idx_reg_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Event Attendance Table (Single authoritative record for QR and manual check-ins)
CREATE TABLE IF NOT EXISTS `event_attendance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `event_id` INT NOT NULL,
  `registration_id` INT DEFAULT NULL,
  `user_id` INT NOT NULL,
  `student_name` VARCHAR(191) NOT NULL,
  `matric_id` VARCHAR(100) DEFAULT NULL,
  `checkin_method` VARCHAR(50) DEFAULT 'qr',
  `checkin_by` VARCHAR(191) DEFAULT NULL,
  `manual_reason` TEXT DEFAULT NULL,
  `adab_points_awarded` INT DEFAULT 0,
  `checked_in_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_att_ev_user` (`event_id`, `user_id`),
  INDEX `idx_att_ev` (`event_id`),
  INDEX `idx_att_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Event Feedback Table
CREATE TABLE IF NOT EXISTS `event_feedback` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `event_id` INT NOT NULL,
  `user_id` INT DEFAULT NULL,
  `is_anonymous` TINYINT(1) DEFAULT 0,
  `overall_rating` INT NOT NULL DEFAULT 5,
  `organization_rating` INT DEFAULT 5,
  `venue_rating` INT DEFAULT 5,
  `what_went_well` TEXT DEFAULT NULL,
  `improvement_suggestions` TEXT DEFAULT NULL,
  `comments` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_fb_ev_user` (`event_id`, `user_id`),
  INDEX `idx_fb_ev` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Adab Points Ledger Table
CREATE TABLE IF NOT EXISTS `adab_points_ledger` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `event_id` INT DEFAULT NULL,
  `points` INT NOT NULL,
  `reason` VARCHAR(255) DEFAULT NULL,
  `awarded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_adab_user` (`user_id`),
  INDEX `idx_adab_ev` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Admin Audit Logs Table
CREATE TABLE IF NOT EXISTS `admin_audit_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `action` VARCHAR(100) NOT NULL,
  `target_type` VARCHAR(50) NOT NULL,
  `target_id` INT DEFAULT NULL,
  `target_name` VARCHAR(255) DEFAULT NULL,
  `admin_user` VARCHAR(100) NOT NULL DEFAULT 'admin',
  `details` TEXT DEFAULT NULL,
  `ip_address` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_audit_action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================================
-- Initial Seed Data: Authentic UMPSA Clubs, Societies & Administrative Demo
-- =========================================================================

INSERT IGNORE INTO `users` (`id`, `email`, `username`, `university`, `gender`, `full_name`, `matric_id`, `phone`, `faculty`, `password`, `role`) VALUES
(1, 'azfar@umpsa.edu.my', 'azfar_lead', 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'Male', 'Muhammad Azfar Irfan', 'CB210045', '+601123456789', 'Faculty of Computing', '$2y$10$wT8v1R5jB5G.yE4c0qKJeux0K1uS1/gQZc8iYxRlm39FwZsqhJ50G', 'student'),
(2, 'sarah@umpsa.edu.my', 'sarah_sec', 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'Female', 'Sarah Nadiah', 'CB210089', '+60129876543', 'Faculty of Computing', '$2y$10$wT8v1R5jB5G.yE4c0qKJeux0K1uS1/gQZc8iYxRlm39FwZsqhJ50G', 'student'),
(3, 'daniel@umpsa.edu.my', 'daniel_dev', 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'Male', 'Ahmad Daniel', 'CB22019', '+60174455667', 'Faculty of Computing', '$2y$10$wT8v1R5jB5G.yE4c0qKJeux0K1uS1/gQZc8iYxRlm39FwZsqhJ50G', 'student'),
(4, 'danial@umpsa.edu.my', 'danial_h', 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'Male', 'Danial Haziq', 'MH22019', '+60114433221', 'Faculty of Mechanical & Automotive Engineering Technology', '$2y$10$wT8v1R5jB5G.yE4c0qKJeux0K1uS1/gQZc8iYxRlm39FwZsqhJ50G', 'student'),
(5, 'lim@umpsa.edu.my', 'lim_wk', 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'Male', 'Lim Wei Kang', 'EE23011', '+60126655443', 'Faculty of Electrical & Electronics Engineering Technology', '$2y$10$wT8v1R5jB5G.yE4c0qKJeux0K1uS1/gQZc8iYxRlm39FwZsqhJ50G', 'student'),
(6, 'nurul@umpsa.edu.my', 'nurul_ain', 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'Female', 'Nurul Ain Binti Razak', 'FS22041', '+60133322110', 'Faculty of Industrial Sciences and Technology', '$2y$10$wT8v1R5jB5G.yE4c0qKJeux0K1uS1/gQZc8iYxRlm39FwZsqhJ50G', 'student'),
(7, 'shuhada@umpsa.edu.my', 'shuhada_c', 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'Female', 'Nur Shuhada', 'CS21094', '+60187766554', 'Faculty of Computing', '$2y$10$wT8v1R5jB5G.yE4c0qKJeux0K1uS1/gQZc8iYxRlm39FwZsqhJ50G', 'student'),
(8, 'hariz@umpsa.edu.my', 'hariz_k', 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'Male', 'Hariz Zulhilmi', 'MH21088', '+60195544332', 'Pusat Sukan UMPSA', '$2y$10$wT8v1R5jB5G.yE4c0qKJeux0K1uS1/gQZc8iYxRlm39FwZsqhJ50G', 'student');

INSERT IGNORE INTO `clubs` (`id`, `name`, `slug`, `category`, `club_type`, `faculty`, `short_description`, `full_description`, `logo_url`, `banner_url`, `objectives`, `mission`, `established_date`, `meeting_schedule`, `meeting_location`, `contact_email`, `contact_phone`, `website_url`, `social_media_links`, `membership_requirements`, `member_count`, `verification_status`, `publication_status`, `is_suspended`, `primary_organizer_id`, `primary_organizer_name`, `created_by_admin`) VALUES
(1, 'PETAKOM UMPSA', 'petakom-umpsa', 'Technology & Innovation', 'official', 'Faculty of Computing (FK)', 'Official student body of the Faculty of Computing at UMPSA, recognized as an MDEC Premier Digital Tech Club organizing HackathonX, CTF, and AWS bootcamps.', 'Persatuan Mahasiswa Teknologi Komputer (PETAKOM) serves as the primary representative student body for all undergraduate and postgraduate students in the Faculty of Computing (FK) at Universiti Malaysia Pahang Al-Sultan Abdullah. Established under the Student Affairs and Alumni Department (JHEPA), PETAKOM spearheads major technical hackathons, industry collaborations with MDEC, AWS, and IBM, professional software developer circles, and student welfare initiatives like WeCare.', 'uploads/logos/petakom.svg', 'https://images.unsplash.com/photo-1517694712202-14dd9538aa97?w=1000&auto=format&fit=crop&q=80', '1. Represent computing students.\n2. Organize international hackathons.\n3. Link with tech leaders.', 'To champion computing excellence and digital innovation.', '2004-06-15', 'Every Wednesday, 8:00 PM - 10:00 PM', 'Cyber Range Lab & Studio FK, Gambang Campus', 'petakom@umpsa.edu.my', '+6095492000', 'https://fk.umpsa.edu.my', '{"instagram":"@petakom.umpsa"}', 'All registered students of Faculty of Computing.', 450, 'verified', 'published', 0, 1, 'Muhammad Azfar Irfan', 1),
(2, 'MECHAPRO UMPSA', 'mechapro-umpsa', 'Engineering & Robotics', 'official', 'Faculty of Mechanical & Automotive Engineering Technology (FTKMA)', 'The official student society of FTKMA leading automotive design colloquiums, national formula racing prototypes, CAD/CAM workshops, and industrial engineering exchanges.', 'Mechanical Engineering Professional Student Club (MECHAPRO) represents the academic and technical aspirations of mechanical and automotive engineering students at UMPSA Pekan. MECHAPRO facilitates hands-on automotive engine prototyping, collaborative inter-faculty programs, and student technical competitions across Malaysia.', 'uploads/logos/mechapro.svg', 'https://images.unsplash.com/photo-1581092160607-ee22621dd758?w=1000&auto=format&fit=crop&q=80', '1. Advance mechanical prototyping.\n2. Participate in national formula student.\n3. Industrial benchmarking.', 'Cultivating world-class mechanical and automotive engineers.', '2008-04-12', 'Every Thursday, 8:30 PM', 'Automotive Lab, Block A, Pekan Campus', 'mechapro@umpsa.edu.my', '+6094245000', 'https://ftkma.umpsa.edu.my', '{"instagram":"@mechapro.umpsa"}', 'Enrolled FTKMA students.', 380, 'verified', 'published', 0, 4, 'Danial Haziq', 1),
(3, 'EEES UMPSA', 'eees-umpsa', 'Engineering & Robotics', 'official', 'Faculty of Electrical & Electronics Engineering Technology (FTKEE)', 'The premier electrical & electronics engineering society at UMPSA, spearheading Robocon robotics, IoT telemetry, power grid symposiums, and embedded firmware design.', 'Electrical & Electronics Engineering Society (EEES) unites students across electrical power, electronics, instrumentation, and telecommunications disciplines at UMPSA Pekan Campus.', 'uploads/logos/eees.svg', 'https://images.unsplash.com/photo-1516321318423-f06f85e504b3?w=1000&auto=format&fit=crop&q=80', '1. Advance smart power systems.\n2. Robocon preparation.\n3. IEEE linkages.', 'Fostering cutting-edge electronic innovation.', '2006-11-20', 'Alternate Tuesdays, 8:00 PM', 'Power & Electronics Lab, FTKEE, Pekan Campus', 'eees@umpsa.edu.my', '+6094246000', 'https://ftkee.umpsa.edu.my', '{"instagram":"@eees_umpsa"}', 'Open to FTKEE students.', 320, 'verified', 'published', 0, 5, 'Lim Wei Kang', 1),
(4, 'SMACH UMPSA', 'smach-umpsa', 'Engineering & Robotics', 'official', 'Faculty of Mechanical & Automotive Engineering Technology (FTKMA)', 'The manufacturing, mechatronics, and smart automation student society at UMPSA, championing Industry 4.0 CNC precision, PLC programming, and agricultural tech.', 'Society of Manufacturing and Mechatronics (SMACH) champions advanced manufacturing engineering, CNC operations, precision tooling, robotic kinematics, and industrial PLC control systems at UMPSA Pekan.', 'uploads/logos/smach.svg', 'https://images.unsplash.com/photo-1581092335397-9583fe92d232?w=1000&auto=format&fit=crop&q=80', '1. Industry 4.0 automation.\n2. PLC & pneumatic control systems.\n3. Sustainable engineering.', 'To be the hallmark of advanced manufacturing innovation.', '2010-08-14', 'Every Wednesday, 5:00 PM', 'Advanced Manufacturing Center, Pekan Campus', 'smach@umpsa.edu.my', '+6094245500', '', '{"instagram":"@smach_umpsa"}', 'Manufacturing & mechatronics students.', 210, 'verified', 'published', 0, 4, 'Danial Haziq', 1),
(5, 'SciTex FIST UMPSA', 'scitex-fist-umpsa', 'Academic & Professional', 'official', 'Faculty of Industrial Sciences and Technology (FIST)', 'The official student society for industrial chemistry, biotechnology, and applied physics undergraduates at UMPSA Gambang Campus.', 'Science, Industry & Technology Expert Society (SciTex FIST) represents all students enrolled in FIST at UMPSA Gambang Campus.', 'uploads/logos/scitex_fist.svg', 'https://images.unsplash.com/photo-1532094349884-543bc11b234d?w=1000&auto=format&fit=crop&q=80', '1. Promote applied science research.\n2. Lab safety standards.\n3. STEM school outreach.', 'Bridging scientific discovery with industrial application.', '2009-03-25', 'Every Monday, 8:00 PM', 'Science Complex Lecture Hall, Gambang Campus', 'scitex.fist@umpsa.edu.my', '+6095492400', 'https://fist.umpsa.edu.my', '{"instagram":"@scitexfist.umpsa"}', 'All FIST undergraduates and postgraduates.', 290, 'pending', 'published', 0, 6, 'Nurul Ain Binti Razak', 0),
(6, 'Kelab Kayak UMPSA', 'kelab-kayak-umpsa', 'Sports & Recreation', 'official', 'Pusat Sukan UMPSA (Sports Centre)', 'The official competitive water sports and kayak expedition club of UMPSA, competing in the Lenggong International Kayak Orienteering and regional regattas.', 'Kelab Kayak UMPSA represents the premier water sports society at UMPSA under Pusat Sukan UMPSA.', 'uploads/logos/kayak.svg', 'https://images.unsplash.com/photo-1544551763-46a013bb70d5?w=1000&auto=format&fit=crop&q=80', '1. Train athletes for MASUM & SUKIPT.\n2. River & lake expeditions.\n3. Water rescue certifications.', 'Cultivating resilience and athletic excellence.', '2012-05-18', 'Every Saturday & Sunday, 8:00 AM', 'Pusat Sukan & Tasik Kampus Gambang / Pekan', 'kayak@umpsa.edu.my', '+6095492020', 'https://sukan.umpsa.edu.my', '{"instagram":"@kayak_umpsa"}', 'All registered students passionate about paddling.', 125, 'pending', 'published', 0, 8, 'Hariz Zulhilmi', 0),
(7, 'Kelab In-Smartive UMPSA', 'kelab-in-smartive-umpsa', 'Leadership & Community', 'official', 'Centre for Human Sciences / JHEPA', 'Flagship peer leadership and youth transformation society established under JHEPA, renowned for high-impact rural youth transformation bootcamps.', 'Kelab In-Smartive UMPSA is one of the most prominent leadership bodies at UMPSA.', 'uploads/logos/insmartive.svg', 'https://images.unsplash.com/photo-1529156069898-49953e39b3ac?w=1000&auto=format&fit=crop&q=80', '1. Soft skills & peer leadership.\n2. National youth camps.\n3. Character building.', 'Transforming students into empathetic and inspiring leaders.', '2005-09-10', 'Every Tuesday, 8:30 PM', 'Dewan Serbaguna, Gambang Campus', 'insmartive@umpsa.edu.my', '+6095492200', 'https://jhepa.umpsa.edu.my', '{"instagram":"@insmartive_umpsa"}', 'Open to all university students.', 310, 'verified', 'published', 0, 2, 'Sarah Nadiah', 1),
(8, 'Kelab Penyayang UMPSA', 'kelab-penyayang-umpsa', 'Leadership & Community', 'official', 'Department of Student Affairs and Alumni (JHEPA)', 'Dedicated student welfare and humanitarian assistance association, managing campus food banks, welfare emergency aid, and blood donation drives.', 'Kelab Penyayang UMPSA serves as the caring heart of the university.', 'uploads/logos/penyayang.svg', 'https://images.unsplash.com/photo-1593113598332-cd288d649433?w=1000&auto=format&fit=crop&q=80', '1. Manage campus food bank.\n2. Flood relief missions.\n3. Student wellbeing.', 'Nurturing a compassionate and supportive university community.', '2007-02-14', 'Every Thursday, 5:30 PM', 'Student Activity Centre (PUSA), Pekan Campus', 'penyayang@umpsa.edu.my', '+6094245100', 'https://jhepa.umpsa.edu.my', '{"instagram":"@penyayang_umpsa"}', 'Open to all students.', 240, 'verified', 'published', 0, 2, 'Sarah Nadiah', 1),
(9, 'Kelab Sukarelawan Yayasan UMPSA', 'sukarelawan-yayasan-umpsa', 'Leadership & Community', 'official', 'Yayasan UMPSA (UMPSA Foundation)', 'The premier philanthropic student ambassador arm of Yayasan UMPSA, distributing education sponsorships and community development assistance.', 'Kelab Sukarelawan Yayasan UMPSA mobilizes passionate university scholars to give back to society.', 'uploads/logos/yayasan_umpsa.svg', 'https://images.unsplash.com/photo-1469571486292-0ba58a3f068b?w=1000&auto=format&fit=crop&q=80', '1. Coordinate charity campaigns.\n2. Asnaf student aid.\n3. Sustainable social projects.', 'Spreading hope, education, and benevolence across Malaysia.', '2016-10-01', 'Alternate Saturdays, 10:00 AM', 'Kompleks Yayasan UMPSA, Pekan Campus', 'yayasan@umpsa.edu.my', '+6094245050', 'https://yayasan.umpsa.edu.my', '{"instagram":"@yayasanumpsa"}', 'Open to all registered UMPSA students.', 195, 'verified', 'published', 0, 1, 'Muhammad Azfar Irfan', 1),
(10, 'Pekan Coding & AI Circle', 'pekan-coding-circle', 'Technology & Innovation', 'personal', 'Faculty of Computing', 'Informal student study group focused on open-source coding, competitive programming, and machine learning tutorials.', 'An informal student group at Pekan Campus exploring machine learning models and mobile application development.', 'https://images.unsplash.com/photo-1517694712202-14dd9538aa97?w=240&auto=format&fit=crop&q=80', 'https://images.unsplash.com/photo-1526374965328-7f61d4dc18c5?w=1000&auto=format&fit=crop&q=80', '1. Peer-to-peer coding.\n2. Leetcode practice.\n3. Study circles.', 'Sharing code and learning together.', '2026-03-01', 'Fridays, 4:00 PM', 'Library Discussion Room 3, Pekan Campus', 'daniel@umpsa.edu.my', '+60174455667', '', '{"telegram":"t.me/pekan_coding"}', 'Informal open circle.', 34, 'personal', 'published', 0, 3, 'Ahmad Daniel', 0);

-- =========================================================================
-- 12. Faculties Table & Seed Data
-- =========================================================================

CREATE TABLE IF NOT EXISTS `faculties` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `university` VARCHAR(255) DEFAULT 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)',
  `university_code` VARCHAR(50) DEFAULT 'UMPSA',
  `name` VARCHAR(191) NOT NULL,
  `code` VARCHAR(50) NOT NULL,
  `students_count` INT DEFAULT 1000,
  `icon` VARCHAR(100) DEFAULT 'school_rounded',
  `color` VARCHAR(50) DEFAULT '#4AB7B6',
  `description` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_faculty_uni` (`university_code`),
  INDEX `idx_faculty_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `faculties` (`id`, `university`, `university_code`, `name`, `code`, `students_count`, `icon`, `color`, `description`) VALUES
(1, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Computer Science & IT', 'FCSIT', 1420, 'computer_rounded', '#4AB7B6', 'Premier faculty specializing in Software Engineering, Artificial Intelligence, Cybersecurity, and Data Science.'),
(2, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Engineering', 'FOE', 2150, 'engineering_rounded', '#E53935', 'Leading engineering disciplines encompassing mechanical, electrical, chemical, and civil engineering.'),
(3, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Medicine', 'FOM', 850, 'medical_services_rounded', '#FDAA5D', 'Comprehensive medical and health sciences education with cutting-edge clinical laboratories.'),
(4, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Business & Accountancy', 'FBA', 1890, 'business_center_rounded', '#FF9800', 'Equipping future corporate leaders, entrepreneurs, and accountants with modern digital commerce skills.'),
(5, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Computing', 'FK', 1650, 'computer_rounded', '#2E8B8A', 'Home to PETAKOM and top-tier computing research in cloud computing, networking, and software engineering.'),
(6, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Mechanical & Automotive Engineering Technology', 'FTKMA', 1980, 'precision_manufacturing_rounded', '#D32F2F', 'Specialized automotive testbeds, formula student prototypes, and advanced materials engineering.'),
(7, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Electrical & Electronics Engineering Technology', 'FTKEE', 1420, 'bolt_rounded', '#F57C00', 'Center of excellence in robotics, semiconductor engineering, smart power systems, and IoT.'),
(8, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Chemical & Process Engineering Technology', 'FTKKP', 1120, 'science_rounded', '#7B1FA2', 'Sustainable energy research, chemical processing, biofuels, and biotechnology engineering.'),
(9, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Civil Engineering Technology', 'FTKA', 1250, 'architecture_rounded', '#1976D2', 'Sustainable structural design, smart infrastructure, and environmental engineering.'),
(10, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Industrial Sciences & Technology', 'FSTI', 890, 'biotech_rounded', '#388E3C', 'Applied sciences, chemistry, nanotechnology, and industrial data science.'),
(11, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Industrial Management', 'FIM', 1350, 'assessment_rounded', '#C2185B', 'Supply chain logistics, operations management, financial technology, and project analytics.'),
(12, 'Universiti Malaysia Pahang Al-Sultan Abdullah (UMPSA)', 'UMPSA', 'Faculty of Arts & Social Sciences', 'FASS', 1340, 'palette_rounded', '#E91E63', 'Liberal arts, communication, media studies, and humanities.');