-- Users table
CREATE INDEX idx_users_id ON users(id);
CREATE INDEX idx_users_uuid ON users(uuid);
CREATE INDEX idx_users_company_status ON users(company_id, status_id);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_process_status ON users(process_status);
CREATE INDEX idx_users_is_drafted ON users(is_drafted);

-- User Details table
CREATE INDEX idx_user_details_user_id ON user_details(user_id);
CREATE INDEX idx_user_details_code ON user_details(code);

-- User Roles table
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON user_roles(role_id);

-- Roles table
CREATE INDEX idx_roles_id ON roles(id);
CREATE INDEX idx_roles_name ON roles(name);



-- View: public.users_export
-- This view combines data from `users`, `user_details`, `user_roles`, and `roles` tables based on the query you provided.

CREATE OR REPLACE VIEW public.users_export AS
SELECT 
    users.id AS id,
    users.uuid AS uuid,
    user_details.code AS code,
    concat(user_details.first_name, ' ', user_details.last_name) AS name,
    users.email AS email,
    STRING_AGG(roles.name, ', ') AS user_roles,
    users.status_id AS status,
    CASE 
        WHEN users.password IS NULL OR users.password = '' THEN false  
        ELSE true 
    END AS password,
    users.reason AS reason,
    users.process_status AS process_status,
    users.is_drafted AS is_drafted,
    users.uuid AS gparam_1
FROM users
INNER JOIN user_details ON user_details.user_id = users.id
LEFT JOIN user_roles ON users.id = user_roles.user_id
LEFT JOIN roles ON roles.id = user_roles.role_id
WHERE users.company_id = '1' 
    AND users.status_id != '3' 
    AND users.role NOT IN ('super_admin', 'company_admin') 
    AND users.status_id != '3' 
    AND users.role NOT IN ('super_admin', 'company_admin')
GROUP BY users.id, users.uuid, user_details.code, user_details.first_name, user_details.last_name, users.email, users.status_id
ORDER BY users.id ASC 
LIMIT 10 OFFSET 0;
