-- ENUM Types
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'banned');
CREATE TYPE permission_type AS ENUM ('menu', 'resource', 'button');
CREATE TYPE target_type AS ENUM ('user', 'role', 'menu', 'resource', 'button');

-- Users Table
CREATE TABLE sys_user
(
    id         SERIAL PRIMARY KEY,
    username   VARCHAR(50) UNIQUE NOT NULL,
    password   VARCHAR(255)       NOT NULL,
    status     user_status DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by INT                NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by INT                NOT NULL
);

-- Add index for frequent queries
CREATE INDEX idx_sys_user_username ON sys_user (username);

-- Users Table's Comment
COMMENT
ON TABLE sys_user IS 'Stores system users and their authentication details';
COMMENT
ON COLUMN sys_user.id IS 'Unique identifier for each user';
COMMENT
ON COLUMN sys_user.username IS 'Unique username for login';
COMMENT
ON COLUMN sys_user.password IS 'Encrypted user password (bcrypt)';
COMMENT
ON COLUMN sys_user.status IS 'Account status: active, inactive, banned';
COMMENT
ON COLUMN sys_user.created_at IS 'Timestamp when the user was created';
COMMENT
ON COLUMN sys_user.created_by IS 'User id of the creator';
COMMENT
ON COLUMN sys_user.updated_at IS 'Timestamp of the last update';
COMMENT
ON COLUMN sys_user.updated_by IS 'User id of the last user who updated the record';

-- Roles Table
CREATE TABLE sys_role
(
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(50) UNIQUE NOT NULL,
    description VARCHAR(500), -- Changed from TEXT for better performance
    created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by  INT                NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by  INT                NOT NULL
);

-- Add index for role name lookups
CREATE INDEX idx_sys_role_name ON sys_role (name);

-- Roles Table's Comment
COMMENT
ON TABLE sys_role IS 'Stores roles in the system';
COMMENT
ON COLUMN sys_role.id IS 'Unique identifier for each role';
COMMENT
ON COLUMN sys_role.name IS 'Role name (e.g., ROLE_ADMIN, ROLE_EDITOR)';
COMMENT
ON COLUMN sys_role.description IS 'Detailed description of the role';
COMMENT
ON COLUMN sys_role.created_at IS 'Timestamp when the role was created';
COMMENT
ON COLUMN sys_role.created_by IS 'User id of the creator';
COMMENT
ON COLUMN sys_role.updated_at IS 'Timestamp of the last update';
COMMENT
ON COLUMN sys_role.updated_by IS 'User id of the last user who updated the record';

-- User-Role Mapping Table (Many-to-Many)
CREATE TABLE sys_user_role
(
    user_id    INT NOT NULL,
    role_id    INT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by INT NOT NULL,
    PRIMARY KEY (user_id, role_id)
);

-- Add indexes for efficient lookups
CREATE INDEX idx_sys_user_role_user ON sys_user_role (user_id);
CREATE INDEX idx_sys_user_role_role ON sys_user_role (role_id);

-- User-Role Mapping Table's Comment
COMMENT
ON TABLE sys_user_role IS 'Links users to roles in a many-to-many relationship';
COMMENT
ON COLUMN sys_user_role.user_id IS 'ID of the user assigned to the role';
COMMENT
ON COLUMN sys_user_role.role_id IS 'ID of the assigned role';
COMMENT
ON COLUMN sys_user_role.created_at IS 'Timestamp when the user assigned to the role';
COMMENT
ON COLUMN sys_user_role.created_by IS 'User id of the creator';

-- Menu Table
CREATE TABLE sys_menu
(
    id         SERIAL PRIMARY KEY,
    parent_id  INT,
    name       VARCHAR(100) NOT NULL,
    url        VARCHAR(255) NOT NULL,
    icon       VARCHAR(100),
    sort_order INT         DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by INT          NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by INT          NOT NULL
);

-- Index for fast menu lookups
CREATE INDEX idx_sys_menu_parent ON sys_menu (parent_id);
CREATE INDEX idx_sys_menu_name ON sys_menu (name);

-- Menu Table's Comment
COMMENT
ON TABLE sys_menu IS 'Stores navigation menu items';
COMMENT
ON COLUMN sys_menu.id IS 'Unique identifier for the menu';
COMMENT
ON COLUMN sys_menu.parent_id IS 'Parent menu ID (NULL for top-level sys_menu)';
COMMENT
ON COLUMN sys_menu.name IS 'Menu name';
COMMENT
ON COLUMN sys_menu.url IS 'Frontend route or API endpoint';
COMMENT
ON COLUMN sys_menu.icon IS 'Icon class for UI display';
COMMENT
ON COLUMN sys_menu.sort_order IS 'Sort order for menu display';
COMMENT
ON COLUMN sys_menu.created_at IS 'Timestamp when the menu was created';
COMMENT
ON COLUMN sys_menu.created_by IS 'User id of the creator';
COMMENT
ON COLUMN sys_menu.updated_at IS 'Timestamp of the last update';
COMMENT
ON COLUMN sys_menu.updated_by IS 'User id of the last user who updated the record';

-- Resources Table
CREATE TABLE sys_resource
(
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) UNIQUE NOT NULL,
    url         VARCHAR(255)        NOT NULL,
    description VARCHAR(500),
    created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by  INT                 NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by  INT                 NOT NULL
);

-- Index for resource name lookups
CREATE INDEX idx_sys_resource_name ON sys_resource (name);

-- Resources Table's Comment
COMMENT
ON TABLE sys_resource IS 'Stores protected resource that require permissions';
COMMENT
ON COLUMN sys_resource.id IS 'Unique identifier for the resource';
COMMENT
ON COLUMN sys_resource.name IS 'Resource name (e.g., "User Management", "Order API")';
COMMENT
ON COLUMN sys_resource.url IS 'API endpoint or internal resource path';
COMMENT
ON COLUMN sys_resource.description IS 'Description of what this resource does';
COMMENT
ON COLUMN sys_resource.created_at IS 'Timestamp when the resource was created';
COMMENT
ON COLUMN sys_resource.created_by IS 'User id of the creator';
COMMENT
ON COLUMN sys_resource.updated_at IS 'Timestamp of the last update';
COMMENT
ON COLUMN sys_resource.updated_by IS 'User id of the last user who updated the record';

-- Buttons Table
CREATE TABLE sys_button
(
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100)        NOT NULL,
    code        VARCHAR(100) UNIQUE NOT NULL,
    description VARCHAR(500),
    created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by  INT                 NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by  INT                 NOT NULL
);

-- Index for button code lookups
CREATE INDEX idx_sys_button_code ON sys_button (code);

-- Buttons Table's Comment
COMMENT
ON TABLE sys_button IS 'Stores buttons that require permissions for user interaction';
COMMENT
ON COLUMN sys_button.id IS 'Unique identifier for the button';
COMMENT
ON COLUMN sys_button.name IS 'Button name (e.g., "Add User", "Delete Order")';
COMMENT
ON COLUMN sys_button.code IS 'Button code for frontend usage (e.g., "btn_add_user")';
COMMENT
ON COLUMN sys_button.description IS 'Description of the button';
COMMENT
ON COLUMN sys_button.created_at IS 'Timestamp when the button was created';
COMMENT
ON COLUMN sys_button.created_by IS 'User id of the creator';
COMMENT
ON COLUMN sys_button.updated_at IS 'Timestamp of the last update';
COMMENT
ON COLUMN sys_button.updated_by IS 'User id of the last user who updated the record';

-- Role-Permission Table
CREATE TABLE sys_role_permission
(
    role_id         INT             NOT NULL,
    permission_type permission_type NOT NULL,
    permission_id   INT             NOT NULL,
    created_at      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by      INT             NOT NULL,
    PRIMARY KEY (role_id, permission_type, permission_id)
);

-- Indexes for role-based permission lookups
CREATE INDEX idx_sys_role_permission_role ON sys_role_permission (role_id);
CREATE INDEX idx_sys_role_permission_perm ON sys_role_permission (permission_id);

-- Role-Permission Table's Comment
COMMENT
ON TABLE sys_role_permission IS 'Links roles to sys_menu, sys_resource, and buttons for access control';
COMMENT
ON COLUMN sys_role_permission.role_id IS 'ID of the role';
COMMENT
ON COLUMN sys_role_permission.permission_type IS 'Permission type: menu, resource, or button';
COMMENT
ON COLUMN sys_role_permission.permission_id IS 'ID of the menu, resource, or button';
COMMENT
ON COLUMN sys_role_permission.created_at IS 'Timestamp when the role assigned to the permission';
COMMENT
ON COLUMN sys_role_permission.created_by IS 'User id of the creator';

-- Audit Log Table
CREATE TABLE sys_audit_log
(
    id          SERIAL PRIMARY KEY,
    action      VARCHAR(255) NOT NULL,
    target_id   INT,
    target_type target_type  NOT NULL,
    value       TEXT,
    created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by  INT          NOT NULL
);

-- Index for searching logs quickly
CREATE INDEX idx_sys_audit_log_target ON sys_audit_log (target_id);
CREATE INDEX idx_sys_audit_log_created ON sys_audit_log (created_at);

-- Audit Log Table's Comment
COMMENT
ON TABLE sys_audit_log IS 'Stores system actions and security audit logs';
COMMENT
ON COLUMN sys_audit_log.id IS 'Unique identifier for each audit log entry';
COMMENT
ON COLUMN sys_audit_log.action IS 'Action performed (e.g., "User created", "Permission granted")';
COMMENT
ON COLUMN sys_audit_log.target_id IS 'ID of the target entity affected';
COMMENT
ON COLUMN sys_audit_log.target_type IS 'Type of entity affected';
COMMENT
ON COLUMN sys_audit_log.value IS 'Content of entity (json format)';
COMMENT
ON COLUMN sys_audit_log.created_at IS 'Timestamp when the log was created';
COMMENT
ON COLUMN sys_audit_log.created_by IS 'User id of the creator';

-- Default Roles Table
CREATE TABLE sys_default_role
(
    id         SERIAL PRIMARY KEY,
    role_id    INT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by INT NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by INT NOT NULL
);

-- Index for role default assignments
CREATE INDEX idx_sys_default_role ON sys_default_role (role_id);

-- Default Roles Table's Comment
COMMENT
ON TABLE sys_default_role IS 'Defines default roles assigned to new users';
COMMENT
ON COLUMN sys_default_role.id IS 'Unique identifier';
COMMENT
ON COLUMN sys_default_role.role_id IS 'ID of the default role';
COMMENT
ON COLUMN sys_default_role.created_at IS 'Timestamp when the default role was created';
COMMENT
ON COLUMN sys_default_role.created_by IS 'User id of the creator';
COMMENT
ON COLUMN sys_default_role.updated_at IS 'Timestamp of the last update';
COMMENT
ON COLUMN sys_default_role.updated_by IS 'User id of the last user who updated the record';
Please optimize this and don't use foreign key, I will do check Check constraints.