-- Create access control tables

CREATE TABLE IF NOT EXISTS resource_types (
  id SERIAL PRIMARY KEY,
  tenant_id INTEGER,
  branch_id INTEGER,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS resource_types_tenant_idx ON resource_types(tenant_id);
CREATE INDEX IF NOT EXISTS resource_types_branch_idx ON resource_types(branch_id);

CREATE TABLE IF NOT EXISTS resources (
  id SERIAL PRIMARY KEY,
  resource_type_id INTEGER NOT NULL REFERENCES resource_types(id),
  tenant_id INTEGER,
  branch_id INTEGER,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS resources_type_idx ON resources(resource_type_id);
CREATE INDEX IF NOT EXISTS resources_tenant_idx ON resources(tenant_id);
CREATE INDEX IF NOT EXISTS resources_branch_idx ON resources(branch_id);

CREATE TABLE IF NOT EXISTS access_policies (
  id SERIAL PRIMARY KEY,
  resource_id INTEGER NOT NULL REFERENCES resources(id),
  role_id INTEGER NOT NULL REFERENCES roles(id),
  can_view BOOLEAN DEFAULT FALSE,
  can_edit BOOLEAN DEFAULT FALSE,
  can_delete BOOLEAN DEFAULT FALSE,
  tenant_id INTEGER,
  branch_id INTEGER,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS access_policies_resource_idx ON access_policies(resource_id);
CREATE INDEX IF NOT EXISTS access_policies_role_idx ON access_policies(role_id);
CREATE INDEX IF NOT EXISTS access_policies_tenant_idx ON access_policies(tenant_id);
CREATE INDEX IF NOT EXISTS access_policies_branch_idx ON access_policies(branch_id);

CREATE TABLE IF NOT EXISTS user_admin_roles (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  role_id INTEGER NOT NULL REFERENCES roles(id),
  tenant_id INTEGER,
  branch_id INTEGER,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS user_admin_roles_user_idx ON user_admin_roles(user_id);
CREATE INDEX IF NOT EXISTS user_admin_roles_role_idx ON user_admin_roles(role_id);
CREATE INDEX IF NOT EXISTS user_admin_roles_tenant_idx ON user_admin_roles(tenant_id);
CREATE INDEX IF NOT EXISTS user_admin_roles_branch_idx ON user_admin_roles(branch_id);
