-- Migration: Add Roles and Permissions System
-- This migration creates a flexible role-based permission system

-- ============================================================
-- ROLES TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS `roles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `is_system` TINYINT(1) DEFAULT 0 COMMENT '1 = default system role, 0 = custom role',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- PERMISSIONS TABLE (Menu-based permissions)
-- ============================================================
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL COMMENT 'e.g., dashboard.view, products.create',
  `display_name` VARCHAR(150) NOT NULL COMMENT 'Human-readable name',
  `description` TEXT,
  `category` VARCHAR(50) NOT NULL COMMENT 'e.g., overview, inventory, transactions, contacts, admin',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- ROLE_PERMISSIONS PIVOT TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_permission` (`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;

-- ============================================================
-- UPDATE USERS TABLE TO REFERENCE ROLES
-- ============================================================
ALTER TABLE `users`
ADD COLUMN `role_id` INT UNSIGNED DEFAULT NULL AFTER `role`,
ADD INDEX `role_id` (`role_id`),
ADD FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL;

-- ============================================================
-- SEED DEFAULT PERMISSIONS (Based on menu structure)
-- ============================================================
INSERT IGNORE INTO `permissions` (`name`, `display_name`, `description`, `category`) VALUES
-- Overview
('dashboard.view', 'View Dashboard', 'Access to dashboard overview', 'overview'),
('dashboard.sales_today', 'View Sales Today', 'View Sales Today stat card', 'overview'),
('dashboard.purchase_today', 'View Purchase Today', 'View Purchase Today stat card', 'overview'),
('dashboard.total_products', 'View Total Products', 'View Total Products stat card', 'overview'),
('dashboard.alerts', 'View Alerts', 'View Alerts stat card', 'overview'),
('dashboard.total_month', 'View Total Month', 'View Total This Month stat card', 'overview'),
('dashboard.revenue_chart', 'View Revenue Chart', 'View Revenue chart (Last 7 Days)', 'overview'),
('dashboard.recent_sales', 'View Recent Sales', 'View Recent Sales table', 'overview'),
('dashboard.low_stock', 'View Low Stock', 'View Low Stock section', 'overview'),
('dashboard.expiring', 'View Expiring', 'View Expiring Products section', 'overview'),

-- Inventory
('products.view', 'View Products', 'View products list', 'inventory'),
('products.create', 'Create Products', 'Create new products', 'inventory'),
('products.edit', 'Edit Products', 'Edit existing products', 'inventory'),
('products.delete', 'Delete Products', 'Delete products', 'inventory'),
('categories.manage', 'Manage Categories', 'Manage product categories', 'inventory'),
('units.manage', 'Manage Units', 'Manage measurement units', 'inventory'),
('stock.view', 'View Stock', 'View stock levels', 'inventory'),
('stock.adjust', 'Adjust Stock', 'Perform stock adjustments', 'inventory'),
('stock.transfer', 'Transfer Stock', 'Transfer stock between locations', 'inventory'),
('stock.history', 'View Stock History', 'View stock movement history', 'inventory'),
('batches.manage', 'Manage Batches', 'Manage product batches', 'inventory'),

-- Transactions
('sales.view', 'View Sales', 'View sales history', 'transactions'),
('sales.create', 'Create Sales', 'Create new sales', 'transactions'),
('sales.edit', 'Edit Sales', 'Edit existing sales', 'transactions'),
('sales.delete', 'Delete Sales', 'Delete sales', 'transactions'),
('pos.access', 'Access POS Terminal', 'Access POS terminal', 'transactions'),
('purchases.view', 'View Purchases', 'View purchase history', 'transactions'),
('purchases.create', 'Create Purchases', 'Create new purchases', 'transactions'),
('purchases.edit', 'Edit Purchases', 'Edit existing purchases', 'transactions'),
('purchases.delete', 'Delete Purchases', 'Delete purchases', 'transactions'),

-- Contacts
('customers.view', 'View Customers', 'View customers list', 'contacts'),
('customers.create', 'Create Customers', 'Create new customers', 'contacts'),
('customers.edit', 'Edit Customers', 'Edit existing customers', 'contacts'),
('customers.delete', 'Delete Customers', 'Delete customers', 'contacts'),
('suppliers.view', 'View Suppliers', 'View suppliers list', 'contacts'),
('suppliers.create', 'Create Suppliers', 'Create new suppliers', 'contacts'),
('suppliers.edit', 'Edit Suppliers', 'Edit existing suppliers', 'contacts'),
('suppliers.delete', 'Delete Suppliers', 'Delete suppliers', 'contacts'),

-- Ledger
('supplier_ledger.view', 'View Supplier Ledger', 'View supplier ledger entries', 'ledger'),
('supplier_ledger.create', 'Create Supplier Ledger Entry', 'Create manual supplier ledger entries', 'ledger'),
('supplier_ledger.manage', 'Manage Supplier Ledger', 'Manage supplier ledger operations', 'ledger'),
('customer_ledger.view', 'View Customer Ledger', 'View customer ledger entries', 'ledger'),
('customer_ledger.create', 'Create Customer Ledger Entry', 'Create manual customer ledger entries', 'ledger'),
('customer_ledger.manage', 'Manage Customer Ledger', 'Manage customer ledger operations', 'ledger'),

-- Reports & Analytics
('reports.view', 'View Reports', 'Access reports and analytics', 'reports'),
('reports.sales', 'Sales Reports', 'View sales reports', 'reports'),
('reports.inventory', 'Inventory Reports', 'View inventory reports', 'reports'),
('reports.expiry', 'Expiry Reports', 'View expiry reports', 'reports'),
('reports.stock_valuation', 'Stock Valuation', 'View stock valuation reports', 'reports'),
('reports.profit_loss', 'Profit & Loss', 'View profit and loss reports', 'reports'),
('reports.product_sales', 'Product Sales Reports', 'View product sales reports', 'reports'),
('reports.daily_stock_monitoring', 'Daily Stock Monitoring', 'View daily stock monitoring reports', 'reports'),
('reports.stock_movement', 'Stock Movement', 'View stock movement reports', 'reports'),
('reports.stock_trending', 'Stock Trending', 'View stock trending reports', 'reports'),
('reports.ai_analytics', 'AI Analytics', 'View AI analytics reports', 'reports'),
('reports.tax', 'Tax Reports', 'View tax reports', 'reports'),
('reports.stock_summary', 'Stock Summary', 'View stock summary reports', 'reports'),
('reports.purchase_items', 'Purchase Items Reports', 'View purchase items reports', 'reports'),
('reports.top_suppliers', 'Top Suppliers', 'View top suppliers reports', 'reports'),
('reports.purchase_pay_due', 'Purchase Pay Due', 'View purchase payment due reports', 'reports'),

-- Admin
('tax.manage', 'Manage Tax Groups', 'Manage tax groups', 'admin'),
('users.manage', 'Manage Users', 'Manage user accounts', 'admin'),
('roles.manage', 'Manage Roles', 'Manage roles and permissions', 'admin'),
('settings.view', 'View Settings', 'Access settings', 'admin'),
('settings.edit', 'Edit Settings', 'Edit business settings', 'admin'),

-- Billing
('billing.view', 'View Billing', 'Access billing information', 'billing'),
('billing.manage', 'Manage Billing', 'Manage subscription and payments', 'billing'),
('locations.manage', 'Manage Locations', 'Manage business locations', 'admin'),
('notifications.view', 'View Notifications', 'Access notifications', 'admin');
