-- TokoBisnisku Multi-Tenant Database Schema
-- Compatible with MySQL 5.7+ / MariaDB 10.2+

CREATE DATABASE IF NOT EXISTS `tokobisnisku` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `tokobisnisku`;

-- 1. COMPANIES (Tenants)
CREATE TABLE IF NOT EXISTS `companies` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(150) NOT NULL,
  `address` TEXT NULL,
  `phone` VARCHAR(30) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. BRANCHES
CREATE TABLE IF NOT EXISTS `branches` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `code` VARCHAR(50) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `address` TEXT NULL,
  `phone` VARCHAR(30) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uk_company_branch_code` (`company_id`, `code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. ROLES
CREATE TABLE IF NOT EXISTS `roles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NULL, -- NULL means global system role (e.g., PROGRAMER)
  `name` VARCHAR(50) NOT NULL,
  `display_name` VARCHAR(100) NOT NULL,
  `description` TEXT NULL,
  `is_system` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. PERMISSIONS
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `module` VARCHAR(50) NOT NULL,
  `description` VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. ROLE_PERMISSIONS
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role_id` INT NOT NULL,
  `permission_id` INT NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. USERS
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(30) NULL,
  `role_id` INT NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `remember_token` VARCHAR(100) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. USER_BRANCHES
CREATE TABLE IF NOT EXISTS `user_branches` (
  `user_id` INT NOT NULL,
  `branch_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `branch_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. CATEGORIES
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. PRODUCTS
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `sku` VARCHAR(50) NOT NULL,
  `barcode` VARCHAR(100) NULL,
  `nama` VARCHAR(200) NOT NULL,
  `kategori_id` INT NULL,
  `satuan` VARCHAR(50) NOT NULL DEFAULT 'Pcs',
  `harga_beli` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `harga_jual` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `stok_minimum` INT NOT NULL DEFAULT 5,
  `aktif` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`kategori_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL,
  UNIQUE KEY `uk_company_sku` (`company_id`, `sku`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. STOCK_MOVEMENTS
CREATE TABLE IF NOT EXISTS `stock_movements` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `branch_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `reference_type` VARCHAR(50) NOT NULL, -- PURCHASE, SALE, SALE_RETURN, PURCHASE_RETURN, ADJUSTMENT_IN, ADJUSTMENT_OUT, TRANSFER_IN, TRANSFER_OUT, INITIAL_STOCK
  `reference_id` VARCHAR(100) NULL,
  `movement_type` ENUM('IN', 'OUT') NOT NULL,
  `qty` DECIMAL(12,2) NOT NULL,
  `unit_cost` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `notes` TEXT NULL,
  `created_by` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11. CUSTOMERS
CREATE TABLE IF NOT EXISTS `customers` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `code` VARCHAR(50) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(30) NULL,
  `email` VARCHAR(100) NULL,
  `address` TEXT NULL,
  `latitude` DECIMAL(10,8) NULL,
  `longitude` DECIMAL(11,8) NULL,
  `credit_limit` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `payment_term` INT NOT NULL DEFAULT 30, -- In days
  `notes` TEXT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uk_company_customer_code` (`company_id`, `code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 12. SALES (POS / Order Header)
CREATE TABLE IF NOT EXISTS `sales` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `client_id` VARCHAR(100) NOT NULL UNIQUE, -- Idempotency key from mobile
  `company_id` INT NOT NULL,
  `branch_id` INT NOT NULL,
  `invoice_number` VARCHAR(50) NOT NULL,
  `customer_id` INT NULL,
  `sales_user_id` INT NOT NULL,
  `transaction_date` DATETIME NOT NULL,
  `subtotal` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `discount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `tax` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `grand_total` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `payment_status` ENUM('UNPAID', 'PARTIAL', 'PAID') NOT NULL DEFAULT 'UNPAID',
  `notes` TEXT NULL,
  `created_by` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`sales_user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
  UNIQUE KEY `uk_company_invoice` (`company_id`, `invoice_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 13. SALE_DETAILS
CREATE TABLE IF NOT EXISTS `sale_details` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sale_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `qty` DECIMAL(12,2) NOT NULL,
  `price` DECIMAL(15,2) NOT NULL,
  `discount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `subtotal` DECIMAL(15,2) NOT NULL,
  `cost` DECIMAL(15,2) NOT NULL DEFAULT 0.00, -- HPP per unit at transaction time
  FOREIGN KEY (`sale_id`) REFERENCES `sales`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 14. PAYMENTS
CREATE TABLE IF NOT EXISTS `payments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `branch_id` INT NOT NULL,
  `sale_id` INT NOT NULL,
  `customer_id` INT NULL,
  `payment_number` VARCHAR(50) NOT NULL,
  `payment_date` DATETIME NOT NULL,
  `payment_method` ENUM('CASH', 'TRANSFER', 'QRIS', 'CREDIT', 'OTHER') NOT NULL DEFAULT 'CASH',
  `amount` DECIMAL(15,2) NOT NULL,
  `reference_number` VARCHAR(100) NULL,
  `notes` TEXT NULL,
  `created_by` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`sale_id`) REFERENCES `sales`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 15. RECEIVABLES (Piutang)
CREATE TABLE IF NOT EXISTS `receivables` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `branch_id` INT NOT NULL,
  `sale_id` INT NOT NULL,
  `customer_id` INT NOT NULL,
  `invoice_number` VARCHAR(50) NOT NULL,
  `transaction_date` DATETIME NOT NULL,
  `due_date` DATE NOT NULL,
  `total_amount` DECIMAL(15,2) NOT NULL,
  `paid_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `remaining_amount` DECIMAL(15,2) NOT NULL,
  `status` ENUM('UNPAID', 'PARTIAL', 'PAID', 'OVERDUE') NOT NULL DEFAULT 'UNPAID',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`sale_id`) REFERENCES `sales`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 16. RECEIVABLE_PAYMENTS (Histori Pelunasan Piutang)
CREATE TABLE IF NOT EXISTS `receivable_payments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `receivable_id` INT NOT NULL,
  `payment_id` INT NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  `payment_date` DATETIME NOT NULL,
  `notes` TEXT NULL,
  `created_by` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`receivable_id`) REFERENCES `receivables`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`payment_id`) REFERENCES `payments`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 17. SALES_VISITS (Sales Lapangan & GPS)
CREATE TABLE IF NOT EXISTS `sales_visits` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NOT NULL,
  `branch_id` INT NOT NULL,
  `sales_user_id` INT NOT NULL,
  `customer_id` INT NOT NULL,
  `visit_date` DATETIME NOT NULL,
  `latitude` DECIMAL(10,8) NULL,
  `longitude` DECIMAL(11,8) NULL,
  `accuracy` FLOAT NULL,
  `photo` LONGTEXT NULL,
  `notes` TEXT NULL,
  `status` ENUM('PLANNED', 'VISITED', 'ORDER', 'CANCELLED') NOT NULL DEFAULT 'VISITED',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`sales_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 18. MODULES (Dynamic System Modules)
CREATE TABLE IF NOT EXISTS `modules` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) NULL,
  `icon` VARCHAR(50) NOT NULL DEFAULT 'view-dashboard',
  `route_name` VARCHAR(100) NOT NULL,
  `min_permission` VARCHAR(100) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 19. AUDIT_LOGS
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_id` INT NULL,
  `user_id` INT NULL,
  `action` VARCHAR(50) NOT NULL,
  `module` VARCHAR(50) NOT NULL,
  `record_id` VARCHAR(100) NULL,
  `old_data` LONGTEXT NULL,
  `new_data` LONGTEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 20. PERSONAL_ACCESS_TOKENS
CREATE TABLE IF NOT EXISTS `personal_access_tokens` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `token_hash` VARCHAR(255) NOT NULL,
  `name` VARCHAR(100) NOT NULL DEFAULT 'mobile_app',
  `expires_at` DATETIME NULL,
  `last_used_at` DATETIME NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 21. PASSWORD_RESETS
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(100) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ========================================================
-- INITIAL SEED DATA
-- ========================================================

-- Seed Companies
INSERT INTO `companies` (`id`, `code`, `name`, `address`, `phone`, `is_active`) VALUES
(1, 'CMP001', 'PT Toko Bisnis Utama', 'Jl. Merdeka No. 45, Jakarta Pusat', '021-5550123', 1),
(2, 'CMP002', 'Toko Berkah Jaya', 'Jl. Sudirman No. 12, Surabaya', '031-4440987', 1);

-- Seed Branches
INSERT INTO `branches` (`id`, `company_id`, `code`, `name`, `address`, `phone`, `is_active`) VALUES
(1, 1, 'BR-PST', 'Cabang Pusat Jakarta', 'Jl. Merdeka No. 45, Jakarta Pusat', '021-5550123', 1),
(2, 1, 'BR-BDG', 'Cabang Bandung', 'Jl. Asia Afrika No. 88, Bandung', '022-7771234', 1),
(3, 2, 'BR-SBY', 'Cabang Utama Surabaya', 'Jl. Sudirman No. 12, Surabaya', '031-4440987', 1);

-- Seed Permissions
INSERT INTO `permissions` (`id`, `name`, `module`, `description`) VALUES
(1, 'dashboard.view', 'dashboard', 'Melihat Dashboard Bisnis'),
(2, 'product.view', 'product', 'Melihat Daftar Produk'),
(3, 'product.create', 'product', 'Menambah Produk Baru'),
(4, 'product.update', 'product', 'Mengubah Data Produk'),
(5, 'product.delete', 'product', 'Menghapus/Nonaktifkan Produk'),
(6, 'stock.view', 'stock', 'Melihat Stok & Movement'),
(7, 'stock.adjust', 'stock', 'Melakukan Stock Adjustment & Opname'),
(8, 'sales.view', 'sales', 'Melihat Transaksi Penjualan'),
(9, 'sales.create', 'sales', 'Membuat Transaksi Penjualan'),
(10, 'sales.cancel', 'sales', 'Pembatalan Penjualan'),
(11, 'purchase.view', 'purchase', 'Melihat Pembelian'),
(12, 'purchase.create', 'purchase', 'Membuat Pembelian'),
(13, 'customer.view', 'customer', 'Melihat Data Pelanggan'),
(14, 'customer.create', 'customer', 'Menambah Pelanggan Baru'),
(15, 'payment.create', 'payment', 'Mencatat Pembayaran'),
(16, 'receivable.view', 'receivable', 'Melihat Daftar Piutang'),
(17, 'report.view', 'report', 'Melihat Laporan Bisnis'),
(18, 'report.profit', 'report', 'Melihat Laporan Laba Kotor & HPP'),
(19, 'user.manage', 'setting', 'Manajemen User'),
(20, 'branch.manage', 'setting', 'Manajemen Cabang'),
(21, 'sales_visit.create', 'sales_visit', 'Mencatat Kunjungan Sales'),
(22, 'sales_visit.view', 'sales_visit', 'Melihat Histori Kunjungan Sales'),
(23, 'ai.assistant', 'ai', 'Akses AI Business Assistant'),
(24, 'role.manage', 'programmer', 'Manajemen Role & Hak Akses'),
(25, 'module.manage', 'programmer', 'Manajemen Modul Aplikasi');

-- Seed Roles
INSERT INTO `roles` (`id`, `company_id`, `name`, `display_name`, `description`, `is_system`) VALUES
(1, NULL, 'PROGRAMER', 'Programmer / Super Admin', 'Akses Penuh ke Seluruh Modul & Manajemen Role/Modul', 1),
(2, NULL, 'OWNER', 'Pemilik Perusahaan (Owner)', 'Akses Penuh Laporan, Dashboard Konsolidasi, & Manajemen Bisnis', 1),
(3, NULL, 'MANAGER', 'Manager Cabang', 'Pengelola Operasional Cabang & Stok', 1),
(4, NULL, 'CASHIER', 'Kasir (Cashier)', 'Fokus Transaksi Penjualan POS & Pembayaran', 1),
(5, NULL, 'WAREHOUSE', 'Petugas Gudang (Warehouse)', 'Pengelolaan Stok, Stock Movement & Opname', 1),
(6, NULL, 'SALES', 'Sales Lapangan (Sales Rep)', 'Mencatat Kunjungan Sales, GPS, & Input Order Pelanggan', 1),
(7, NULL, 'ADMIN', 'Administrator Toko', 'Kelola Produk, Pelanggan, & Transaksi Harian', 1);

-- Assign all permissions to PROGRAMER
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, id FROM `permissions`;

-- Assign OWNER permissions (all except programmer tools)
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 2, id FROM `permissions` WHERE `name` NOT IN ('role.manage', 'module.manage');

-- Assign CASHIER permissions
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 4, id FROM `permissions` WHERE `name` IN ('dashboard.view', 'product.view', 'sales.view', 'sales.create', 'customer.view', 'payment.create');

-- Assign SALES permissions
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 6, id FROM `permissions` WHERE `name` IN ('dashboard.view', 'product.view', 'customer.view', 'customer.create', 'sales.create', 'sales_visit.create', 'sales_visit.view');

-- Seed Users (Passwords hashed using BCRYPT for 'password123')
INSERT INTO `users` (`id`, `company_id`, `username`, `email`, `password_hash`, `full_name`, `phone`, `role_id`, `is_active`) VALUES
(1, 1, 'programmer', 'programmer@tokobisnisku.id', '$2y$10$e0MYzXyjpJS7Pd0RVvHwHe1FzM7gV/1T6p2w.0qFh/sH06g4P9zGG', 'Sani Programmer', '081234567890', 1, 1),
(2, 1, 'owner', 'owner@tokobisnisku.id', '$2y$10$e0MYzXyjpJS7Pd0RVvHwHe1FzM7gV/1T6p2w.0qFh/sH06g4P9zGG', 'Bapak Owner', '081299988877', 2, 1),
(3, 1, 'sales1', 'sales1@tokobisnisku.id', '$2y$10$e0MYzXyjpJS7Pd0RVvHwHe1FzM7gV/1T6p2w.0qFh/sH06g4P9zGG', 'Budi Sales', '081377766655', 6, 1),
(4, 2, 'owner2', 'owner@berkahjaya.com', '$2y$10$e0MYzXyjpJS7Pd0RVvHwHe1FzM7gV/1T6p2w.0qFh/sH06g4P9zGG', 'Haji Berkah', '081122334455', 2, 1);

-- Seed User Branches Access
INSERT INTO `user_branches` (`user_id`, `branch_id`) VALUES
(1, 1), (1, 2),
(2, 1), (2, 2),
(3, 1),
(4, 3);

-- Seed Categories
INSERT INTO `categories` (`id`, `company_id`, `name`, `description`) VALUES
(1, 1, 'Sembako', 'Kebutuhan Pokok Harian'),
(2, 1, 'Minuman', 'Minuman Kemasan & Botol'),
(3, 1, 'Elektronik', 'Peralatan Elektronik & Gadget');

-- Seed Products
INSERT INTO `products` (`id`, `company_id`, `sku`, `barcode`, `nama`, `kategori_id`, `satuan`, `harga_beli`, `harga_jual`, `stok_minimum`, `aktif`) VALUES
(1, 1, 'PRD-001', '899999900001', 'Beras Ramos 5kg', 1, 'Karung', 55000.00, 68000.00, 10, 1),
(2, 1, 'PRD-002', '899999900002', 'Minyak Goreng 2L', 1, 'Pouch', 28000.00, 34000.00, 15, 1),
(3, 1, 'PRD-003', '899999900003', 'Air Mineral 600ml Box', 2, 'Karton', 38000.00, 48000.00, 5, 1),
(4, 1, 'PRD-004', '899999900004', 'Lampu LED 12W', 3, 'Pcs', 22000.00, 32000.00, 8, 1);

-- Initial Stock Movements
INSERT INTO `stock_movements` (`company_id`, `branch_id`, `product_id`, `reference_type`, `reference_id`, `movement_type`, `qty`, `unit_cost`, `notes`, `created_by`) VALUES
(1, 1, 1, 'INITIAL_STOCK', 'INIT-001', 'IN', 50.00, 55000.00, 'Saldo Awal Stok Jakarta', 1),
(1, 1, 2, 'INITIAL_STOCK', 'INIT-002', 'IN', 100.00, 28000.00, 'Saldo Awal Stok Jakarta', 1),
(1, 1, 3, 'INITIAL_STOCK', 'INIT-003', 'IN', 30.00, 38000.00, 'Saldo Awal Stok Jakarta', 1),
(1, 1, 4, 'INITIAL_STOCK', 'INIT-004', 'IN', 4.00, 22000.00, 'Saldo Awal Stok Jakarta (Hampir Habis)', 1),
(1, 2, 1, 'INITIAL_STOCK', 'INIT-005', 'IN', 20.00, 55000.00, 'Saldo Awal Stok Bandung', 1);

-- Seed Customers
INSERT INTO `customers` (`id`, `company_id`, `code`, `name`, `phone`, `email`, `address`, `latitude`, `longitude`, `credit_limit`, `payment_term`, `notes`, `active`) VALUES
(1, 1, 'CUST-001', 'Toko Rejeki', '081288887777', 'rejeki@gmail.com', 'Jl. Mangga Dua No. 10, Jakarta', -6.13520000, 106.83100000, 10000000.00, 14, 'Pelanggan Grosir Utama', 1),
(2, 1, 'CUST-002', 'Warung Bu Siti', '081399990000', 'siti@yahoo.com', 'Jl. Kebon Jeruk No. 5, Jakarta', -6.19000000, 106.78000000, 2000000.00, 7, 'Pelanggan Eceran', 1);

-- Seed System Modules
INSERT INTO `modules` (`id`, `code`, `name`, `description`, `icon`, `route_name`, `min_permission`, `is_active`, `sort_order`) VALUES
(1, 'dashboard', 'Dashboard Bisnis', 'Metrik Penjualan, Laba, & Summary Bisnis', 'view-dashboard', 'Dashboard', 'dashboard.view', 1, 1),
(2, 'products', 'Manajemen Produk', 'Katalog Barang & Initial Stock', 'package-variant-closed', 'ProductList', 'product.view', 1, 2),
(3, 'stock', 'Pergerakan Stok', 'Stock Movement, Opname & Transfer', 'warehouse', 'StockMovement', 'stock.view', 1, 3),
(4, 'sales', 'Penjualan (POS)', 'Transaksi Kasir & Input Order', 'cart-outline', 'SalesPOS', 'sales.view', 1, 4),
(5, 'receivables', 'Daftar Piutang', 'Monitoring & Pelunasan Piutang', 'account-cash', 'ReceivablesList', 'receivable.view', 1, 5),
(6, 'customers', 'Pelanggan', 'Manajemen Data Pelanggan', 'account-group', 'CustomerList', 'customer.view', 1, 6),
(7, 'sales_visit', 'Kunjungan Sales GPS', 'Check-in Sales Lapangan & GPS Tracker', 'map-marker-path', 'SalesVisitList', 'sales_visit.view', 1, 7),
(8, 'ai_assistant', 'AI Business Assistant', 'Asisten Cerdas Analisis Performa Bisnis', 'robot', 'AiAssistant', 'ai.assistant', 1, 8),
(9, 'reports', 'Laporan & Akuntansi', 'Laporan Bisnis, Rugi Laba & Neraca', 'file-chart', 'Reports', 'report.view', 1, 9),
(10, 'role_management', 'Manajemen Role', 'Kelola Role & Hak Akses Granular', 'shield-account', 'RoleManagement', 'role.manage', 1, 10),
(11, 'module_management', 'Manajemen Modul', 'Kelola Modul Dinamis Sistem', 'apps', 'ModuleManagement', 'module.manage', 1, 11),
(12, 'audit_log', 'Audit Log Security', 'Rekap Keamanan & Jejak Aktivitas User', 'history', 'AuditLog', 'audit.view', 1, 12);