-- 003_auth_rbac.sql
-- Sprint 2: Auth + Users + Roles + Permissions + User Locations
-- NOTE: tenant_id type mengikuti tabel tenants/locations (INT UNSIGNED)

CREATE TABLE IF NOT EXISTS `roles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `code` VARCHAR(50) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `is_locked` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_tenant_code` (`tenant_id`, `code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `email` VARCHAR(190) NOT NULL,
  `full_name` VARCHAR(150) NOT NULL,
  `avatar_path` VARCHAR(255) NULL,
  `password_hash` VARCHAR(255) NOT 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 `uniq_tenant_email` (`tenant_id`, `email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `code` VARCHAR(120) NOT NULL,
  `module` VARCHAR(60) NOT NULL,
  `perm_action` VARCHAR(30) NOT NULL,
  `label` VARCHAR(150) NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_tenant_code` (`tenant_id`, `code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `role_permissions` (
  `tenant_id` INT UNSIGNED NOT NULL,
  `role_id` INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  KEY `idx_tenant` (`tenant_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;

CREATE TABLE IF NOT EXISTS `user_roles` (
  `tenant_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `role_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`user_id`, `role_id`),
  KEY `idx_tenant` (`tenant_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;

CREATE TABLE IF NOT EXISTS `user_locations` (
  `tenant_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `location_id` INT UNSIGNED NOT NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`user_id`, `location_id`),
  KEY `idx_tenant` (`tenant_id`),
  KEY `idx_user_default` (`user_id`, `is_default`),
  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;

-- Seed roles per tenant
INSERT INTO `roles` (`tenant_id`,`code`,`name`,`is_default`,`is_locked`,`created_at`,`updated_at`)
SELECT t.id, r.code, r.name, 1, r.locked, NOW(), NOW()
FROM `tenants` t
JOIN (
  SELECT 'owner' AS code, 'Pemilik' AS name, 1 AS locked
  UNION ALL SELECT 'admin','Admin',0
  UNION ALL SELECT 'kepala_toko','Kepala Toko',0
  UNION ALL SELECT 'kasir','Kasir',0
  UNION ALL SELECT 'karyawan','Karyawan',0
) r
LEFT JOIN `roles` ex ON ex.tenant_id=t.id AND ex.code=r.code
WHERE ex.id IS NULL;

-- Seed permission catalog per tenant (minimal: dashboard + settings + pos)
INSERT INTO `permissions` (`tenant_id`,`code`,`module`,`perm_action`,`label`,`created_at`)
SELECT t.id, p.code, p.module, p.action, p.label, NOW()
FROM `tenants` t
JOIN (
  SELECT 'dashboard.view' AS code, 'dashboard' AS module, 'view' AS action, 'Dashboard - View' AS label
  UNION ALL SELECT 'settings.locations.view','settings_locations','view','Lokasi Bisnis - View'
  UNION ALL SELECT 'settings.locations.add','settings_locations','add','Lokasi Bisnis - Add'
  UNION ALL SELECT 'settings.locations.edit','settings_locations','edit','Lokasi Bisnis - Edit'
  UNION ALL SELECT 'settings.branding.view','settings_branding','view','Branding - View'
  UNION ALL SELECT 'settings.branding.edit','settings_branding','edit','Branding - Edit'
  UNION ALL SELECT 'pos.view','pos','view','POS - View'
) p
LEFT JOIN `permissions` ex ON ex.tenant_id=t.id AND ex.code=p.code
WHERE ex.id IS NULL;

-- Seed owner user per tenant (email: owner@rajawali.local, password: owner)
INSERT INTO `users` (`tenant_id`,`email`,`full_name`,`avatar_path`,`password_hash`,`is_owner`,`is_active`,`created_at`,`updated_at`)
SELECT t.id, 'owner@rajawali.local', 'Owner', NULL, '$2b$10$EhrzGoHQ8DKvwmAcdi6CnOhQJr0Lt22J7WPEcoIF41b93n/b8LVu.', 1, 1, NOW(), NOW()
FROM `tenants` t
LEFT JOIN `users` u ON u.tenant_id=t.id AND u.is_owner=1
WHERE u.id IS NULL;

-- Attach owner role to owner user
INSERT INTO `user_roles` (`tenant_id`,`user_id`,`role_id`,`created_at`)
SELECT u.tenant_id, u.id, r.id, NOW()
FROM `users` u
JOIN `roles` r ON r.tenant_id=u.tenant_id AND r.code='owner'
LEFT JOIN `user_roles` ur ON ur.user_id=u.id AND ur.role_id=r.id
WHERE u.is_owner=1 AND ur.user_id IS NULL;

-- Owner & Admin get all permissions
INSERT INTO `role_permissions` (`tenant_id`,`role_id`,`permission_id`,`created_at`)
SELECT r.tenant_id, r.id, p.id, NOW()
FROM `roles` r
JOIN `permissions` p ON p.tenant_id=r.tenant_id
LEFT JOIN `role_permissions` rp ON rp.role_id=r.id AND rp.permission_id=p.id
WHERE r.code IN ('owner','admin') AND rp.role_id IS NULL;

-- Kasir: dashboard.view + pos.view
INSERT INTO `role_permissions` (`tenant_id`,`role_id`,`permission_id`,`created_at`)
SELECT r.tenant_id, r.id, p.id, NOW()
FROM `roles` r
JOIN `permissions` p ON p.tenant_id=r.tenant_id
LEFT JOIN `role_permissions` rp ON rp.role_id=r.id AND rp.permission_id=p.id
WHERE r.code='kasir' AND p.code IN ('dashboard.view','pos.view') AND rp.role_id IS NULL;

-- Kepala Toko: dashboard.view + pos.view + view settings
INSERT INTO `role_permissions` (`tenant_id`,`role_id`,`permission_id`,`created_at`)
SELECT r.tenant_id, r.id, p.id, NOW()
FROM `roles` r
JOIN `permissions` p ON p.tenant_id=r.tenant_id
LEFT JOIN `role_permissions` rp ON rp.role_id=r.id AND rp.permission_id=p.id
WHERE r.code='kepala_toko' AND p.code IN ('dashboard.view','pos.view','settings.locations.view','settings.branding.view') AND rp.role_id IS NULL;

-- Karyawan: dashboard.view
INSERT INTO `role_permissions` (`tenant_id`,`role_id`,`permission_id`,`created_at`)
SELECT r.tenant_id, r.id, p.id, NOW()
FROM `roles` r
JOIN `permissions` p ON p.tenant_id=r.tenant_id
LEFT JOIN `role_permissions` rp ON rp.role_id=r.id AND rp.permission_id=p.id
WHERE r.code='karyawan' AND p.code IN ('dashboard.view') AND rp.role_id IS NULL;

-- Map owner to all locations by default (tenant 1) if exists
INSERT INTO `user_locations` (`tenant_id`,`user_id`,`location_id`,`is_default`,`created_at`)
SELECT u.tenant_id, u.id, l.id, IF(l.id=1,1,0), NOW()
FROM `users` u
JOIN `locations` l ON l.tenant_id=u.tenant_id
LEFT JOIN `user_locations` ul ON ul.user_id=u.id AND ul.location_id=l.id
WHERE u.is_owner=1 AND ul.user_id IS NULL;
