-- 002_auth.sql (Sprint 2 Patch 2)
-- Auth + Users + Roles + Permissions + Akses Lokasi
-- Requires: 001_core.sql already applied


-- 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,
  password_hash VARCHAR(255) NOT NULL,
  thumbnail VARCHAR(255) NULL,
  is_owner TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  failed_login_count 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),
  CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Roles
CREATE TABLE IF NOT EXISTS roles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(80) 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),
  CONSTRAINT fk_roles_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Permissions (catalog)
CREATE TABLE IF NOT EXISTS permissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(120) NOT NULL,
  module VARCHAR(80) NOT NULL,
  action VARCHAR(40) NOT NULL,
  name VARCHAR(160) 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;

-- Role permissions pivot
CREATE TABLE IF NOT EXISTS role_permissions (
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User roles pivot
CREATE TABLE IF NOT EXISTS user_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User locations pivot
CREATE TABLE IF NOT EXISTS user_locations (
  user_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (user_id, location_id),
  CONSTRAINT fk_ul_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ul_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Password reset gateway (Sprint 2: email sending later)
CREATE TABLE IF NOT EXISTS password_resets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_pr_user (user_id),
  CONSTRAINT fk_pr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- =========================================================
-- SEED: Permission Catalog (minimal per modul: view/add/edit/print/export)
-- =========================================================

INSERT IGNORE INTO permissions (code, module, action, name, created_at) VALUES
-- dashboard
('dashboard.view','dashboard','view','Dashboard: View',NOW()),
('dashboard.add','dashboard','add','Dashboard: Add',NOW()),
('dashboard.edit','dashboard','edit','Dashboard: Edit',NOW()),
('dashboard.print','dashboard','print','Dashboard: Print',NOW()),
('dashboard.export','dashboard','export','Dashboard: Export',NOW()),

-- pos
('pos.view','pos','view','POS: View',NOW()),
('pos.add','pos','add','POS: Add',NOW()),
('pos.edit','pos','edit','POS: Edit',NOW()),
('pos.print','pos','print','POS: Print',NOW()),
('pos.export','pos','export','POS: Export',NOW()),

-- settings.locations
('settings.locations.view','settings.locations','view','Settings Lokasi: View',NOW()),
('settings.locations.add','settings.locations','add','Settings Lokasi: Add',NOW()),
('settings.locations.edit','settings.locations','edit','Settings Lokasi: Edit',NOW()),
('settings.locations.print','settings.locations','print','Settings Lokasi: Print',NOW()),
('settings.locations.export','settings.locations','export','Settings Lokasi: Export',NOW()),

-- settings.branding
('settings.branding.view','settings.branding','view','Settings Branding: View',NOW()),
('settings.branding.add','settings.branding','add','Settings Branding: Add',NOW()),
('settings.branding.edit','settings.branding','edit','Settings Branding: Edit',NOW()),
('settings.branding.print','settings.branding','print','Settings Branding: Print',NOW()),
('settings.branding.export','settings.branding','export','Settings Branding: Export',NOW()),

-- settings.users
('settings.users.view','settings.users','view','Settings Users: View',NOW()),
('settings.users.add','settings.users','add','Settings Users: Add',NOW()),
('settings.users.edit','settings.users','edit','Settings Users: Edit',NOW()),
('settings.users.print','settings.users','print','Settings Users: Print',NOW()),
('settings.users.export','settings.users','export','Settings Users: Export',NOW()),

-- settings.roles
('settings.roles.view','settings.roles','view','Settings Roles: View',NOW()),
('settings.roles.add','settings.roles','add','Settings Roles: Add',NOW()),
('settings.roles.edit','settings.roles','edit','Settings Roles: Edit',NOW()),
('settings.roles.print','settings.roles','print','Settings Roles: Print',NOW()),
('settings.roles.export','settings.roles','export','Settings Roles: Export',NOW()),

-- profile
('profile.view','profile','view','Profile: View',NOW()),
('profile.add','profile','add','Profile: Add',NOW()),
('profile.edit','profile','edit','Profile: Edit',NOW()),
('profile.print','profile','print','Profile: Print',NOW()),
('profile.export','profile','export','Profile: Export',NOW());

-- =========================================================
-- SEED: Roles bawaan (owner/admin/kepala_toko/kasir/karyawan)
-- =========================================================
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: Role Permissions (minimal)
-- =========================================================
-- owner = full (all permissions)
INSERT IGNORE INTO role_permissions (role_id, permission_id, created_at)
SELECT r.id, p.id, NOW()
FROM roles r
JOIN permissions p ON 1=1
WHERE r.tenant_id=1 AND r.code='owner';

-- admin = almost all settings + dashboard + pos + profile (no need add/print/export forced but ok)
INSERT IGNORE INTO role_permissions (role_id, permission_id, created_at)
SELECT r.id, p.id, NOW()
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.roles.view','settings.roles.add','settings.roles.edit',
  'profile.view','profile.edit'
)
WHERE r.tenant_id=1 AND r.code='admin';

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

-- kasir = only POS + profile
INSERT IGNORE INTO role_permissions (role_id, permission_id, created_at)
SELECT r.id, p.id, NOW()
FROM roles r
JOIN permissions p ON p.code IN (
  'pos.view',
  'profile.view','profile.edit'
)
WHERE r.tenant_id=1 AND r.code='kasir';

-- karyawan = dashboard + profile (no settings)
INSERT IGNORE INTO role_permissions (role_id, permission_id, created_at)
SELECT r.id, p.id, NOW()
FROM roles r
JOIN permissions p ON p.code IN (
  'dashboard.view',
  'profile.view','profile.edit'
)
WHERE r.tenant_id=1 AND r.code='karyawan';

-- =========================================================
-- SEED: Owner user (is_owner=1)
-- =========================================================
INSERT IGNORE INTO users
(tenant_id, name, email, password_hash, thumbnail, is_owner, is_active, failed_login_count, locked_until, last_login_at, created_at, updated_at)
VALUES
(1,'Owner','owner@rajawali.local','$2y$10$qXnF2JvpDBXfc0/CZL31CORH5XK2sdsPnLe5HqOaLirmaU12XMN72',NULL,1,1,0,NULL,NULL,NOW(),NOW());

-- attach owner role
INSERT IGNORE INTO user_roles (user_id, role_id, created_at)
SELECT u.id, r.id, NOW()
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';

-- allow owner at least first location (owner bypass anyway)
INSERT IGNORE INTO user_locations (user_id, location_id, created_at)
SELECT u.id, l.id, NOW()
FROM users u
JOIN locations l ON l.tenant_id=u.tenant_id
WHERE u.tenant_id=1 AND u.email='owner@rajawali.local'
LIMIT 1;
