-- KINET MikroTik Admin Module - Update v31 Reseller Portal + Permissions

CREATE TABLE IF NOT EXISTS reseller_module_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_id INT NOT NULL,
    module_key VARCHAR(80) NOT NULL,
    can_view TINYINT(1) NOT NULL DEFAULT 0,
    can_create TINYINT(1) NOT NULL DEFAULT 0,
    can_edit TINYINT(1) NOT NULL DEFAULT 0,
    can_delete TINYINT(1) NOT NULL DEFAULT 0,
    can_action TINYINT(1) NOT NULL DEFAULT 0,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_reseller_module (reseller_id, module_key),
    INDEX idx_reseller_perm_module (module_key),
    CONSTRAINT fk_reseller_perm_reseller FOREIGN KEY (reseller_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_reseller_perm_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO reseller_module_permissions (reseller_id, module_key, can_view, can_create, can_edit, can_delete, can_action)
SELECT u.id, x.module_key, x.can_view, x.can_create, x.can_edit, x.can_delete, x.can_action
FROM users u
JOIN roles r ON r.id=u.role_id
JOIN (
    SELECT 'customers' module_key, 1 can_view, 1 can_create, 1 can_edit, 0 can_delete, 0 can_action
    UNION ALL SELECT 'invoices', 1, 0, 0, 0, 1
    UNION ALL SELECT 'confirm_payment', 0, 0, 0, 0, 0
    UNION ALL SELECT 'vouchers', 1, 1, 0, 0, 1
    UNION ALL SELECT 'mikrotik', 1, 0, 0, 0, 0
    UNION ALL SELECT 'reports', 1, 0, 0, 0, 0
) x
WHERE r.code='reseller'
ON DUPLICATE KEY UPDATE module_key=VALUES(module_key);