-- =============================================================================
-- Insert Users and Roles (MySQL)
-- =============================================================================
-- Run this against your app database (DB_NAME in .env) to seed users and roles.
-- Tables: alf_authority (users + roles), alf_authority_alias (user → role links),
--         plg_user_credentials (passwords, bcrypt).
--
-- Usage:
--   mysql -h localhost -P 3306 -u YOUR_DB_USER -p YOUR_DB_NAME < scripts/sql/insert_users_and_roles.sql
-- Or paste sections into MySQL Workbench / DBeaver.
--
-- Users: Super Admins josephdilas@gmail.com, yusuphdilas@gmail.com (password: 5bad89a3);
--        plus indexer1, scanner1, archiver1 (dummy passwords).
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 1. Create password table if not exists (app uses this for login)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS plg_user_credentials (
  username   VARCHAR(255) PRIMARY KEY,
  password_hash VARCHAR(255) NOT NULL
);

-- -----------------------------------------------------------------------------
-- 2. Insert standard roles and users into alf_authority
--    (id, version, authority, crc). Skip if row already exists.
-- -----------------------------------------------------------------------------
-- Roles (ids 2–7)
INSERT IGNORE INTO alf_authority (id, version, authority, crc) VALUES
  (2, 1, 'ROLE_ADMINISTRATOR',     494732046),
  (3, 1, 'ROLE_INDEXING_OFFICER',  18216359),
  (4, 1, 'ROLE_SCANNING_OPERATOR', 2257917166),
  (5, 1, 'ROLE_CASHIER',           2507465194),
  (6, 1, 'ROLE_GIS_VIEWER',        3226851999),
  (7, 1, 'ROLE_ARCHIVING_OFFICER', 1154550265);

-- Users: super admins (1, 11), indexer (8), scanner (9), archiver (10)
INSERT IGNORE INTO alf_authority (id, version, authority, crc) VALUES
  (1, 1, 'josephdilas@gmail.com',  2227980814),
  (11, 1, 'yusuphdilas@gmail.com', 759598338),
  (8, 1, 'indexer1',               536378566),
  (9, 1, 'scanner1',               3241908228),
  (10, 1, 'archiver1',              3307222034);

-- -----------------------------------------------------------------------------
-- 3. Link users to roles via alf_authority_alias
--    (id, version, auth_id = user id, alias_id = role id).
-- -----------------------------------------------------------------------------
-- josephdilas@gmail.com (1) → ROLE_ADMINISTRATOR (2) [Super Admin]
-- yusuphdilas@gmail.com (11) → ROLE_ADMINISTRATOR (2) [Super Admin]
-- indexer1 (8) → ROLE_INDEXING_OFFICER (3), ROLE_SCANNING_OPERATOR (4)
-- scanner1 (9) → ROLE_SCANNING_OPERATOR (4)
-- archiver1 (10) → ROLE_ARCHIVING_OFFICER (7)
INSERT IGNORE INTO alf_authority_alias (id, version, auth_id, alias_id) VALUES
  (1, 1, 1, 2),
  (6, 1, 11, 2),
  (2, 1, 8, 3),
  (3, 1, 8, 4),
  (4, 1, 9, 4),
  (5, 1, 10, 7);

-- -----------------------------------------------------------------------------
-- 4. Insert passwords (bcrypt). Admin: 5bad89a3; others: dummy passwords.
--    (Run section 1 first, or the CREATE below, so the table exists.)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS plg_user_credentials (
  username     VARCHAR(255) PRIMARY KEY,
  password_hash VARCHAR(255) NOT NULL
);

-- Super Admins: josephdilas@gmail.com, yusuphdilas@gmail.com / 5bad89a3 (same bcrypt hash)
-- indexer1 / indexer123, scanner1 / scanner123, archiver1 / archiver123
INSERT INTO plg_user_credentials (username, password_hash) VALUES
  ('josephdilas@gmail.com', '$2b$12$yzeTzJBwEH5rWBiCLxjpQuuUOBl0n6y6JkLXEupkT1KXyY3EVTbmy'),
  ('yusuphdilas@gmail.com', '$2b$12$yzeTzJBwEH5rWBiCLxjpQuuUOBl0n6y6JkLXEupkT1KXyY3EVTbmy'),
  ('indexer1', '$2b$12$R2S48nFAcnY5BOSAVbMgp.P3RzHqMfzpneFm0Awk26/BBl55paA.y'),
  ('scanner1', '$2b$12$ZDsDTXLVcwJHSlrKld5rjuOsHFhdmstXhCQql4FKuVAG2JnBVMdGu'),
  ('archiver1', '$2b$12$pCtgaq4kITsYLG1uAwdxr.Qp3PWsayK5gub8ZF5.08N3zx06huDhS')
ON DUPLICATE KEY UPDATE password_hash = VALUES(password_hash);

-- -----------------------------------------------------------------------------
-- If you already had user id 1 as 'myadmin', replace with admin email:
--   UPDATE alf_authority SET authority = 'josephdilas@gmail.com', crc = 2227980814 WHERE id = 1;
-- Then run the plg_user_credentials INSERT above (or the ON DUPLICATE will update).
-- -----------------------------------------------------------------------------
--
-- Logins:
--   Super Admin: josephdilas@gmail.com / 5bad89a3
--   Super Admin: yusuphdilas@gmail.com / 5bad89a3
--   Indexer:     indexer1 / indexer123
--   Scanner:     scanner1 / scanner123
--   Archiver:    archiver1 / archiver123