-- Biometric Staff Attendance System — full schema
-- InnoDB, utf8mb4. Every hyphenated identifier is backticked everywhere it is used in PHP.

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `departments` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `department-name` VARCHAR(150) UNIQUE NOT NULL,
    `is-active` TINYINT(1) DEFAULT 1,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `staff-roles` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `role-name` VARCHAR(100) UNIQUE NOT NULL,
    `is-system-role` TINYINT(1) DEFAULT 0,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `role-permissions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `role-id` INT NOT NULL,
    `permission-key` VARCHAR(100) NOT NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `role-permission-unique` (`role-id`, `permission-key`),
    CONSTRAINT `fk-role-permissions-role` FOREIGN KEY (`role-id`) REFERENCES `staff-roles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `staff-members` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `worker-code` VARCHAR(50) UNIQUE NOT NULL,
    `full-name` VARCHAR(150) NULL,
    `phone-number` VARCHAR(50) NULL,
    `email-address` VARCHAR(150) NULL,
    `department-id` INT NULL,
    `photo-path` VARCHAR(255) NULL,
    `portal-password-hash` VARCHAR(255) NULL,
    `date-hired` DATE NULL,
    `is-active` TINYINT(1) DEFAULT 1,
    `source` ENUM('dat-import','manual','device-auto') DEFAULT 'manual',
    `needs-worker-code-mapping` TINYINT(1) DEFAULT 0,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx-staff-department` (`department-id`),
    CONSTRAINT `fk-staff-department` FOREIGN KEY (`department-id`) REFERENCES `departments`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `admin-users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `staff-id` INT NULL,
    `username` VARCHAR(100) UNIQUE NOT NULL,
    `password-hash` VARCHAR(255) NOT NULL,
    `totp-secret` VARCHAR(64) NULL,
    `totp-enabled` TINYINT(1) DEFAULT 0,
    `role-id` INT NOT NULL,
    `is-active` TINYINT(1) DEFAULT 1,
    `last-login-at` DATETIME NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx-admin-role` (`role-id`),
    CONSTRAINT `fk-admin-staff` FOREIGN KEY (`staff-id`) REFERENCES `staff-members`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk-admin-role` FOREIGN KEY (`role-id`) REFERENCES `staff-roles`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `login-attempts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(100) NOT NULL,
    `ip-address` VARCHAR(45) NOT NULL,
    `was-successful` TINYINT(1) DEFAULT 0,
    `attempted-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    KEY `idx-login-attempts-username` (`username`),
    KEY `idx-login-attempts-time` (`attempted-at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `devices` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `serial-number` VARCHAR(100) UNIQUE NOT NULL,
    `device-label` VARCHAR(150) NULL,
    `ip-address` VARCHAR(45) NULL,
    `door-sensor-status` TINYINT(1) NULL,
    `last-seen-at` DATETIME NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `device-events` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `serial-number` VARCHAR(100) NULL,
    `event-route` VARCHAR(100) NULL,
    `method-name` VARCHAR(100) NULL,
    `ip-address` VARCHAR(45) NOT NULL,
    `raw-payload` LONGTEXT NOT NULL,
    `received-at` DATETIME NOT NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx-device-events-serial` (`serial-number`),
    KEY `idx-device-events-received` (`received-at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `attendance-logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `worker-code` VARCHAR(50) NOT NULL,
    `staff-id` INT NULL,
    `work-date` DATE NOT NULL,
    `clock-in-time` DATETIME NULL,
    `clock-out-time` DATETIME NULL,
    `clock-in-photo-path` VARCHAR(255) NULL,
    `clock-out-photo-path` VARCHAR(255) NULL,
    `raw-check-type-in` VARCHAR(10) NULL,
    `raw-check-type-out` VARCHAR(10) NULL,
    `status` ENUM('on-time','late','left-early','no-clockout','absent','holiday','on-leave') NULL,
    `device-serial` VARCHAR(100) NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `attendance-worker-date-unique` (`worker-code`, `work-date`),
    KEY `idx-attendance-staff` (`staff-id`),
    KEY `idx-attendance-date` (`work-date`),
    KEY `idx-attendance-status` (`status`),
    CONSTRAINT `fk-attendance-staff` FOREIGN KEY (`staff-id`) REFERENCES `staff-members`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `attendance-settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting-key` VARCHAR(100) NOT NULL,
    `setting-value` TEXT NULL,
    `department-id` INT NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `setting-key-department-unique` (`setting-key`, `department-id`),
    CONSTRAINT `fk-settings-department` FOREIGN KEY (`department-id`) REFERENCES `departments`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `holidays` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `holiday-date` DATE UNIQUE NOT NULL,
    `holiday-label` VARCHAR(150) NOT NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `leave-types` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `leave-type-name` VARCHAR(100) UNIQUE NOT NULL,
    `default-days-per-year` INT DEFAULT 0,
    `is-active` TINYINT(1) DEFAULT 1,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `leave-requests` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `staff-id` INT NOT NULL,
    `leave-type-id` INT NOT NULL,
    `start-date` DATE NOT NULL,
    `end-date` DATE NOT NULL,
    `reason` TEXT NULL,
    `status` ENUM('pending','approved','rejected','cancelled') DEFAULT 'pending',
    `reviewed-by` INT NULL,
    `reviewed-at` DATETIME NULL,
    `admin-comment` VARCHAR(500) NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx-leave-staff` (`staff-id`),
    KEY `idx-leave-status` (`status`),
    KEY `idx-leave-dates` (`start-date`, `end-date`),
    CONSTRAINT `fk-leave-staff` FOREIGN KEY (`staff-id`) REFERENCES `staff-members`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk-leave-type` FOREIGN KEY (`leave-type-id`) REFERENCES `leave-types`(`id`),
    CONSTRAINT `fk-leave-reviewer` FOREIGN KEY (`reviewed-by`) REFERENCES `admin-users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `memo-templates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `template-name` VARCHAR(150) NOT NULL,
    `template-type` ENUM('commendation','warning','custom') NOT NULL,
    `subject-line` VARCHAR(255) NOT NULL,
    `body-html` LONGTEXT NOT NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `sent-memos` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `staff-id` INT NOT NULL,
    `template-id` INT NOT NULL,
    `generated-by` INT NOT NULL,
    `period-start` DATE NOT NULL,
    `period-end` DATE NOT NULL,
    `rendered-body` LONGTEXT NOT NULL,
    `delivery-method` ENUM('download','email') NOT NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx-sent-memos-staff` (`staff-id`),
    CONSTRAINT `fk-sent-memos-staff` FOREIGN KEY (`staff-id`) REFERENCES `staff-members`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk-sent-memos-template` FOREIGN KEY (`template-id`) REFERENCES `memo-templates`(`id`),
    CONSTRAINT `fk-sent-memos-admin` FOREIGN KEY (`generated-by`) REFERENCES `admin-users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `notifications` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `notification-type` VARCHAR(100) NOT NULL,
    `message` VARCHAR(500) NOT NULL,
    `is-read` TINYINT(1) DEFAULT 0,
    `target-admin-id` INT NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx-notifications-target` (`target-admin-id`),
    KEY `idx-notifications-read` (`is-read`),
    CONSTRAINT `fk-notifications-admin` FOREIGN KEY (`target-admin-id`) REFERENCES `admin-users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `audit-log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `admin-id` INT NULL,
    `action` VARCHAR(150) NOT NULL,
    `details` TEXT NULL,
    `ip-address` VARCHAR(45) NOT NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx-audit-admin` (`admin-id`),
    KEY `idx-audit-created` (`created-at`),
    CONSTRAINT `fk-audit-admin` FOREIGN KEY (`admin-id`) REFERENCES `admin-users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `dat-imports` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `original-filename` VARCHAR(255) NOT NULL,
    `file-size` INT NOT NULL,
    `magic-header` VARCHAR(8) NULL,
    `raw-data` LONGBLOB NOT NULL,
    `new-staff-count` INT DEFAULT 0,
    `new-department-count` INT DEFAULT 0,
    `skipped-existing-count` INT DEFAULT 0,
    `uploaded-by` INT NOT NULL,
    `created-at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated-at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk-dat-imports-admin` FOREIGN KEY (`uploaded-by`) REFERENCES `admin-users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- Seed: system role with every permission
INSERT INTO `staff-roles` (`role-name`, `is-system-role`) VALUES ('Super Admin', 1);

-- Seed default admin — username: admin / password: ChangeMe123! (hash generated at install time by install.php, placeholder below is illustrative only)
-- Do NOT rely on this row; install.php inserts the real hashed row.

-- Seed attendance-settings defaults
INSERT INTO `attendance-settings` (`setting-key`, `setting-value`, `department-id`) VALUES
('clock-in-window-start', '05:00', NULL),
('clock-in-window-end', '08:00', NULL),
('clock-out-threshold', '16:00', NULL),
('working-days', 'mon,tue,wed,thu,fri', NULL),
('weekly-email-enabled', '0', NULL),
('weekly-email-day', 'mon', NULL),
('weekly-email-time', '07:00', NULL),
('org-name', '', NULL),
('org-logo-path', '', NULL);

-- Seed memo templates
INSERT INTO `memo-templates` (`template-name`, `template-type`, `subject-line`, `body-html`) VALUES
('Default Commendation', 'commendation', 'Commendation for {staff-name}',
'<p>Dear {staff-name},</p><p>We would like to commend you for your excellent attendance record during {period-label}. You achieved an attendance rate of {attendance-percentage}% and an on-time rate of {on-time-percentage}%.</p><p>Keep up the great work.</p><p>{today-date}</p>'),
('Default Warning', 'warning', 'Attendance Warning for {staff-name}',
'<p>Dear {staff-name},</p><p>Our records show that during {period-label} you were late {late-count} time(s) and absent {absent-count} time(s), for an attendance rate of {attendance-percentage}%.</p><p>Please treat this as a formal warning and ensure improved attendance going forward.</p><p>{today-date}</p>');

-- Seed leave types
INSERT INTO `leave-types` (`leave-type-name`, `default-days-per-year`) VALUES
('Annual Leave', 21), ('Sick Leave', 10), ('Unpaid Leave', 0), ('Compassionate Leave', 5);