-- Add permissions engine tables

CREATE TABLE IF NOT EXISTS resource_types (
  id SERIAL PRIMARY KEY,
  code VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS resources (
  id SERIAL PRIMARY KEY,
  resource_type_id INTEGER NOT NULL REFERENCES resource_types(id) ON DELETE CASCADE,
  resource_id INTEGER NOT NULL,
  name VARCHAR(255) DEFAULT NULL,
  reference_table VARCHAR(100) DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(resource_type_id, resource_id)
);
CREATE INDEX IF NOT EXISTS resources_type_idx ON resources(resource_type_id);

CREATE TABLE IF NOT EXISTS permissions (
  id SERIAL PRIMARY KEY,
  resource_id INTEGER NOT NULL REFERENCES resources(id) ON DELETE CASCADE,
  subject_type VARCHAR(50) NOT NULL,
  subject_id INTEGER NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  department_id INTEGER DEFAULT NULL,
  district_id INTEGER DEFAULT NULL,
  can_view BOOLEAN DEFAULT FALSE,
  can_edit BOOLEAN DEFAULT FALSE,
  can_create BOOLEAN DEFAULT FALSE,
  can_delete BOOLEAN DEFAULT FALSE,
  can_delegate BOOLEAN DEFAULT FALSE,
  effect VARCHAR(10) DEFAULT 'allow',
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS permissions_resource_idx ON permissions(resource_id);
CREATE INDEX IF NOT EXISTS permissions_tenant_idx ON permissions(tenant_id);
CREATE INDEX IF NOT EXISTS permissions_branch_idx ON permissions(branch_id);
CREATE INDEX IF NOT EXISTS permissions_department_idx ON permissions(department_id);
CREATE INDEX IF NOT EXISTS permissions_district_idx ON permissions(district_id);

CREATE TABLE IF NOT EXISTS admin_scopes (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  department_id INTEGER DEFAULT NULL,
  district_id INTEGER DEFAULT NULL,
  can_delegate BOOLEAN DEFAULT FALSE,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS admin_scopes_user_idx ON admin_scopes(user_id);
