-- ============================================================
-- Child Safeguarding Policy - Database Setup
-- Empower Learning System-Malaysia
-- Run this in phpMyAdmin (SQL tab)
-- ============================================================

-- Select database
USE elsyxizi_csg;

-- Drop old table if exists
DROP TABLE IF EXISTS consent_submissions;

-- Create table
CREATE TABLE consent_submissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference VARCHAR(20) DEFAULT NULL,

    -- Staff details
    full_name VARCHAR(200) NOT NULL,
    ic_number VARCHAR(50) NOT NULL,
    position VARCHAR(100) NOT NULL,
    department VARCHAR(100) NOT NULL,
    email VARCHAR(200) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    commencement_date DATE DEFAULT NULL,
    date_signed DATE NOT NULL,

    -- Section 1: Receipt and Reading (4 items)
    receipt_copy TINYINT(1) NOT NULL DEFAULT 0,
    read_policy TINYINT(1) NOT NULL DEFAULT 0,
    read_sops TINYINT(1) NOT NULL DEFAULT 0,
    read_scenarios TINYINT(1) NOT NULL DEFAULT 0,

    -- Section 2: Understanding of Obligations (5 items)
    understand_mandated TINYINT(1) NOT NULL DEFAULT 0,
    understand_failure TINYINT(1) NOT NULL DEFAULT 0,
    understand_boundaries TINYINT(1) NOT NULL DEFAULT 0,
    understand_reporting TINYINT(1) NOT NULL DEFAULT 0,
    understand_consequences TINYINT(1) NOT NULL DEFAULT 0,

    -- Section 3: Commitment to Compliance (6 items)
    commit_policy TINYINT(1) NOT NULL DEFAULT 0,
    commit_boundaries TINYINT(1) NOT NULL DEFAULT 0,
    commit_confidentiality TINYINT(1) NOT NULL DEFAULT 0,
    commit_cooperate TINYINT(1) NOT NULL DEFAULT 0,
    commit_training TINYINT(1) NOT NULL DEFAULT 0,
    commit_code TINYINT(1) NOT NULL DEFAULT 0,

    -- Section 4: Consent and Agreement (3 items)
    consent_abide TINYINT(1) NOT NULL DEFAULT 0,
    consent_bgcheck TINYINT(1) NOT NULL DEFAULT 0,
    consent_retention TINYINT(1) NOT NULL DEFAULT 0,

    -- Meta
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    -- Prevent duplicate submissions
    UNIQUE KEY unique_submission (ic_number, email)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Done! The table is ready to receive submissions.