-- SMART SCHOOL Beta 1.0 extension schema.
-- Run on staging first. Existing CMS tables are intentionally not altered.
-- Before adding FKs to existing students/users/parents, verify their exact key types.

CREATE TABLE IF NOT EXISTS ss_cards (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  existing_student_id BIGINT UNSIGNED NOT NULL,
  card_type ENUM('qr','barcode','nfc') NOT NULL,
  card_label VARCHAR(80) NOT NULL,
  credential_hash CHAR(64) NULL,
  nfc_uid VARCHAR(100) NULL,
  status ENUM('active','blocked','expired') NOT NULL DEFAULT 'active',
  issued_at DATETIME NULL,
  expires_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ss_card_label (card_label),
  UNIQUE KEY uq_ss_credential_hash (credential_hash),
  KEY idx_ss_card_student (existing_student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ss_devices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_code VARCHAR(50) NOT NULL,
  device_name VARCHAR(120) NOT NULL,
  device_type ENUM('android','barcode_usb','nfc_usb','nfc_gate','web_camera') NOT NULL,
  token_hash CHAR(64) NOT NULL,
  location_label VARCHAR(150) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_seen_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ss_device_code (device_code),
  UNIQUE KEY uq_ss_device_token (token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ss_attendance_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_name VARCHAR(100) NOT NULL,
  event_type ENUM('check_in','check_out') NOT NULL,
  starts_at TIME NOT NULL,
  ends_at TIME NOT NULL,
  late_after TIME NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ss_attendance_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transaction_uuid CHAR(36) NOT NULL,
  existing_student_id BIGINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NOT NULL,
  session_id BIGINT UNSIGNED NULL,
  method ENUM('qr','barcode','nfc','manual') NOT NULL,
  event_type ENUM('check_in','check_out') NOT NULL,
  scanned_at DATETIME NOT NULL,
  received_at DATETIME NOT NULL,
  result ENUM('success','duplicate','invalid_card','blocked','rejected') NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ss_attendance_uuid (transaction_uuid),
  KEY idx_ss_attendance_student_date (existing_student_id, scanned_at),
  KEY idx_ss_attendance_device_date (device_id, scanned_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ss_attendances (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  existing_student_id BIGINT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  check_in_at DATETIME NULL,
  check_out_at DATETIME NULL,
  attendance_status ENUM('present','late','sick','permission','absent') NOT NULL DEFAULT 'present',
  first_log_id BIGINT UNSIGNED NULL,
  last_log_id BIGINT UNSIGNED NULL,
  remarks VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_ss_student_day (existing_student_id, attendance_date),
  KEY idx_ss_attendance_date_status (attendance_date, attendance_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ss_wa_contacts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  existing_parent_id BIGINT UNSIGNED NULL,
  existing_student_id BIGINT UNSIGNED NOT NULL,
  phone_e164 VARCHAR(20) NOT NULL,
  contact_name VARCHAR(150) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ss_wa_phone (phone_e164),
  KEY idx_ss_wa_student (existing_student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ss_wa_consents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contact_id BIGINT UNSIGNED NOT NULL,
  consent_status ENUM('pending','approved','revoked') NOT NULL DEFAULT 'pending',
  consent_source VARCHAR(80) NULL,
  consented_at DATETIME NULL,
  revoked_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ss_wa_consent (contact_id, consent_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ss_wa_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  template_key VARCHAR(100) NOT NULL,
  meta_template_name VARCHAR(100) NOT NULL,
  language_code VARCHAR(10) NOT NULL DEFAULT 'id',
  event_type VARCHAR(80) NOT NULL,
  is_approved TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ss_wa_template_key (template_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ss_wa_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_uuid CHAR(36) NOT NULL,
  contact_id BIGINT UNSIGNED NOT NULL,
  template_id BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  payload JSON NOT NULL,
  status ENUM('pending','processing','accepted','failed','cancelled') NOT NULL DEFAULT 'pending',
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  scheduled_at DATETIME NOT NULL,
  locked_at DATETIME NULL,
  last_error VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ss_wa_event_contact (event_uuid, contact_id),
  KEY idx_ss_wa_queue_status_schedule (status, scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ss_wa_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  queue_id BIGINT UNSIGNED NULL,
  contact_id BIGINT UNSIGNED NOT NULL,
  provider_message_id VARCHAR(150) NULL,
  delivery_status ENUM('queued','accepted','sent','delivered','read','failed') NOT NULL DEFAULT 'queued',
  error_code VARCHAR(80) NULL,
  sent_at DATETIME NULL,
  delivered_at DATETIME NULL,
  read_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ss_wa_provider_message (provider_message_id),
  KEY idx_ss_wa_message_status (delivery_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ss_wa_webhook_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_event_key VARCHAR(190) NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  payload JSON NOT NULL,
  processed_at DATETIME NULL,
  received_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ss_wa_webhook_event (provider_event_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ss_audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_type VARCHAR(30) NOT NULL,
  actor_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id VARCHAR(80) NULL,
  details JSON NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ss_audit_entity (entity_type, entity_id),
  KEY idx_ss_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
