-- ============================================================
--  MIGRATION – Sistem Peningkatan Mutu & Keselamatan Pasien
--  Database  : mutu_rskhjt
--  Dibuat    : 2026-09-11
-- ============================================================

-- Pastikan menggunakan database yang benar
USE mutu_rskhjt;

-- ============================================================
--  TABLE: users
--  Menyimpan akun staf yang berwenang mengakses sistem SPM-KP
-- ============================================================
CREATE TABLE IF NOT EXISTS `users` (
    `id`            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `nama_lengkap`  VARCHAR(150)     NOT NULL COMMENT 'Nama lengkap staf',
    `username`      VARCHAR(60)      NOT NULL COMMENT 'Username unik untuk login',
    `email`         VARCHAR(150)     NOT NULL COMMENT 'Email institusi staf',
    `password`      VARCHAR(255)     NOT NULL COMMENT 'Bcrypt hash',
    `nip`           VARCHAR(30)      NULL     COMMENT 'Nomor Induk Pegawai',
    `role`          ENUM(
                        'admin',
                        'dokter',
                        'perawat',
                        'bidan',
                        'apoteker',
                        'analis',
                        'radiografer',
                        'rekam_medis',
                        'manajemen',
                        'viewer'
                    )                NOT NULL DEFAULT 'viewer' COMMENT 'Peran pengguna di sistem',
    `unit`          VARCHAR(100)     NULL     COMMENT 'Unit/Departemen asal (contoh: IGD, Poli Anak)',
    `no_hp`         VARCHAR(20)      NULL     COMMENT 'Nomor HP untuk notifikasi',
    `foto`          VARCHAR(255)     NULL     COMMENT 'Path foto profil',
    `status`        ENUM('aktif','nonaktif','pending')
                                     NOT NULL DEFAULT 'aktif',
    `last_login`    DATETIME         NULL     COMMENT 'Waktu login terakhir',
    `login_attempt` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Jumlah percobaan login gagal',
    `locked_until`  DATETIME         NULL     COMMENT 'Akun terkunci sampai waktu ini',
    `reset_token`   VARCHAR(100)     NULL     COMMENT 'Token reset password',
    `reset_expires` DATETIME         NULL     COMMENT 'Batas waktu token reset',
    `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_username` (`username`),
    UNIQUE KEY `uq_email`    (`email`),
    KEY `idx_status`         (`status`),
    KEY `idx_role`           (`role`),
    KEY `idx_unit`           (`unit`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Tabel akun pengguna sistem SPM-KP';

-- ============================================================
--  TABLE: user_sessions
--  Mencatat sesi login aktif (opsional, untuk audit trail)
-- ============================================================
CREATE TABLE IF NOT EXISTS `user_sessions` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`    INT UNSIGNED    NOT NULL,
    `token`      VARCHAR(128)    NOT NULL COMMENT 'Session token unik',
    `ip_address` VARCHAR(45)     NULL     COMMENT 'IPv4 atau IPv6',
    `user_agent` TEXT            NULL,
    `created_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATETIME        NOT NULL,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_token` (`token`),
    KEY `idx_user_id`     (`user_id`),
    CONSTRAINT `fk_session_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Audit trail sesi login pengguna';

-- ============================================================
--  TABLE: activity_logs
--  Log aktivitas pengguna (login, akses modul, ubah data)
-- ============================================================
CREATE TABLE IF NOT EXISTS `activity_logs` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`     INT UNSIGNED    NULL     COMMENT 'NULL jika aksi anonim/sistem',
    `aksi`        VARCHAR(100)    NOT NULL COMMENT 'Contoh: LOGIN, LOGOUT, CREATE_INSIDEN',
    `modul`       VARCHAR(60)     NULL     COMMENT 'Contoh: insiden, indikator, dashboard',
    `keterangan`  TEXT            NULL,
    `ip_address`  VARCHAR(45)     NULL,
    `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_user_id`    (`user_id`),
    KEY `idx_aksi`       (`aksi`),
    KEY `idx_created_at` (`created_at`),
    CONSTRAINT `fk_log_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Log aktivitas semua pengguna sistem';

-- ============================================================
--  DATA AWAL: Akun Administrator Default
--  Password  : adminrskhjt  (bcrypt hash)
-- ============================================================
INSERT IGNORE INTO `users`
    (`nama_lengkap`, `username`, `email`, `password`, `nip`, `role`, `unit`, `status`)
VALUES
    (
        'Administrator SPM-KP',
        'admin',
        'admin@rsss.go.id',
        '$2y$10$0sDIpTufe7AV14FgTqx3ruYbG.5bwGjegUVysLk5mA4K1K4/VM.G.',
        '000000000001',
        'admin',
        'Komite PMKP',
        'aktif'
    );

-- ============================================================
--  SELESAI
-- ============================================================