use socle ;
-- Create the profile_role_permissions table with a foreign key reference to solution_menu
CREATE TABLE profile_role_permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,    -- Auto-incremented primary key
    profileId INT,                        -- Foreign key to the profiles table
    moduleId INT,                         -- Foreign key to the Modules table
    canView BOOLEAN DEFAULT FALSE,        -- Permission to view
    canCreate BOOLEAN DEFAULT FALSE,      -- Permission to create
    canEdit BOOLEAN DEFAULT FALSE,        -- Permission to edit
    canImport BOOLEAN DEFAULT FALSE,      -- Permission to import
    canSearch BOOLEAN DEFAULT FALSE,      -- Permission to search
    canDelete BOOLEAN DEFAULT FALSE,      -- Permission to delete
	canDisable BOOLEAN DEFAULT FALSE,     -- Permission to disable
    FOREIGN KEY (profileId) REFERENCES company_profile(id),  -- Linking profileId to profiles table
    FOREIGN KEY (moduleId) REFERENCES solution_menu(id)     -- Linking moduleId to solution_menu table
);

INSERT INTO profile_role_permissions (profileId, moduleId, canView, canCreate, canEdit, canImport, canSearch, canDelete, canDisable)
VALUES
(1, 1, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE);  -- profile User can view, create, edit, import, search, delete, but cannot disable

INSERT INTO profile_role_permissions (profileId, moduleId, canView, canCreate, canEdit, canImport, canSearch, canDelete, canDisable)
VALUES
(2, 2, TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, FALSE);  -- profile User  can view, create, edit, search, but cannot delete or disable

INSERT INTO profile_role_permissions (profileId, moduleId, canView, canCreate, canEdit, canImport, canSearch, canDelete, canDisable)
VALUES
(3, 3, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE);  -- profile User can view and search reports, but cannot create, edit, import, delete, or disable