-- Drop existing tables and views
DROP VIEW IF EXISTS effective_permissions CASCADE;
DROP TABLE IF EXISTS role_permissions CASCADE;
DROP TABLE IF EXISTS submodules CASCADE;
DROP TABLE IF EXISTS modules CASCADE;
DROP TABLE IF EXISTS permission_types CASCADE;

-- Create modules table
CREATE TABLE modules (
    module_id SERIAL PRIMARY KEY,
    module_name VARCHAR(50) NOT NULL,
    module_slug VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT modules_name_key UNIQUE(module_name),
    CONSTRAINT modules_slug_key UNIQUE(module_slug)
);

-- Create submodules table
CREATE TABLE submodules (
    submodule_id SERIAL PRIMARY KEY,
    module_id INTEGER NOT NULL,
    submodule_name VARCHAR(50) NOT NULL,
    submodule_slug VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (module_id) REFERENCES modules(module_id),
    CONSTRAINT submodules_name_key UNIQUE(module_id, submodule_name),
    CONSTRAINT submodules_slug_key UNIQUE(module_id, submodule_slug)
);

-- Create permission_types table
CREATE TABLE permission_types (
    permission_type_id SERIAL PRIMARY KEY,
    permission_type VARCHAR(20) NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT permission_type_key UNIQUE(permission_type)
);

-- Create role_permissions table
CREATE TABLE role_permissions (
    role_permission_id SERIAL PRIMARY KEY,
    role_id INTEGER NOT NULL,
    module_id INTEGER NOT NULL,
    submodule_id INTEGER NOT NULL,
    permission_type_id INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(role_id),
    FOREIGN KEY (module_id) REFERENCES modules(module_id),
    FOREIGN KEY (submodule_id) REFERENCES submodules(submodule_id),
    FOREIGN KEY (permission_type_id) REFERENCES permission_types(permission_type_id),
    CONSTRAINT role_permission_key UNIQUE(role_id, module_id, submodule_id, permission_type_id)
);

-- Insert default permission types
INSERT INTO permission_types (permission_type, description) VALUES 
    ('view', 'Can view items'),
    ('edit', 'Can create and edit items'),
    ('delete', 'Can delete items')
ON CONFLICT ON CONSTRAINT permission_type_key DO UPDATE SET 
    description = EXCLUDED.description;

-- Insert default modules
INSERT INTO modules (module_name, module_slug, description) VALUES 
    ('Home', 'home', 'Home module for basic information'),
    ('Admin', 'admin', 'Administrative functions'),
    ('Providers', 'providers', 'Provider management'),
    ('Services', 'services', 'Service management')
ON CONFLICT ON CONSTRAINT modules_name_key DO UPDATE SET 
    module_slug = EXCLUDED.module_slug,
    description = EXCLUDED.description;

-- Insert default submodules with slugs
WITH module_data AS (
    SELECT module_id, module_name FROM modules
)
INSERT INTO submodules (module_id, submodule_name, submodule_slug, description)
SELECT 
    m.module_id,
    sm.name,
    LOWER(REPLACE(sm.name, ' ', '_')),
    sm.description
FROM module_data m
CROSS JOIN (
    VALUES 
        ('Home', 'Demographic Information', 'Basic demographic information'),
        ('Home', 'Operational Detail', 'Operational details and settings'),
        ('Home', 'Financial Information', 'Financial details and settings'),
        ('Home', 'Services Information', 'Services details and settings'),
        ('Admin', 'Roles Management', 'Manage user roles and permissions'),
        ('Admin', 'User Management', 'Manage system users'),
        ('Admin', 'Dashboard Administration', 'Manage system settings'),
        ('Providers', 'Register Provider', 'Register new service providers'),
        ('Providers', 'Approve Provider', 'Approve or reject  providers'),
        ('Providers', 'Master Data Provider', 'Master data for providers'),
        ('Services', 'Register Service', 'Register new services'),
        ('Services', 'Approve Service Provider', 'Approve or reject service provider'),
        ('Services', 'Service Directory', 'View service directory')
) AS sm(module, name, description)
WHERE m.module_name = sm.module
ON CONFLICT ON CONSTRAINT submodules_name_key DO UPDATE SET 
    submodule_slug = EXCLUDED.submodule_slug,
    description = EXCLUDED.description;

-- Insert default permissions for Super Admin role
WITH role_data AS (
    SELECT role_id FROM roles WHERE role_name = 'Super Admin'
)
INSERT INTO role_permissions (role_id, module_id, submodule_id, permission_type_id)
SELECT DISTINCT
    r.role_id,
    m.module_id,
    sm.submodule_id,
    pt.permission_type_id
FROM role_data r
CROSS JOIN modules m
JOIN submodules sm ON m.module_id = sm.module_id
CROSS JOIN permission_types pt
ON CONFLICT ON CONSTRAINT role_permission_key DO NOTHING;

-- Create effective_permissions view
CREATE OR REPLACE VIEW effective_permissions AS
SELECT 
    rp.role_id,
    m.module_name,
    sm.submodule_name,
    pt.permission_type
FROM role_permissions rp
JOIN modules m ON rp.module_id = m.module_id
JOIN submodules sm ON rp.submodule_id = sm.submodule_id
JOIN permission_types pt ON rp.permission_type_id = pt.permission_type_id;

-- Create update triggers
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_modules_updated_at
BEFORE UPDATE ON modules
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at();

CREATE TRIGGER update_submodules_updated_at
BEFORE UPDATE ON submodules
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at();

CREATE TRIGGER update_permission_types_updated_at
BEFORE UPDATE ON permission_types
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at();

CREATE TRIGGER update_role_permissions_updated_at
BEFORE UPDATE ON role_permissions
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at();
