-- ============================================================================
-- READS Unified System — single MySQL database
-- Attendance (kiosk/sync), evaluation portal, scholars, devices, content.
--
-- Engine: InnoDB   Charset: utf8mb4   Collation: utf8mb4_unicode_ci
--
-- phpMyAdmin: Import this file once. Creates database `reads_attendance`.
-- ============================================================================


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

-- ----------------------------------------------------------------------------
-- Shared org structure
-- ----------------------------------------------------------------------------
CREATE TABLE departments (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name            VARCHAR(150) NOT NULL,
  code            VARCHAR(30)  DEFAULT NULL,
  department_head VARCHAR(255) DEFAULT NULL,
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_departments_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE courses (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name           VARCHAR(150) NOT NULL,
  code           VARCHAR(30)  DEFAULT NULL,
  department_id  INT UNSIGNED DEFAULT NULL,
  created_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_courses_code (code),
  KEY idx_courses_department (department_id),
  CONSTRAINT fk_courses_department
    FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- Users — one table for portal accounts, scholars, and kiosk/sync profiles.
-- Columns not used by a given account type are NULL or empty.
--   role          — portal: staff, scholarship_head, admin, scholar
--   user_type     — kiosk: admin, employee, student
--   name          — portal display name; scholars may also use first_name/last_name
-- ----------------------------------------------------------------------------
CREATE TABLE users (
  user_id         VARCHAR(64)  NOT NULL,
  id_no           VARCHAR(50)  DEFAULT NULL,
  first_name      VARCHAR(100) DEFAULT NULL,
  last_name       VARCHAR(100) DEFAULT NULL,
  name            VARCHAR(100) DEFAULT NULL,
  user_type       ENUM('admin','employee','student') DEFAULT NULL,
  role            VARCHAR(20)  DEFAULT NULL,
  username        VARCHAR(100) DEFAULT NULL,
  password        VARCHAR(255) NOT NULL DEFAULT '',
  email           VARCHAR(190) DEFAULT NULL,
  department      VARCHAR(100) DEFAULT NULL,
  department_id   INT UNSIGNED DEFAULT NULL,
  course_id       INT UNSIGNED DEFAULT NULL,
  scholar_id      INT UNSIGNED DEFAULT NULL,
  status          ENUM('active','pending','inactive') DEFAULT NULL,
  date_indorsed   DATETIME     DEFAULT NULL,
  front_encoding  TEXT         DEFAULT NULL,
  left_encoding   TEXT         DEFAULT NULL,
  right_encoding  TEXT         DEFAULT NULL,
  profile_image   LONGTEXT     DEFAULT NULL,
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id),
  UNIQUE KEY uq_users_username (username),
  UNIQUE KEY uq_users_id_no (id_no),
  KEY idx_users_role (role),
  KEY idx_users_email (email),
  KEY idx_users_department_id (department_id),
  KEY idx_users_course (course_id),
  KEY idx_users_status (status),
  KEY idx_users_type (user_type),
  CONSTRAINT fk_users_department
    FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE SET NULL,
  CONSTRAINT fk_users_course
    FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- Scholarships & scholars
-- ----------------------------------------------------------------------------
CREATE TABLE scholarships (
  scholarship_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code           VARCHAR(30)  NOT NULL,
  name           VARCHAR(150) NOT NULL,
  layout_mode    ENUM('department','course') NOT NULL DEFAULT 'department',
  description    TEXT         DEFAULT NULL,
  image_url      LONGTEXT     DEFAULT NULL,
  active         TINYINT(1)   NOT NULL DEFAULT 1,
  created_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (scholarship_id),
  UNIQUE KEY uq_scholarships_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reads_scholars (
  scholar_id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  scholarship_id    INT UNSIGNED DEFAULT NULL,
  idno            VARCHAR(50)  NOT NULL,
  fname           VARCHAR(100) NOT NULL,
  lname           VARCHAR(100) NOT NULL,
  email           VARCHAR(190) DEFAULT NULL,
  account_user_id VARCHAR(50)  DEFAULT NULL,
  department_id   INT UNSIGNED NOT NULL,
  course          VARCHAR(100) NOT NULL,
  year            INT          NOT NULL,
  status          VARCHAR(20)  NOT NULL DEFAULT 'active',
  assigned_to     VARCHAR(50)  NOT NULL,
  assignment      VARCHAR(150) NOT NULL,
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (scholar_id),
  UNIQUE KEY uq_reads_scholars_idno (idno),
  KEY idx_reads_scholars_dept (department_id),
  KEY idx_reads_scholars_assigned (assigned_to),
  CONSTRAINT fk_reads_scholars_department
    FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE RESTRICT,
  CONSTRAINT fk_reads_scholars_scholarship
    FOREIGN KEY (scholarship_id) REFERENCES scholarships (scholarship_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE department_heads (
  head_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  fname          VARCHAR(100) NOT NULL,
  lname          VARCHAR(100) NOT NULL,
  department_id  INT UNSIGNED NOT NULL,
  created_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (head_id),
  UNIQUE KEY uq_department_heads_dept (department_id),
  CONSTRAINT fk_department_heads_department
    FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- Evaluation forms
-- ----------------------------------------------------------------------------

CREATE TABLE evaluation_forms (
  form_id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title        VARCHAR(255) NOT NULL,
  school_year  VARCHAR(50)  NOT NULL,
  semester     VARCHAR(50)  NOT NULL,
  description  TEXT         DEFAULT NULL,
  status       VARCHAR(20)  NOT NULL DEFAULT 'active',
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (form_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE evaluation_criteria (
  criterion_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  form_id      INT UNSIGNED NOT NULL,
  name         VARCHAR(100) NOT NULL,
  numeral      VARCHAR(10)  NOT NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (criterion_id),
  KEY idx_eval_criteria_form (form_id),
  CONSTRAINT fk_eval_criteria_form
    FOREIGN KEY (form_id) REFERENCES evaluation_forms (form_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE evaluation_questions (
  question_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  criterion_id  INT UNSIGNED NOT NULL,
  question_text TEXT         NOT NULL,
  order_num     INT          NOT NULL DEFAULT 0,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (question_id),
  KEY idx_eval_questions_criterion (criterion_id),
  CONSTRAINT fk_eval_questions_criterion
    FOREIGN KEY (criterion_id) REFERENCES evaluation_criteria (criterion_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE evaluation_ratings (
  rating_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  rating_value INT          NOT NULL,
  rating_label VARCHAR(50)  NOT NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (rating_id),
  UNIQUE KEY uq_eval_ratings_value (rating_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE evaluation_submissions (
  submission_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  scholar_id       INT UNSIGNED NOT NULL,
  evaluator_id     VARCHAR(50)  NOT NULL,
  form_id          INT UNSIGNED NOT NULL,
  total_mean       DECIMAL(4,2) DEFAULT NULL,
  overall_rating   DECIMAL(4,2) DEFAULT NULL,
  interpretation   VARCHAR(50)  DEFAULT NULL,
  status           VARCHAR(20)  NOT NULL DEFAULT 'draft',
  submitted_at     DATETIME     DEFAULT NULL,
  created_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (submission_id),
  KEY idx_eval_sub_scholar (scholar_id),
  KEY idx_eval_sub_evaluator (evaluator_id),
  CONSTRAINT fk_eval_sub_scholar
    FOREIGN KEY (scholar_id) REFERENCES reads_scholars (scholar_id) ON DELETE CASCADE,
  CONSTRAINT fk_eval_sub_evaluator
    FOREIGN KEY (evaluator_id) REFERENCES users (user_id) ON DELETE CASCADE,
  CONSTRAINT fk_eval_sub_form
    FOREIGN KEY (form_id) REFERENCES evaluation_forms (form_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE evaluation_answers (
  answer_id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  submission_id INT UNSIGNED NOT NULL,
  question_id   INT UNSIGNED NOT NULL,
  rating_value  INT          NOT NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (answer_id),
  KEY idx_eval_answers_submission (submission_id),
  CONSTRAINT fk_eval_answers_submission
    FOREIGN KEY (submission_id) REFERENCES evaluation_submissions (submission_id) ON DELETE CASCADE,
  CONSTRAINT fk_eval_answers_question
    FOREIGN KEY (question_id) REFERENCES evaluation_questions (question_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE evaluation_feedback (
  feedback_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  submission_id INT UNSIGNED NOT NULL,
  criterion_id  INT UNSIGNED NOT NULL,
  feedback_text TEXT         NOT NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (feedback_id),
  KEY idx_eval_feedback_submission (submission_id),
  CONSTRAINT fk_eval_feedback_submission
    FOREIGN KEY (submission_id) REFERENCES evaluation_submissions (submission_id) ON DELETE CASCADE,
  CONSTRAINT fk_eval_feedback_criterion
    FOREIGN KEY (criterion_id) REFERENCES evaluation_criteria (criterion_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- Portal content & notifications
-- ----------------------------------------------------------------------------
CREATE TABLE scholarship_content (
  content_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  type        ENUM('news','announcement','event','admission','scholarship') NOT NULL,
  title       VARCHAR(255) NOT NULL,
  slug        VARCHAR(255) NOT NULL,
  category    VARCHAR(100) NOT NULL,
  summary     TEXT         NOT NULL,
  body        TEXT         NOT NULL,
  image_url   MEDIUMTEXT   NOT NULL,
  event_date  DATE         DEFAULT NULL,
  event_time  VARCHAR(100) DEFAULT NULL,
  location    VARCHAR(255) DEFAULT NULL,
  status      ENUM('draft','published') NOT NULL DEFAULT 'draft',
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (content_id),
  UNIQUE KEY uq_scholarship_content_slug (slug),
  KEY idx_content_type_status (type, status),
  KEY idx_content_event_date (event_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notifications (
  notification_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title              VARCHAR(255) NOT NULL,
  body               TEXT         NOT NULL,
  created_by         VARCHAR(50)  NOT NULL,
  target_type        ENUM('all','department','specific') NOT NULL DEFAULT 'all',
  scholarship_id     INT UNSIGNED DEFAULT NULL,
  target_departments JSON         DEFAULT NULL,
  target_scholar_ids JSON         DEFAULT NULL,
  emailed            TINYINT(1)   NOT NULL DEFAULT 0,
  created_at         TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (notification_id),
  KEY idx_notifications_scholarship (scholarship_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notification_recipients (
  notification_id INT UNSIGNED NOT NULL,
  scholar_id      INT UNSIGNED NOT NULL,
  read_at         TIMESTAMP    NULL DEFAULT NULL,
  PRIMARY KEY (notification_id, scholar_id),
  KEY idx_nr_scholar (scholar_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
  audit_id    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_id    VARCHAR(50)  DEFAULT NULL,
  actor_role  VARCHAR(30)  DEFAULT NULL,
  actor_name  VARCHAR(100) DEFAULT NULL,
  action      VARCHAR(80)  NOT NULL,
  entity_type VARCHAR(50)  DEFAULT NULL,
  entity_id   VARCHAR(80)  DEFAULT NULL,
  details     JSON         DEFAULT NULL,
  ip_address  VARCHAR(45)  DEFAULT NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (audit_id),
  KEY idx_audit_actor (actor_id),
  KEY idx_audit_action (action),
  KEY idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- Attendance (kiosk sync)
-- ----------------------------------------------------------------------------
CREATE TABLE attendance (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  local_id    VARCHAR(128) NOT NULL,
  user_id     VARCHAR(64)  NOT NULL,
  slot        ENUM('time_in_morning','time_out_morning',
                  'time_in_afternoon','time_out_afternoon') NOT NULL,
  logged_at   DATETIME     NOT NULL,
  synced_at   DATETIME     DEFAULT NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  attend_date DATE AS (DATE(logged_at)) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY uq_attendance_local_id (local_id),
  UNIQUE KEY uq_attendance_user_slot_day (user_id, slot, attend_date),
  KEY idx_attendance_user (user_id),
  KEY idx_attendance_logged_at (logged_at),
  CONSTRAINT fk_attendance_user
    FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
  `key`      VARCHAR(100) NOT NULL,
  `value`    TEXT         DEFAULT NULL,
  updated_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE registered_devices (
  device_id      VARCHAR(64)  NOT NULL,
  device_name    VARCHAR(150) NOT NULL,
  api_key        VARCHAR(128) NOT NULL,
  status         ENUM('active','revoked') NOT NULL DEFAULT 'active',
  registered_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at     TIMESTAMP    NULL DEFAULT NULL,
  last_seen_at   TIMESTAMP    NULL DEFAULT NULL,
  created_by     VARCHAR(50)  DEFAULT NULL,
  PRIMARY KEY (device_id),
  UNIQUE KEY uq_devices_api_key (api_key),
  KEY idx_devices_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- Seed data
-- ============================================================================
INSERT INTO departments (id, name, code) VALUES
  (1, 'College of Computer Studies', 'CCS', NULL),
  (2, 'College of Business',         'COB', NULL),
  (3, 'College of Education',        'COE', NULL),
  (4, 'Administration',              'ADMIN', NULL)
ON DUPLICATE KEY UPDATE name = VALUES(name), code = VALUES(code);

INSERT INTO courses (id, name, code, department_id) VALUES
  (1, 'BS Information Technology',  'BSIT', 1),
  (2, 'BS Computer Science',        'BSCS', 1),
  (3, 'BS Business Administration', 'BSBA', 2),
  (4, 'BS Secondary Education',     'BSEd', 3)
ON DUPLICATE KEY UPDATE name = VALUES(name), code = VALUES(code), department_id = VALUES(department_id);

INSERT INTO scholarships (code, name, layout_mode) VALUES
  ('reads',   'READS Scholarship',             'department'),
  ('heart',   'HEART Scholarship',             'course'),
  ('esp',     'Educational Support Program',   'course'),
  ('horizon', 'Horizon Grant',                 'course')
ON DUPLICATE KEY UPDATE name = VALUES(name), layout_mode = VALUES(layout_mode);

-- Portal demo logins (plaintext equivalents: admin123, head123, staff123) — stored as bcrypt.
INSERT INTO users (user_id, password, name, first_name, last_name, role, department, email) VALUES
  ('admin001', '$2y$10$bE6ovcCa4NKfwyuO8iAX0.5PeCJmXmnt1qdW0ve1EHNtsPanX5oPy', 'System Administrator', 'System', 'Administrator', 'admin', 'ICT', 'admin@usjr.edu.ph'),
  ('head001',  '$2y$10$eNx8sncEv6SnRtCMii4RVetJZMaCcn/xqv2cwDUAO8nF/lSLOyGZO', 'Dr. Maria Santos', 'Dr. Maria', 'Santos', 'scholarship_head', 'Scholarship Center', NULL),
  ('staff001', '$2y$10$SiqdNfN7.eyYnPUeasTD..B7CYWK2p73NyFTMjkTcab4OwLcLXtGW', 'John Reyes', 'John', 'Reyes', 'staff', 'Library', NULL),
  ('staff002', '$2y$10$SiqdNfN7.eyYnPUeasTD..B7CYWK2p73NyFTMjkTcab4OwLcLXtGW', 'Anna Cruz', 'Anna', 'Cruz', 'staff', 'Registrar', NULL)
ON DUPLICATE KEY UPDATE name = VALUES(name), first_name = VALUES(first_name), last_name = VALUES(last_name), role = VALUES(role);

INSERT INTO reads_scholars (idno, fname, lname, department_id, course, year, status, assigned_to, assignment, scholarship_id) VALUES
  ('SCH-2024-001', 'Juan',   'Dela Cruz',  1, 'BS Information Technology',  2, 'active', 'staff001', 'Library Aide',       (SELECT scholarship_id FROM scholarships WHERE code = 'reads' LIMIT 1)),
  ('SCH-2024-002', 'Maria',  'Garcia',     1, 'BS Computer Science',        1, 'active', 'staff001', 'Library Aide',       (SELECT scholarship_id FROM scholarships WHERE code = 'reads' LIMIT 1)),
  ('SCH-2024-003', 'Pedro',  'Santos',     1, 'BS Information Technology',  3, 'active', 'staff001', 'Circulation Desk',   (SELECT scholarship_id FROM scholarships WHERE code = 'reads' LIMIT 1)),
  ('SCH-2024-004', 'Ana',    'Reyes',      2, 'BS Business Administration', 2, 'active', 'staff002', 'Records Clerk',      (SELECT scholarship_id FROM scholarships WHERE code = 'reads' LIMIT 1)),
  ('SCH-2024-005', 'Carlos', 'Bautista',   2, 'BS Accountancy',             1, 'active', 'staff002', 'Records Clerk',      (SELECT scholarship_id FROM scholarships WHERE code = 'reads' LIMIT 1)),
  ('SCH-2024-006', 'Sofia',  'Mendoza',    4, 'BS Psychology',              2, 'active', 'head001',  'Administrative Aide', (SELECT scholarship_id FROM scholarships WHERE code = 'reads' LIMIT 1)),
  ('SCH-2024-007', 'Marco',  'Villanueva', 4, 'BS Industrial Engineering',  1, 'active', 'head001',  'Administrative Aide', (SELECT scholarship_id FROM scholarships WHERE code = 'reads' LIMIT 1))
ON DUPLICATE KEY UPDATE fname = VALUES(fname), lname = VALUES(lname), status = VALUES(status);

INSERT INTO users
  (user_id, id_no, first_name, last_name, user_type, username, password, email,
   department_id, course_id, status, date_indorsed)
VALUES
  ('admin',  'ADMIN-001', 'System', 'Administrator', 'admin',   'admin',     '$2y$10$bE6ovcCa4NKfwyuO8iAX0.5PeCJmXmnt1qdW0ve1EHNtsPanX5oPy', NULL, 4, NULL, 'active', NOW()),
  ('emp001', '2024-0001', 'Juan',   'Dela Cruz',     'student', 'jdelacruz', '$2y$10$.jBh70cQCZGIpMR.oTlJE.H0q.AHahl1hwwJ1PbHXWRP70IPNhohe', NULL, 1, 1,    'active', NOW()),
  ('emp002', '2024-0002', 'Maria',  'Santos',        'student', 'msantos',   '$2y$10$.jBh70cQCZGIpMR.oTlJE.H0q.AHahl1hwwJ1PbHXWRP70IPNhohe', NULL, 1, 2,    'active', NOW())
ON DUPLICATE KEY UPDATE
  first_name = VALUES(first_name), last_name = VALUES(last_name),
  user_type = VALUES(user_type), status = VALUES(status);

INSERT INTO settings (`key`, `value`) VALUES
  ('timezone',  'Asia/Manila'),
  ('device_id', 'LOCAL-KIOSK-01'),
  ('api_key',   'local_api_key_123456')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

INSERT INTO registered_devices (device_id, device_name, api_key, status, created_by)
VALUES ('LOCAL-KIOSK-01', 'Local Dev Kiosk', 'local_api_key_123456', 'active', 'system')
ON DUPLICATE KEY UPDATE device_name = VALUES(device_name);

INSERT INTO evaluation_forms (form_id, title, school_year, semester, description, status)
VALUES (1, 'READS Work Performance Evaluation', 'AY 2025-2026', 'Second Semester', 'Evaluate work performance of READS scholars', 'active')
ON DUPLICATE KEY UPDATE title = VALUES(title);

INSERT INTO evaluation_ratings (rating_value, rating_label) VALUES
  (5, 'Excellent'),
  (4, 'Above Average'),
  (3, 'Average'),
  (2, 'Below Average'),
  (1, 'Poor')
ON DUPLICATE KEY UPDATE rating_label = VALUES(rating_label);

CREATE OR REPLACE VIEW v_attendance_daily AS
SELECT
  a.user_id,
  DATE(a.logged_at) AS attend_date,
  MAX(CASE WHEN a.slot = 'time_in_morning'    THEN a.logged_at END) AS time_in_morning,
  MAX(CASE WHEN a.slot = 'time_out_morning'   THEN a.logged_at END) AS time_out_morning,
  MAX(CASE WHEN a.slot = 'time_in_afternoon'  THEN a.logged_at END) AS time_in_afternoon,
  MAX(CASE WHEN a.slot = 'time_out_afternoon' THEN a.logged_at END) AS time_out_afternoon
FROM attendance a
GROUP BY a.user_id, DATE(a.logged_at);
