-- RAJAWALI CORE SYSTEM
-- Sprint 2 — PATCH 2: AUTH + USER + ROLE + PERMISSION + AKSES LOKASI
-- Migration: 002_auth.sql
-- Target: MySQL 8.x / MariaDB 10.x


-- ============================================================
-- USERS
-- ============================================================

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  avatar_path VARCHAR(255) NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_owner TINYINT(1) NOT NULL DEFAULT 0,
  failed_attempts INT NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_tenant_email (tenant_id, email),
  KEY idx_users_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- ROLES & PERMISSIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS roles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(64) NOT NULL,
  name VARCHAR(120) NOT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_roles_tenant_code (tenant_id, code),
  KEY idx_roles_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(120) NOT NULL,
  name VARCHAR(160) NOT NULL,
  module VARCHAR(64) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_permissions_code (code),
  KEY idx_permissions_module (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  KEY idx_rp_perm (permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id),
  KEY idx_ur_role (role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- USER LOCATION ACCESS
-- ============================================================

CREATE TABLE IF NOT EXISTS user_locations (
  user_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, location_id),
  KEY idx_ul_location (location_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- SEED: PERMISSION CATALOG (minimal)
-- ============================================================

INSERT IGNORE INTO permissions (code, name, module, created_at) VALUES
  ('dashboard.view', 'Lihat Dashboard', 'dashboard', NOW()),
  ('pos.view', 'Lihat POS', 'pos', NOW()),

  ('settings.locations.view', 'Lihat Lokasi Bisnis', 'settings', NOW()),
  ('settings.locations.add', 'Tambah Lokasi Bisnis', 'settings', NOW()),
  ('settings.locations.edit', 'Edit/Toggle Lokasi Bisnis', 'settings', NOW()),

  ('settings.branding.view', 'Lihat Branding', 'settings', NOW()),
  ('settings.branding.edit', 'Edit Branding', 'settings', NOW()),

  ('settings.users.view', 'Lihat Users', 'settings', NOW()),
  ('settings.users.add', 'Tambah Users', 'settings', NOW()),
  ('settings.users.edit', 'Edit Users', 'settings', NOW()),
  ('settings.users.toggle', 'Aktif/Nonaktif Users', 'settings', NOW()),

  ('settings.roles.view', 'Lihat Roles & Permissions', 'settings', NOW()),
  ('settings.roles.add', 'Tambah Role', 'settings', NOW()),
  ('settings.roles.edit', 'Edit Role Permissions', 'settings', NOW());

-- ============================================================
-- SEED: SYSTEM ROLES (per tenant)
-- ============================================================

-- Assumption: tenant id = 1 exists from 001_core.sql seeder
INSERT IGNORE INTO roles (tenant_id, code, name, is_system, created_at, updated_at) VALUES
  (1, 'owner', 'Owner', 1, NOW(), NOW()),
  (1, 'admin', 'Admin', 1, NOW(), NOW()),
  (1, 'kepala_toko', 'Kepala Toko', 1, NOW(), NOW()),
  (1, 'kasir', 'Kasir', 1, NOW(), NOW()),
  (1, 'karyawan', 'Karyawan', 1, NOW(), NOW());

-- ============================================================
-- SEED: OWNER USER (is_owner=1)
-- Password default: admin123
-- NOTE: bcrypt hash generated by PHP password_hash('admin123', PASSWORD_DEFAULT)
-- ============================================================

INSERT IGNORE INTO users (tenant_id, name, email, avatar_path, password_hash, is_active, is_owner, failed_attempts, locked_until, last_login_at, created_at, updated_at)
VALUES
  (1, 'Owner', 'owner@rajawali.local', NULL, '$2y$10$6ap8w0RvpMF6neO5jxtQHedJCrPkgWTcRfIdVNnHd7SkiOS9xc11C', 1, 1, 0, NULL, NULL, NOW(), NOW());

-- owner role assignment (for display; permission bypass is_owner)
INSERT IGNORE INTO user_roles (user_id, role_id)
SELECT u.id, r.id
FROM users u
JOIN roles r ON r.tenant_id = u.tenant_id AND r.code = 'owner'
WHERE u.tenant_id = 1 AND u.email = 'owner@rajawali.local';

-- owner location access: all active locations
INSERT IGNORE INTO user_locations (user_id, location_id)
SELECT u.id, l.id
FROM users u
JOIN locations l ON l.tenant_id = u.tenant_id
WHERE u.tenant_id = 1 AND u.email = 'owner@rajawali.local';

-- ============================================================
-- SEED: ROLE PERMISSIONS
-- Kasir acceptance: only dashboard + pos
-- ============================================================

-- helper inserts: grant permissions to role
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.code IN ('dashboard.view','pos.view')
WHERE r.tenant_id = 1 AND r.code = 'kasir';

-- admin: everything settings + dashboard + pos
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.code IN (
  'dashboard.view','pos.view',
  'settings.locations.view','settings.locations.add','settings.locations.edit',
  'settings.branding.view','settings.branding.edit',
  'settings.users.view','settings.users.add','settings.users.edit','settings.users.toggle',
  'settings.roles.view','settings.roles.add','settings.roles.edit'
)
WHERE r.tenant_id = 1 AND r.code = 'admin';

-- kepala_toko: dashboard + pos + locations view/edit + branding view
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.code IN (
  'dashboard.view','pos.view',
  'settings.locations.view','settings.locations.edit',
  'settings.branding.view'
)
WHERE r.tenant_id = 1 AND r.code = 'kepala_toko';

-- karyawan: dashboard view only
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.code IN ('dashboard.view')
WHERE r.tenant_id = 1 AND r.code = 'karyawan';
