--
-- ELS Staff Charter - Database Setup
-- Database: elsyxizi_csg
-- Run this in phpMyAdmin SQL tab
--

-- STEP 1: If old parent table exists, back it up
-- Uncomment the next line ONLY if you want to keep old parent data:
-- RENAME TABLE IF EXISTS `charter_consent_records` TO `charter_consent_records_backup_parent`;

-- STEP 2: Drop old table if it exists (WARNING: this deletes old data)
DROP TABLE IF EXISTS `charter_consent_records`;

-- STEP 3: Create new staff table
CREATE TABLE `charter_consent_records` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `reference` VARCHAR(30) DEFAULT NULL,
  `staff_name` VARCHAR(200) NOT NULL,
  `staff_ic` VARCHAR(50) NOT NULL,
  `staff_email` VARCHAR(200) NOT NULL,
  `staff_phone` VARCHAR(50) DEFAULT NULL,
  `staff_position` VARCHAR(100) NOT NULL,
  `staff_department` VARCHAR(100) NOT NULL,
  `date_of_joining` DATE NOT NULL,
  `emergency_contact` VARCHAR(100) DEFAULT NULL,
  `date_signed` DATE NOT NULL,
  `charter_version` VARCHAR(50) DEFAULT 'AY 2026-2027 Staff',
  `consent_enrolment` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `consent_policies` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `consent_fees` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `consent_attendance` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `consent_discipline` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `consent_medical` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `consent_photos` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `consent_fieldtrips` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `consent_data` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `consent_safeguarding` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `consent_communication` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `signature` VARCHAR(300) DEFAULT NULL,
  `printed_name` VARCHAR(300) DEFAULT NULL,
  `office_received_by` VARCHAR(200) DEFAULT NULL,
  `office_date_received` DATE DEFAULT NULL,
  `office_file_ref` VARCHAR(100) DEFAULT NULL,
  `office_staff_signature` VARCHAR(300) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `submitted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_staff` (`staff_ic`, `staff_email`),
  KEY `idx_reference` (`reference`),
  KEY `idx_position` (`staff_position`),
  KEY `idx_department` (`staff_department`),
  KEY `idx_submitted` (`submitted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;