-- ============================================================
-- Migrasi Fase A — Sistem 4 Role SKPL + Log Aktivitas
-- ============================================================
-- Jalankan SEKALI pada database existing (webgis_db).
-- Aman untuk data yang sudah ada: role lama dipetakan, bukan dihapus.
--   admin    -> operator
--   user     -> pimpinan
-- ============================================================

-- 1. Migrasi nilai role lama -> SKPL (widen -> update -> narrow)
ALTER TABLE users MODIFY COLUMN role VARCHAR(20) NOT NULL DEFAULT 'enumerator';

UPDATE users SET role = 'operator' WHERE role = 'admin';
UPDATE users SET role = 'pimpinan' WHERE role = 'user';

ALTER TABLE users
    MODIFY COLUMN role ENUM('publik','enumerator','operator','pimpinan')
    NOT NULL DEFAULT 'enumerator';

-- 2. Kolom tambahan users (KF-02 / KF-03 / KF-26)
ALTER TABLE users
    ADD COLUMN email         VARCHAR(120) DEFAULT NULL AFTER username,
    ADD COLUMN is_active     TINYINT(1)   NOT NULL DEFAULT 1 AFTER role,
    ADD COLUMN wilayah_tugas VARCHAR(100) DEFAULT NULL AFTER is_active,
    ADD COLUMN last_login    TIMESTAMP    NULL DEFAULT NULL AFTER wilayah_tugas;

-- 3. Akun demo Enumerator (password: user123)
--    operator & pimpinan berasal dari migrasi akun lama (admin / pengguna)
INSERT IGNORE INTO users (username, email, password, role, nama_lengkap)
VALUES (
    'enumerator',
    'enumerator@webgis.id',
    '$2y$12$djJkmhJBRCSCqqpAICIvGuLoVpICrDUS2IVECbZUodgR89VRTqdLW',
    'enumerator',
    'Petugas Lapangan'
);

-- 4. Log aktivitas (KF-04) — audit trail
CREATE TABLE IF NOT EXISTS activity_log (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT NULL,
    username     VARCHAR(50)  DEFAULT NULL,
    aksi         VARCHAR(50)  NOT NULL,
    tabel_target VARCHAR(50)  DEFAULT NULL,
    record_id    INT          DEFAULT NULL,
    keterangan   TEXT         DEFAULT NULL,
    ip_address   VARCHAR(45)  DEFAULT NULL,
    created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_activity_user (user_id),
    INDEX idx_activity_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;