-- Create roles table with hierarchy support
CREATE TABLE IF NOT EXISTS roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    role_slug VARCHAR(50) NOT NULL UNIQUE,
    parent_role_id INTEGER REFERENCES roles(role_id),
    is_system_role BOOLEAN DEFAULT false,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create modules table
CREATE TABLE IF NOT EXISTS modules (
    module_id SERIAL PRIMARY KEY,
    module_name VARCHAR(50) NOT NULL UNIQUE,
    module_slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create submodules table
CREATE TABLE IF NOT EXISTS submodules (
    submodule_id SERIAL PRIMARY KEY,
    module_id INTEGER REFERENCES modules(module_id) ON DELETE CASCADE,
    submodule_name VARCHAR(50) NOT NULL,
    submodule_slug VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(module_id, submodule_slug)
);

-- Create permissions table with more granular control
CREATE TABLE IF NOT EXISTS permissions (
    permission_id SERIAL PRIMARY KEY,
    submodule_id INTEGER REFERENCES submodules(submodule_id) ON DELETE CASCADE,
    permission_type VARCHAR(10) CHECK (permission_type IN ('view', 'edit', 'delete')) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (submodule_id, permission_type)
);

-- Create role_permissions junction table with inheritance flag
CREATE TABLE IF NOT EXISTS role_permissions (
    role_id INTEGER REFERENCES roles(role_id) ON DELETE CASCADE,
    permission_id INTEGER REFERENCES permissions(permission_id) ON DELETE CASCADE,
    is_inherited BOOLEAN DEFAULT false,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id, permission_id)
);

-- Create indexes for performance
CREATE INDEX idx_role_permissions_role_id ON role_permissions(role_id);
CREATE INDEX idx_role_permissions_permission_id ON role_permissions(permission_id);
CREATE INDEX idx_permissions_submodule_id ON permissions(submodule_id);
CREATE INDEX idx_submodules_module_id ON submodules(module_id);

-- Add trigger to update updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_roles_updated_at
    BEFORE UPDATE ON roles
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Create a view for easy permission checking
CREATE OR REPLACE VIEW effective_permissions AS
WITH RECURSIVE role_hierarchy AS (
    -- Base case: direct roles
    SELECT role_id, parent_role_id, 1 as level
    FROM roles
    WHERE parent_role_id IS NULL
    
    UNION ALL
    
    -- Recursive case: child roles
    SELECT r.role_id, r.parent_role_id, rh.level + 1
    FROM roles r
    INNER JOIN role_hierarchy rh ON r.parent_role_id = rh.role_id
)
SELECT DISTINCT
    r.role_id,
    r.role_name,
    m.module_name,
    sm.submodule_name,
    p.permission_type,
    CASE 
        WHEN rp.is_inherited THEN 'inherited'
        ELSE 'direct'
    END as grant_type
FROM role_hierarchy rh
JOIN roles r ON r.role_id = rh.role_id
JOIN role_permissions rp ON (r.role_id = rp.role_id OR rh.parent_role_id = rp.role_id)
JOIN permissions p ON rp.permission_id = p.permission_id
JOIN submodules sm ON p.submodule_id = sm.submodule_id
JOIN modules m ON sm.module_id = m.module_id;
