-- Permissions tables for RBAC
CREATE TABLE IF NOT EXISTS permissions(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    strKey VARCHAR(100) NOT NULL UNIQUE,
    strName VARCHAR(200) NOT NULL,
    strGroup VARCHAR(100) NOT NULL,
    intStatus TINYINT DEFAULT 1,
    dtmCreated DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS role_permissions(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intRoleId INT NOT NULL,
    intPermissionId INT NOT NULL,
    FOREIGN KEY (intRoleId) REFERENCES roles(intId) ON DELETE CASCADE,
    FOREIGN KEY (intPermissionId) REFERENCES permissions(intId) ON DELETE CASCADE,
    UNIQUE KEY (intRoleId, intPermissionId)
);

CREATE TABLE IF NOT EXISTS user_permissions(
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intUserId INT NOT NULL,
    intPermissionId INT NOT NULL,
    intType TINYINT NOT NULL COMMENT '1=grant, 0=deny',
    FOREIGN KEY (intUserId) REFERENCES users(intId) ON DELETE CASCADE,
    FOREIGN KEY (intPermissionId) REFERENCES permissions(intId) ON DELETE CASCADE,
    UNIQUE KEY (intUserId, intPermissionId)
);

-- Seed permissions
INSERT INTO permissions (strKey, strName, strGroup) VALUES
-- Dashboard
('dashboard.view', 'View Dashboard', 'Dashboard'),
-- Master Data
('customers.view', 'View Customers', 'Master Data'),
('customers.create', 'Create Customers', 'Master Data'),
('customers.edit', 'Edit Customers', 'Master Data'),
('customers.delete', 'Delete Customers', 'Master Data'),
('suppliers.view', 'View Suppliers', 'Master Data'),
('suppliers.create', 'Create Suppliers', 'Master Data'),
('suppliers.edit', 'Edit Suppliers', 'Master Data'),
('suppliers.delete', 'Delete Suppliers', 'Master Data'),
('products.view', 'View Products', 'Master Data'),
('products.create', 'Create Products', 'Master Data'),
('products.edit', 'Edit Products', 'Master Data'),
('products.delete', 'Delete Products', 'Master Data'),
('categories.view', 'View Categories', 'Master Data'),
('categories.create', 'Create Categories', 'Master Data'),
('categories.edit', 'Edit Categories', 'Master Data'),
('categories.delete', 'Delete Categories', 'Master Data'),
('units.view', 'View Units', 'Master Data'),
('units.create', 'Create Units', 'Master Data'),
('units.edit', 'Edit Units', 'Master Data'),
('units.delete', 'Delete Units', 'Master Data'),
('warehouses.view', 'View Warehouses', 'Master Data'),
('warehouses.create', 'Create Warehouses', 'Master Data'),
('warehouses.edit', 'Edit Warehouses', 'Master Data'),
('warehouses.delete', 'Delete Warehouses', 'Master Data'),
-- Logistics
('vehicles.view', 'View Vehicles', 'Logistics'),
('vehicles.create', 'Create Vehicles', 'Logistics'),
('vehicles.edit', 'Edit Vehicles', 'Logistics'),
('vehicles.delete', 'Delete Vehicles', 'Logistics'),
('drivers.view', 'View Drivers', 'Logistics'),
('drivers.create', 'Create Drivers', 'Logistics'),
('drivers.edit', 'Edit Drivers', 'Logistics'),
('drivers.delete', 'Delete Drivers', 'Logistics'),
('branches.view', 'View Branches', 'Logistics'),
('branches.create', 'Create Branches', 'Logistics'),
('branches.edit', 'Edit Branches', 'Logistics'),
('branches.delete', 'Delete Branches', 'Logistics'),
-- Orders
('purchases.view', 'View Purchase Orders', 'Orders'),
('purchases.create', 'Create Purchase Orders', 'Orders'),
('purchases.edit', 'Edit Purchase Orders', 'Orders'),
('purchases.approve', 'Approve Purchase Orders', 'Orders'),
('purchase-payments.view', 'View Purchase Payments', 'Orders'),
('purchase-payments.create', 'Create Purchase Payments', 'Orders'),
('sales.view', 'View Sales Orders', 'Orders'),
('sales.create', 'Create Sales Orders', 'Orders'),
('sales.edit', 'Edit Sales Orders', 'Orders'),
('sales.approve', 'Approve Sales Orders', 'Orders'),
('deliveries.view', 'View Deliveries', 'Orders'),
('deliveries.create', 'Create Deliveries', 'Orders'),
('deliveries.edit', 'Edit Deliveries', 'Orders'),
('returns.view', 'View Returns', 'Orders'),
('returns.create', 'Create Returns', 'Orders'),
('returns.edit', 'Edit Returns', 'Orders'),
('returns.approve', 'Approve Returns', 'Orders'),
('returns.audit', 'Audit Returns', 'Orders'),
-- Inventory
('stock.in', 'Stock In', 'Inventory'),
('stock.out', 'Stock Out', 'Inventory'),
('stock.transactions', 'View Stock Transactions', 'Inventory'),
-- Finance
('payments.view', 'View Payments', 'Finance'),
('payments.create', 'Create Payments', 'Finance'),
('cheques.view', 'View Cheques', 'Finance'),
('cheques.edit', 'Edit Cheques', 'Finance'),
('banks.view', 'View Banks', 'Finance'),
('banks.create', 'Create Banks', 'Finance'),
('banks.edit', 'Edit Banks', 'Finance'),
('banks.delete', 'Delete Banks', 'Finance'),
-- System
('users.view', 'View Users', 'System'),
('users.create', 'Create Users', 'System'),
('users.edit', 'Edit Users', 'System'),
('users.delete', 'Delete Users', 'System'),
('roles.view', 'View Roles', 'System'),
('roles.create', 'Create Roles', 'System'),
('roles.edit', 'Edit Roles', 'System'),
('roles.delete', 'Delete Roles', 'System'),
-- Reports
('reports.sales', 'Sales Report', 'Reports'),
('reports.inventory', 'Inventory Report', 'Reports'),
('reports.customers', 'Customer Report', 'Reports'),
('reports.deliveries', 'Delivery Report', 'Reports'),
('reports.payments', 'Payment Report', 'Reports'),
('reports.user-wise', 'User Wise Report', 'Reports'),
('reports.stock-movements', 'Stock Movements Report', 'Reports'),
('reports.purchases', 'Purchase Report', 'Reports'),
-- Settings
('company.edit', 'Edit Company Info', 'Settings'),
('currencies.view', 'View Currencies', 'Settings');

-- Assign all permissions to Admin role (id=1)
INSERT INTO role_permissions (intRoleId, intPermissionId)
SELECT 1, intId FROM permissions;