-- ===================================================================== -- SANGGRALOKA ONE — Migration 0008_hr_init -- Modul CREW (HotelCrew / HRIS + Hotel University + Payroll + Ops). -- Semua tabel prefix hr_ + audit_logs (append-only, kedua modul) + -- auth_stepup (zona merah payroll) + ALTER users. -- Target: MySQL 8 — ENGINE=InnoDB, utf8mb4_unicode_ci. -- Acuan: hotelcrew/docs/SPEK-HOTELCREW-MASTER.md §4 (kontrak nama kolom), -- sanggraloka-one/docs/PM-PLAN-SANGGRALOKA-ONE.md §4. -- Jalankan setelah 0001..0007. Referensi ke users/user_property_access/ -- notifications/skills/asset_categories dari 0001_init.sql. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 1; SET default_storage_engine = InnoDB; -- ===================================================================== -- 1. MASTER KARYAWAN & DOKUMEN (F1) -- ===================================================================== -- --------------------------------------------------------------------- -- hr_positions — dibuat sebelum hr_employees (FK position_id) -- --------------------------------------------------------------------- CREATE TABLE hr_positions ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, name VARCHAR(150) NOT NULL, department VARCHAR(60) NOT NULL, level ENUM('staff','coordinator','hod','manager','director') NOT NULL DEFAULT 'staff', jobdesc_code VARCHAR(30) NULL COMMENT 'kode jobdesk mis. HK-RA-01 (dokumen versi di hotelcrew/docs/jobdesk)', jobdesc_version INT UNSIGNED NULL, min_wage_grade VARCHAR(30) NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_positions_property_dept (property_id, department), CONSTRAINT fk_hr_positions_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_employees -- Kolom nominal/rekening sensitif (bank_account) = TEXT ciphertext, -- dienkripsi level aplikasi (§3.2 SPEK-HOTELCREW zona merah). -- --------------------------------------------------------------------- CREATE TABLE hr_employees ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NULL, nik VARCHAR(20) NULL COMMENT 'nomor induk karyawan internal', full_name VARCHAR(150) NOT NULL, photo VARCHAR(255) NULL, ktp_no VARCHAR(20) NULL, npwp VARCHAR(25) NULL, bpjs_kes_no VARCHAR(30) NULL, bpjs_tk_no VARCHAR(30) NULL, ptkp_status VARCHAR(10) NULL COMMENT 'TK/0, K/1, dst', address VARCHAR(255) NULL, domicile VARCHAR(150) NULL, banjar VARCHAR(100) NULL, religion VARCHAR(30) NULL, birth_date DATE NULL, marital ENUM('single','married','divorced','widowed') NULL, emergency_contact VARCHAR(150) NULL, department VARCHAR(60) NOT NULL, division VARCHAR(60) NULL, position_id INT UNSIGNED NULL, level VARCHAR(30) NULL, employment_status ENUM('dw','contract','permanent','casual','trainee') NOT NULL DEFAULT 'contract', join_date DATE NULL, contract_end DATE NULL, supervisor_id INT UNSIGNED NULL, bank_account TEXT NULL COMMENT 'ciphertext — nomor rekening & bank, enkripsi level aplikasi', salary_grade VARCHAR(30) NULL, sc_point DECIMAL(10,2) NULL COMMENT 'poin service charge', uniform_size VARCHAR(10) NULL, locker_no VARCHAR(20) NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_employees_property_nik (property_id, nik), KEY idx_hr_employees_property_dept (property_id, department), KEY idx_hr_employees_user (user_id), KEY idx_hr_employees_position (position_id), KEY idx_hr_employees_supervisor (supervisor_id), CONSTRAINT fk_hr_employees_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_employees_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_hr_employees_position FOREIGN KEY (position_id) REFERENCES hr_positions(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_hr_employees_supervisor FOREIGN KEY (supervisor_id) REFERENCES hr_employees(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- FK susulan: hr_positions tidak mereferensikan hr_competencies di sini -- (required_competencies disimpan sbg JSON kode kompetensi, bukan FK, -- karena banyak-ke-banyak dgn level per kompetensi — lihat hr_employee_comp). ALTER TABLE hr_positions ADD COLUMN required_competencies JSON NULL COMMENT 'array kode kompetensi wajib mis. ["HK-01","HK-18"]' AFTER jobdesc_version; -- --------------------------------------------------------------------- -- hr_documents -- --------------------------------------------------------------------- CREATE TABLE hr_documents ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, employee_id INT UNSIGNED NOT NULL, type ENUM('ktp','ijazah','sertifikat','kontrak','mcu','kitas','lainnya') NOT NULL, title VARCHAR(150) NOT NULL, file VARCHAR(255) NOT NULL, expiry_date DATE NULL, reminder_days SMALLINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_documents_employee (employee_id), KEY idx_hr_documents_expiry (expiry_date), CONSTRAINT fk_hr_documents_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_contracts -- --------------------------------------------------------------------- CREATE TABLE hr_contracts ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, employee_id INT UNSIGNED NOT NULL, type ENUM('pkwt','pkwtt') NOT NULL, start_date DATE NOT NULL, end_date DATE NULL, file VARCHAR(255) NULL, compensation_paid DECIMAL(14,2) NULL COMMENT 'kompensasi PKWT (1 bulan upah / 12 bln masa kerja, prorata)', status ENUM('draft','active','expired','terminated') NOT NULL DEFAULT 'draft', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_contracts_employee (employee_id), KEY idx_hr_contracts_end_date (end_date), CONSTRAINT fk_hr_contracts_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 2. ABSENSI, SHIFT, ROSTER, CUTI (F2, F3, F4) -- ===================================================================== -- --------------------------------------------------------------------- -- hr_attendance_logs — immutable (mesin/mobile-gps) -- --------------------------------------------------------------------- CREATE TABLE hr_attendance_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NOT NULL, machine_id VARCHAR(60) NULL, ts DATETIME NOT NULL, source ENUM('machine','mobile_gps') NOT NULL DEFAULT 'machine', photo VARCHAR(255) NULL, lat DECIMAL(10,7) NULL, lng DECIMAL(10,7) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_attendance_logs_property_employee_ts (property_id, employee_id, ts), CONSTRAINT fk_hr_attendance_logs_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_attendance_logs_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_shifts -- --------------------------------------------------------------------- CREATE TABLE hr_shifts ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, department VARCHAR(60) NOT NULL, code ENUM('M','A','N','M2','OFF','PH') NOT NULL, start_time TIME NULL, end_time TIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_shifts_property_dept_code (property_id, department, code), KEY idx_hr_shifts_property_dept (property_id, department), CONSTRAINT fk_hr_shifts_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_attendance_days -- --------------------------------------------------------------------- CREATE TABLE hr_attendance_days ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NOT NULL, date DATE NOT NULL, shift_code ENUM('M','A','N','M2','OFF','PH') NULL, time_in DATETIME NULL, time_out DATETIME NULL, late_min INT UNSIGNED NOT NULL DEFAULT 0, early_min INT UNSIGNED NOT NULL DEFAULT 0, overtime_min INT UNSIGNED NULL COMMENT 'approved', status ENUM('present','late','alpha','leave','off','holiday','incomplete') NOT NULL DEFAULT 'present', dispute_status ENUM('none','submitted','approved','rejected') NOT NULL DEFAULT 'none', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_attendance_days_employee_date (employee_id, date), KEY idx_hr_attendance_days_property_date (property_id, date), CONSTRAINT fk_hr_attendance_days_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_attendance_days_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_roster -- --------------------------------------------------------------------- CREATE TABLE hr_roster ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NOT NULL, date DATE NOT NULL, shift_id INT UNSIGNED NOT NULL, published_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_roster_employee_date (employee_id, date), KEY idx_hr_roster_property_date (property_id, date), KEY idx_hr_roster_shift (shift_id), CONSTRAINT fk_hr_roster_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_roster_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_roster_shift FOREIGN KEY (shift_id) REFERENCES hr_shifts(id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_leave_types -- --------------------------------------------------------------------- CREATE TABLE hr_leave_types ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, quota SMALLINT UNSIGNED NULL COMMENT 'hari/tahun, NULL = tanpa kuota tetap', requires_doc TINYINT(1) NOT NULL DEFAULT 0, requires_gm TINYINT(1) NOT NULL DEFAULT 0, paid TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_leave_types_property (property_id), CONSTRAINT fk_hr_leave_types_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_leaves -- --------------------------------------------------------------------- CREATE TABLE hr_leaves ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NOT NULL, type_id INT UNSIGNED NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, reason VARCHAR(255) NULL, doc_file VARCHAR(255) NULL, status ENUM('draft','submitted','approved_spv','approved_hod','approved_hr','approved_gm','rejected','cancelled') NOT NULL DEFAULT 'draft', approvals JSON NULL COMMENT '{spv:{by,at}, hod:{...}, hr:{...}, gm:{...}}', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_leaves_property_status (property_id, status), KEY idx_hr_leaves_employee (employee_id), KEY idx_hr_leaves_type (type_id), CONSTRAINT fk_hr_leaves_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_leaves_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_leaves_type FOREIGN KEY (type_id) REFERENCES hr_leave_types(id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_shift_swaps -- --------------------------------------------------------------------- CREATE TABLE hr_shift_swaps ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, requester_id INT UNSIGNED NOT NULL, partner_id INT UNSIGNED NOT NULL, date DATE NOT NULL, status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending', approvals JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_shift_swaps_property_date (property_id, date), KEY idx_hr_shift_swaps_requester (requester_id), KEY idx_hr_shift_swaps_partner (partner_id), CONSTRAINT fk_hr_shift_swaps_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_shift_swaps_requester FOREIGN KEY (requester_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_shift_swaps_partner FOREIGN KEY (partner_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 3. HOTEL UNIVERSITY & KOMPETENSI (F5, F6, F7) -- ===================================================================== -- --------------------------------------------------------------------- -- hr_faculties -- --------------------------------------------------------------------- CREATE TABLE hr_faculties ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, code VARCHAR(10) NOT NULL COMMENT 'GEN, FO, HK, FBS, BAR, KIT, ENG, SPA, WEL, SEC, GRD, SLS, ADM, LDR', name VARCHAR(150) NOT NULL, head_user_id INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_faculties_property_code (property_id, code), KEY idx_hr_faculties_head (head_user_id), CONSTRAINT fk_hr_faculties_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_faculties_head FOREIGN KEY (head_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_competencies -- --------------------------------------------------------------------- CREATE TABLE hr_competencies ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, code VARCHAR(20) NOT NULL COMMENT 'kode {PREFIX}-{nn} mis. HK-01, GEN-18', name VARCHAR(150) NOT NULL, tier ENUM('core','functional','leadership') NOT NULL DEFAULT 'functional', faculty_id INT UNSIGNED NOT NULL, asset_category_id INT UNSIGNED NULL COMMENT 'NULL -> tidak sync ke skills FIX', max_level TINYINT UNSIGNED NOT NULL DEFAULT 4 COMMENT '1 Tahu, 2 Bisa dgn supervisi, 3 Mandiri, 4 Trainer', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_competencies_property_code (property_id, code), KEY idx_hr_competencies_faculty (faculty_id), KEY idx_hr_competencies_asset_category (asset_category_id), CONSTRAINT fk_hr_competencies_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_competencies_faculty FOREIGN KEY (faculty_id) REFERENCES hr_faculties(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_hr_competencies_asset_category FOREIGN KEY (asset_category_id) REFERENCES asset_categories(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_employee_comp -- --------------------------------------------------------------------- CREATE TABLE hr_employee_comp ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, employee_id INT UNSIGNED NOT NULL, competency_id INT UNSIGNED NOT NULL, level TINYINT UNSIGNED NOT NULL DEFAULT 1, assessed_by INT UNSIGNED NULL, assessed_at DATETIME NULL, evidence_type ENUM('test','practical','cert') NULL, evidence_ref_id BIGINT UNSIGNED NULL COMMENT 'test_id atau dokumen sertifikat', valid_until DATE NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_employee_comp (employee_id, competency_id), KEY idx_hr_employee_comp_competency (competency_id), KEY idx_hr_employee_comp_assessed_by (assessed_by), CONSTRAINT fk_hr_employee_comp_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_employee_comp_competency FOREIGN KEY (competency_id) REFERENCES hr_competencies(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_employee_comp_assessed_by FOREIGN KEY (assessed_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_courses -- --------------------------------------------------------------------- CREATE TABLE hr_courses ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, faculty_id INT UNSIGNED NOT NULL, competency_id INT UNSIGNED NULL, title VARCHAR(150) NOT NULL, week_of DATE NULL, deadline DATE NULL, materials JSON NULL COMMENT 'array {type: video|article|card, url/ref}', mandatory_departments JSON NULL COMMENT 'array nama departemen wajib, NULL = semua', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_courses_property_faculty (property_id, faculty_id), KEY idx_hr_courses_competency (competency_id), CONSTRAINT fk_hr_courses_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_courses_faculty FOREIGN KEY (faculty_id) REFERENCES hr_faculties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_courses_competency FOREIGN KEY (competency_id) REFERENCES hr_competencies(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_tests -- --------------------------------------------------------------------- CREATE TABLE hr_tests ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, course_id INT UNSIGNED NULL, competency_id INT UNSIGNED NULL, title VARCHAR(150) NOT NULL, question_bank JSON NOT NULL COMMENT 'array soal {q, options[], answer, ...}', pass_grade TINYINT UNSIGNED NOT NULL DEFAULT 85, time_limit_minutes SMALLINT UNSIGNED NULL, max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 3, type ENUM('course','entrance','certification','placement') NOT NULL DEFAULT 'course', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_tests_property (property_id), KEY idx_hr_tests_course (course_id), KEY idx_hr_tests_competency (competency_id), CONSTRAINT fk_hr_tests_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_tests_course FOREIGN KEY (course_id) REFERENCES hr_courses(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_tests_competency FOREIGN KEY (competency_id) REFERENCES hr_competencies(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_test_attempts — user_id/candidate_id: pakai users.id; kandidat tanpa -- akun direferensikan via hr_candidates.id di kolom candidate_id terpisah. -- --------------------------------------------------------------------- CREATE TABLE hr_test_attempts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, test_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NULL, candidate_id INT UNSIGNED NULL COMMENT '-> hr_candidates(id) bila test kandidat (FK ditambah setelah hr_candidates dibuat)', score DECIMAL(5,2) NULL, answers JSON NULL, passed TINYINT(1) NULL, ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_test_attempts_test (test_id), KEY idx_hr_test_attempts_user (user_id), KEY idx_hr_test_attempts_candidate (candidate_id), CONSTRAINT fk_hr_test_attempts_test FOREIGN KEY (test_id) REFERENCES hr_tests(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_test_attempts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 4. OPS HARIAN: MOD, GUEST CARDS, BRIEFING, TASK, AI INTAKE (F15, F16) -- ===================================================================== -- --------------------------------------------------------------------- -- hr_mod_roster -- --------------------------------------------------------------------- CREATE TABLE hr_mod_roster ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, date DATE NOT NULL, shift ENUM('day','night') NOT NULL, user_id INT UNSIGNED NOT NULL, set_by INT UNSIGNED NULL COMMENT 'HRD', published_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_mod_roster_property_date_shift (property_id, date, shift), KEY idx_hr_mod_roster_user (user_id), CONSTRAINT fk_hr_mod_roster_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_mod_roster_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_mod_roster_set_by FOREIGN KEY (set_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_guest_imports -- --------------------------------------------------------------------- CREATE TABLE hr_guest_imports ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, date DATE NOT NULL, file VARCHAR(255) NOT NULL, imported_by INT UNSIGNED NULL, rows_arrival INT UNSIGNED NOT NULL DEFAULT 0, rows_inhouse INT UNSIGNED NOT NULL DEFAULT 0, rows_departure INT UNSIGNED NOT NULL DEFAULT 0, parsed_at DATETIME NULL, ai_cost_idr DECIMAL(12,2) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_guest_imports_property_date (property_id, date), CONSTRAINT fk_hr_guest_imports_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_guest_imports_imported_by FOREIGN KEY (imported_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_guest_cards -- --------------------------------------------------------------------- CREATE TABLE hr_guest_cards ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, import_id INT UNSIGNED NOT NULL, date DATE NOT NULL, section ENUM('arrival','inhouse','departure') NOT NULL, room VARCHAR(20) NULL, room_type VARCHAR(80) NULL, guest_name VARCHAR(160) NOT NULL, pax SMALLINT UNSIGNED NULL, ci DATE NULL, co DATE NULL, vip ENUM('none','vip1','vip2','vip3') NOT NULL DEFAULT 'none', source VARCHAR(60) NULL, language_hint VARCHAR(10) NULL COMMENT 'FR/KR/EN/ID dst', occasion VARCHAR(100) NULL, family_rooms JSON NULL COMMENT 'array nomor kamar 1 keluarga', expires_at DATETIME NULL, attributes JSON NULL COMMENT 'array {text, type(dietary|spa|bed|transfer|occasion|security|inclusion|care), depts:[...]}', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_guest_cards_property_date_section (property_id, date, section), KEY idx_hr_guest_cards_import (import_id), CONSTRAINT fk_hr_guest_cards_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_guest_cards_import FOREIGN KEY (import_id) REFERENCES hr_guest_imports(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_briefings -- --------------------------------------------------------------------- CREATE TABLE hr_briefings ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, date DATE NOT NULL, scope ENUM('resort','department') NOT NULL DEFAULT 'resort', department VARCHAR(60) NULL COMMENT 'wajib bila scope=department', sections JSON NULL COMMENT 'blok konten (guest, maintenance F16, dst)', generated_at DATETIME NULL, edited_by INT UNSIGNED NULL, published_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_briefings_property_date (property_id, date), KEY idx_hr_briefings_edited_by (edited_by), CONSTRAINT fk_hr_briefings_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_briefings_edited_by FOREIGN KEY (edited_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_briefing_acks -- --------------------------------------------------------------------- CREATE TABLE hr_briefing_acks ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, briefing_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, ack_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_briefing_acks (briefing_id, user_id), KEY idx_hr_briefing_acks_user (user_id), CONSTRAINT fk_hr_briefing_acks_briefing FOREIGN KEY (briefing_id) REFERENCES hr_briefings(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_briefing_acks_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_briefing_items -- --------------------------------------------------------------------- CREATE TABLE hr_briefing_items ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, briefing_id INT UNSIGNED NOT NULL, text VARCHAR(500) NOT NULL, ai_suggested_class ENUM('task','kpi_proposal','hotelfix_wo','info') NULL, confirmed_class ENUM('task','kpi_proposal','hotelfix_wo','info') NULL, confirmed_by INT UNSIGNED NULL, ref_id BIGINT UNSIGNED NULL COMMENT 'id baris hasil (hr_tasks.id / work_orders.id / hr_kpi_definitions.id)', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_briefing_items_briefing (briefing_id), KEY idx_hr_briefing_items_confirmed_by (confirmed_by), CONSTRAINT fk_hr_briefing_items_briefing FOREIGN KEY (briefing_id) REFERENCES hr_briefings(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_briefing_items_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_tasks — papan task terpadu Sanggraloka One (kontrak PM-PLAN §4): -- source_app/source_ref WAJIB untuk label sumber FIX/CREW/SELL di tasks.php. -- --------------------------------------------------------------------- CREATE TABLE hr_tasks ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, source ENUM('briefing','intake','manual') NOT NULL DEFAULT 'manual', source_app ENUM('crew','sell') NOT NULL DEFAULT 'crew' COMMENT 'kontrak papan task terpadu PM-PLAN §4', source_ref VARCHAR(100) NULL COMMENT 'referensi bebas ke record asal, mis. mkt_quotations:123', title VARCHAR(200) NOT NULL, description TEXT NULL, department VARCHAR(60) NULL, assignee_id INT UNSIGNED NULL, due_date DATE NULL, priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal', status ENUM('open','in_progress','done','overdue','cancelled') NOT NULL DEFAULT 'open', progress_notes JSON NULL, hotelfix_wo_id BIGINT UNSIGNED NULL, verified_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_tasks_property_status_due (property_id, status, due_date), KEY idx_hr_tasks_property_source_app (property_id, source_app), KEY idx_hr_tasks_assignee (assignee_id), KEY idx_hr_tasks_hotelfix_wo (hotelfix_wo_id), CONSTRAINT fk_hr_tasks_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_tasks_assignee FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_hr_tasks_hotelfix_wo FOREIGN KEY (hotelfix_wo_id) REFERENCES work_orders(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_hr_tasks_verified_by FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- FK susulan hr_briefing_items.ref_id tidak diberi FK eksplisit (polimorfik -- lintas 3 tabel: hr_tasks/work_orders/hr_kpi_definitions) — konsisten pola -- kolom polimorfik lain di skema ini (mis. hr_test_attempts.candidate_id). -- --------------------------------------------------------------------- -- hr_ai_intakes -- --------------------------------------------------------------------- CREATE TABLE hr_ai_intakes ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, raw_text TEXT NOT NULL, source_type ENUM('guest_request','event','note') NOT NULL DEFAULT 'note', parsed JSON NULL, model VARCHAR(60) NULL, tokens_in INT UNSIGNED NULL, tokens_out INT UNSIGNED NULL, cost_idr DECIMAL(12,2) NULL, confirmed_by INT UNSIGNED NULL, created_task_ids JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_ai_intakes_property (property_id), KEY idx_hr_ai_intakes_confirmed_by (confirmed_by), CONSTRAINT fk_hr_ai_intakes_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_ai_intakes_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 5. CHALLENGE, KPI, INSPEKSI, DISIPLIN (F8, F9) -- ===================================================================== -- --------------------------------------------------------------------- -- hr_challenges -- --------------------------------------------------------------------- CREATE TABLE hr_challenges ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, name VARCHAR(150) NOT NULL, department VARCHAR(60) NULL, type ENUM('judged','timed','data_driven') NOT NULL, metric VARCHAR(150) NULL, rules TEXT NULL, schedule VARCHAR(150) NULL, prize VARCHAR(150) NULL, budget_source VARCHAR(100) NULL, competency_id INT UNSIGNED NULL, status ENUM('draft','open','running','closed','cancelled') NOT NULL DEFAULT 'draft', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_challenges_property_status (property_id, status), KEY idx_hr_challenges_competency (competency_id), CONSTRAINT fk_hr_challenges_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_challenges_competency FOREIGN KEY (competency_id) REFERENCES hr_competencies(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_challenge_results — participant_id/team: employee tunggal via -- participant_id; tim disimpan sbg nama bebas di kolom team. -- --------------------------------------------------------------------- CREATE TABLE hr_challenge_results ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, challenge_id INT UNSIGNED NOT NULL, participant_id INT UNSIGNED NULL, team VARCHAR(100) NULL, score DECIMAL(10,2) NULL, time_seconds INT UNSIGNED NULL, rank SMALLINT UNSIGNED NULL, judged_by INT UNSIGNED NULL, evidence JSON NULL COMMENT 'array url foto/video', awarded JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_challenge_results_challenge (challenge_id), KEY idx_hr_challenge_results_participant (participant_id), CONSTRAINT fk_hr_challenge_results_challenge FOREIGN KEY (challenge_id) REFERENCES hr_challenges(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_challenge_results_participant FOREIGN KEY (participant_id) REFERENCES hr_employees(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_hr_challenge_results_judged_by FOREIGN KEY (judged_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_kpi_definitions -- --------------------------------------------------------------------- CREATE TABLE hr_kpi_definitions ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, department VARCHAR(60) NOT NULL, position_id INT UNSIGNED NULL COMMENT 'NULL = indikator level departemen', name VARCHAR(200) NOT NULL, target VARCHAR(100) NOT NULL COMMENT 'nilai target sbg teks (mis. >=95%, <=2/bulan)', unit VARCHAR(30) NULL, weight DECIMAL(5,2) NOT NULL COMMENT 'bobot %', source ENUM('attendance','training','discipline','checklist','hotelfix_wo','manual','inspection') NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_kpi_definitions_property_dept (property_id, department), KEY idx_hr_kpi_definitions_position (position_id), CONSTRAINT fk_hr_kpi_definitions_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_kpi_definitions_position FOREIGN KEY (position_id) REFERENCES hr_positions(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_kpi_scores — employee_id/department: skor individu via employee_id, -- skor departemen via department (employee_id NULL). -- --------------------------------------------------------------------- CREATE TABLE hr_kpi_scores ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NULL, department VARCHAR(60) NULL COMMENT 'diisi bila skor level departemen (employee_id NULL)', period CHAR(7) NOT NULL COMMENT 'YYYY-MM', component_scores JSON NOT NULL, total DECIMAL(5,2) NULL, published_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_kpi_scores_property_period (property_id, period), KEY idx_hr_kpi_scores_employee (employee_id, period), CONSTRAINT fk_hr_kpi_scores_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_kpi_scores_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_inspections -- --------------------------------------------------------------------- CREATE TABLE hr_inspections ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, department VARCHAR(60) NOT NULL, template JSON NOT NULL COMMENT 'checklist titik inspeksi', scored_by INT UNSIGNED NULL, employee_id INT UNSIGNED NULL COMMENT 'yang diinspeksi (mis. RA pemilik kamar)', location_id INT UNSIGNED NULL, score DECIMAL(5,2) NULL, photos JSON NULL, ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_inspections_property_dept_ts (property_id, department, ts), KEY idx_hr_inspections_employee (employee_id), KEY idx_hr_inspections_location (location_id), CONSTRAINT fk_hr_inspections_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_inspections_scored_by FOREIGN KEY (scored_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_hr_inspections_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_hr_inspections_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_discipline -- --------------------------------------------------------------------- CREATE TABLE hr_discipline ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NOT NULL, level ENUM('coaching','sp1','sp2','sp3') NOT NULL, category VARCHAR(100) NOT NULL, chronology TEXT NULL, evidence JSON NULL, issued_by INT UNSIGNED NOT NULL, valid_until DATE NULL, ack_at DATETIME NULL, ack_signature_hash VARCHAR(255) NULL, objection_note TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_discipline_property_employee (property_id, employee_id), KEY idx_hr_discipline_issued_by (issued_by), CONSTRAINT fk_hr_discipline_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_discipline_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_discipline_issued_by FOREIGN KEY (issued_by) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 6. PAYROLL, SERVICE CHARGE, KASBON — ZONA MERAH (F10) -- Kolom nominal (earnings/deductions/gross/net di hr_payroll_items) = TEXT -- ciphertext, dienkripsi level aplikasi (kunci TIDAK disimpan di DB, §3.2). -- ===================================================================== -- --------------------------------------------------------------------- -- hr_payroll_periods -- --------------------------------------------------------------------- CREATE TABLE hr_payroll_periods ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, month CHAR(7) NOT NULL COMMENT 'YYYY-MM', cutoff DATE NOT NULL, status ENUM('draft','review','approved','locked') NOT NULL DEFAULT 'draft', approvals JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_payroll_periods_property_month (property_id, month), CONSTRAINT fk_hr_payroll_periods_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_payroll_items — earnings/deductions/employer_costs/gross/net = TEXT -- ciphertext (JSON dienkripsi sebelum simpan, aplikasi yang men-decrypt). -- --------------------------------------------------------------------- CREATE TABLE hr_payroll_items ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, period_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NOT NULL, earnings TEXT NULL COMMENT 'ciphertext JSON', deductions TEXT NULL COMMENT 'ciphertext JSON', employer_costs TEXT NULL COMMENT 'ciphertext JSON', gross TEXT NULL COMMENT 'ciphertext (nominal)', net TEXT NULL COMMENT 'ciphertext (nominal)', pph21 TEXT NULL COMMENT 'ciphertext (nominal)', published_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_payroll_items_period_employee (period_id, employee_id), KEY idx_hr_payroll_items_employee (employee_id), CONSTRAINT fk_hr_payroll_items_period FOREIGN KEY (period_id) REFERENCES hr_payroll_periods(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_payroll_items_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_payroll_params — semua tarif configurable, bertanggal-berlaku. -- --------------------------------------------------------------------- CREATE TABLE hr_payroll_params ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, param_key VARCHAR(60) NOT NULL COMMENT 'bpjs_kes_pct|jht_pct|jp_cap|umk|ot_divisor|...', value VARCHAR(60) NOT NULL, effective_from DATE NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_payroll_params_property_key_from (property_id, param_key, effective_from), CONSTRAINT fk_hr_payroll_params_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_service_charge -- --------------------------------------------------------------------- CREATE TABLE hr_service_charge ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, period_id INT UNSIGNED NOT NULL, total_collected DECIMAL(16,2) NOT NULL DEFAULT 0, risk_pct DECIMAL(5,2) NOT NULL DEFAULT 0, hr_dev_pct DECIMAL(5,2) NOT NULL DEFAULT 0, dist_method ENUM('point','equal','grade_weighted') NOT NULL DEFAULT 'point', point_value DECIMAL(14,4) NULL, detail JSON NULL COMMENT 'per employee: {employee_id, points, amount}', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_service_charge_period (period_id), CONSTRAINT fk_hr_service_charge_period FOREIGN KEY (period_id) REFERENCES hr_payroll_periods(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_loans (kasbon) -- --------------------------------------------------------------------- CREATE TABLE hr_loans ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NOT NULL, amount DECIMAL(14,2) NOT NULL, installments TINYINT UNSIGNED NOT NULL DEFAULT 1, balance DECIMAL(14,2) NOT NULL, approvals JSON NULL, status ENUM('requested','approved','rejected','active','settled','cancelled') NOT NULL DEFAULT 'requested', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_loans_property_employee (property_id, employee_id), CONSTRAINT fk_hr_loans_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_loans_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 7. REKRUTMEN, ONBOARDING, DW POOL, TRAINEE (F11, F12) -- ===================================================================== -- --------------------------------------------------------------------- -- hr_vacancies — dibuat sebelum hr_candidates (FK vacancy_id) -- --------------------------------------------------------------------- CREATE TABLE hr_vacancies ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, position_id INT UNSIGNED NOT NULL, headcount SMALLINT UNSIGNED NOT NULL DEFAULT 1, budget_approved TINYINT(1) NOT NULL DEFAULT 0, status ENUM('open','on_hold','closed','filled') NOT NULL DEFAULT 'open', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_vacancies_property_status (property_id, status), KEY idx_hr_vacancies_position (position_id), CONSTRAINT fk_hr_vacancies_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_vacancies_position FOREIGN KEY (position_id) REFERENCES hr_positions(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_candidates -- --------------------------------------------------------------------- CREATE TABLE hr_candidates ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, vacancy_id INT UNSIGNED NOT NULL, data JSON NOT NULL COMMENT 'identitas & data lamaran', cv_file VARCHAR(255) NULL, stage ENUM('applied','test','interview','offer','hired','rejected') NOT NULL DEFAULT 'applied', test_token VARCHAR(64) NULL COMMENT 'token akses test browser tanpa akun', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_candidates_test_token (test_token), KEY idx_hr_candidates_property_stage (property_id, stage), KEY idx_hr_candidates_vacancy (vacancy_id), CONSTRAINT fk_hr_candidates_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_candidates_vacancy FOREIGN KEY (vacancy_id) REFERENCES hr_vacancies(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- FK susulan: hr_test_attempts.candidate_id -> hr_candidates(id) ALTER TABLE hr_test_attempts ADD CONSTRAINT fk_hr_test_attempts_candidate FOREIGN KEY (candidate_id) REFERENCES hr_candidates(id) ON DELETE SET NULL ON UPDATE CASCADE; -- --------------------------------------------------------------------- -- hr_onboarding -- --------------------------------------------------------------------- CREATE TABLE hr_onboarding ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, employee_id INT UNSIGNED NOT NULL, checklist JSON NULL COMMENT 'json: d1/d7/d30/d60/d90', buddy_id INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_onboarding_employee (employee_id), KEY idx_hr_onboarding_buddy (buddy_id), CONSTRAINT fk_hr_onboarding_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_onboarding_buddy FOREIGN KEY (buddy_id) REFERENCES hr_employees(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_dw_pool -- --------------------------------------------------------------------- CREATE TABLE hr_dw_pool ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, full_name VARCHAR(150) NOT NULL, phone VARCHAR(30) NULL, departments JSON NULL COMMENT 'array departemen yang bisa dipanggil', rating_avg DECIMAL(3,2) NULL, last_called DATE NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_dw_pool_property (property_id), CONSTRAINT fk_hr_dw_pool_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_trainees -- --------------------------------------------------------------------- CREATE TABLE hr_trainees ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, full_name VARCHAR(150) NOT NULL, school VARCHAR(150) NULL, mou_file VARCHAR(255) NULL, mentor_id INT UNSIGNED NULL, batch VARCHAR(30) NULL, start_date DATE NULL, end_date DATE NULL, monthly_scores JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_trainees_property (property_id), KEY idx_hr_trainees_mentor (mentor_id), CONSTRAINT fk_hr_trainees_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_trainees_mentor FOREIGN KEY (mentor_id) REFERENCES hr_employees(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 8. HANDOVER, INSIDEN, LOST & FOUND, GRIEVANCE, EXIT (F13, F14) -- hr_announcements TIDAK dibuat -> memakai broadcasts existing -- (app_source='hrd'), sesuai §4 SPEK-HOTELCREW. -- ===================================================================== -- --------------------------------------------------------------------- -- hr_handover_logs -- --------------------------------------------------------------------- CREATE TABLE hr_handover_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, department VARCHAR(60) NOT NULL COMMENT 'non-engineering (handover engineering ada di HotelFix)', shift ENUM('M','A','N') NOT NULL, notes TEXT NULL, by_user_id INT UNSIGNED NOT NULL, ack_by INT UNSIGNED NULL, ack_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_handover_logs_property_dept (property_id, department, shift), KEY idx_hr_handover_logs_by (by_user_id), CONSTRAINT fk_hr_handover_logs_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_handover_logs_by FOREIGN KEY (by_user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_hr_handover_logs_ack_by FOREIGN KEY (ack_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_incidents -- --------------------------------------------------------------------- CREATE TABLE hr_incidents ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, type ENUM('guest','loss','safety') NOT NULL, description TEXT NOT NULL, photos JSON NULL, location_id INT UNSIGNED NULL, reported_by INT UNSIGNED NOT NULL, routed_to VARCHAR(60) NULL COMMENT 'departemen tujuan', status ENUM('open','investigating','resolved','closed') NOT NULL DEFAULT 'open', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_incidents_property_status (property_id, status), KEY idx_hr_incidents_location (location_id), KEY idx_hr_incidents_reported_by (reported_by), CONSTRAINT fk_hr_incidents_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_incidents_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_hr_incidents_reported_by FOREIGN KEY (reported_by) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_lost_found -- --------------------------------------------------------------------- CREATE TABLE hr_lost_found ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, item VARCHAR(200) NOT NULL, photo VARCHAR(255) NULL, location_id INT UNSIGNED NULL, found_by INT UNSIGNED NOT NULL, stored_at VARCHAR(100) NULL, returned_to VARCHAR(150) NULL, status ENUM('stored','claimed','disposed','handed_to_authority') NOT NULL DEFAULT 'stored', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_lost_found_property_status (property_id, status), KEY idx_hr_lost_found_location (location_id), KEY idx_hr_lost_found_found_by (found_by), CONSTRAINT fk_hr_lost_found_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_lost_found_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_hr_lost_found_found_by FOREIGN KEY (found_by) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_grievances -- --------------------------------------------------------------------- CREATE TABLE hr_grievances ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, employee_id INT UNSIGNED NULL COMMENT 'NULL = anonim', channel ENUM('normal','whistle') NOT NULL DEFAULT 'normal', text TEXT NOT NULL, status ENUM('open','investigating','resolved','closed') NOT NULL DEFAULT 'open', handled_by INT UNSIGNED NULL COMMENT 'akses terbatas (HC Dir/GM)', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_hr_grievances_property_status (property_id, status), KEY idx_hr_grievances_employee (employee_id), CONSTRAINT fk_hr_grievances_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_hr_grievances_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_hr_grievances_handled_by FOREIGN KEY (handled_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- hr_exit -- --------------------------------------------------------------------- CREATE TABLE hr_exit ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, employee_id INT UNSIGNED NOT NULL, checklist JSON NULL, interview JSON NULL, reason VARCHAR(255) NULL, last_day DATE NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_hr_exit_employee (employee_id), CONSTRAINT fk_hr_exit_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 9. AUDIT & STEP-UP AUTH (satu utk kedua modul FIX+CREW; §3.2 zona merah) -- ===================================================================== -- --------------------------------------------------------------------- -- audit_logs — APPEND-ONLY (tidak ada UPDATE/DELETE oleh aplikasi) -- --------------------------------------------------------------------- CREATE TABLE audit_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, user_id INT UNSIGNED NULL, role VARCHAR(30) NULL, property_id INT UNSIGNED NULL, module ENUM('fix','crew') NOT NULL, action ENUM('create','update','delete','view_sensitive','export','login','logout','approve','override','stepup_ok','stepup_fail') NOT NULL, entity VARCHAR(60) NOT NULL, entity_id VARCHAR(60) NULL, before_data JSON NULL COMMENT 'field sensitif ikut terenkripsi di level aplikasi', after_data JSON NULL COMMENT 'field sensitif ikut terenkripsi di level aplikasi', ip VARCHAR(45) NULL, device VARCHAR(255) NULL, note VARCHAR(255) NULL, PRIMARY KEY (id), KEY idx_audit_logs_property_ts (property_id, ts), KEY idx_audit_logs_user (user_id), KEY idx_audit_logs_entity (entity, entity_id), CONSTRAINT fk_audit_logs_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_audit_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- auth_stepup — PIN 6 digit + OTP WA (stub interface, gateway menyusul) -- --------------------------------------------------------------------- CREATE TABLE auth_stepup ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, zone ENUM('payroll') NOT NULL DEFAULT 'payroll', pin_hash VARCHAR(255) NULL, otp_hash VARCHAR(255) NULL, wa_msisdn VARCHAR(30) NULL, sent_at DATETIME NULL, expires_at DATETIME NULL, verified_at DATETIME NULL, fail_count TINYINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_auth_stepup_user_zone (user_id, zone), CONSTRAINT fk_auth_stepup_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 10. ALTER users — link ke hr_employees + flag Trainer -- ===================================================================== ALTER TABLE users ADD COLUMN employee_id INT UNSIGNED NULL AFTER phone, ADD COLUMN is_trainer TINYINT(1) NOT NULL DEFAULT 0 AFTER employee_id; ALTER TABLE users ADD CONSTRAINT fk_users_employee FOREIGN KEY (employee_id) REFERENCES hr_employees(id) ON DELETE SET NULL ON UPDATE CASCADE; -- ===================================================================== -- END 0008_hr_init.sql -- Catatan penyimpangan/keputusan: -- 1. hr_positions & hr_faculties/hr_competencies ditambah property_id -- eksplisit (§4 spek tidak menyebut eksplisit tapi §1 PM-PLAN mewajibkan -- SEMUA tabel ber-property_id) — konsisten dgn tabel FIX 0001_init.sql. -- 2. hr_positions.required_competencies & hr_employees kolom kompetensi: -- disimpan JSON kode kompetensi (bukan FK M:N terpisah) karena §4 spek -- hanya menulis "required_competencies (→hr_competencies)" tanpa -- tabel pivot eksplisit; hr_employee_comp sudah menutupi relasi -- per-individu dgn level. -- 3. Kolom polimorfik (hr_briefing_items.ref_id, hr_test_attempts. -- candidate_id + user_id, hr_challenge_results.participant_id + team) -- TIDAK diberi FK tunggal karena mengacu >1 tabel target — konsisten -- pola FIX (mis. tidak ada di 0001, tapi ini pola umum utk polimorfik). -- 4. hr_kpi_scores & hr_inspections: department disimpan sbg VARCHAR -- (bukan FK) mengikuti pola `hr_employees.department`, -- `hr_positions.department`, `work_orders` dst di seluruh skema -- (department = string bebas, bukan tabel master terpisah). -- 5. hr_announcements TIDAK dibuat — memakai broadcasts existing -- (0001_init.sql) dgn app_source; kolom app_source ditambahkan ke -- broadcasts di migrasi ini TIDAK dilakukan (di luar scope Agent DB; -- PM-PLAN §4 tidak mendaftar ALTER broadcasts pada Agent DB — dicatat -- sbg catatan utk Agent SHELL/HR bila dibutuhkan). -- =====================================================================