create extension if not exists "pgcrypto";

create table if not exists users (
  id uuid primary key default gen_random_uuid(),
  username text unique not null,
  password_hash text not null,
  is_active boolean not null default true,
  is_locked boolean not null default false,
  created_at timestamptz not null default now()
);

create table if not exists roles (
  id uuid primary key default gen_random_uuid(),
  slug text unique not null,
  name text not null,
  description text not null default '',
  created_at timestamptz not null default now()
);

create table if not exists permissions (
  id uuid primary key default gen_random_uuid(),
  resource text not null,
  action text not null,
  unique (resource, action)
);

create table if not exists role_permissions (
  role_id uuid not null references roles(id) on delete cascade,
  permission_id uuid not null references permissions(id) on delete cascade,
  scope_type text not null default 'global',
  scope_value text null
);

create unique index if not exists role_permissions_scope_uidx
  on role_permissions (role_id, permission_id, scope_type, coalesce(scope_value, ''));

create table if not exists user_roles (
  user_id uuid not null references users(id) on delete cascade,
  role_id uuid not null references roles(id) on delete cascade,
  primary key (user_id, role_id)
);

create table if not exists resource_groups (
  id uuid primary key default gen_random_uuid(),
  slug text unique not null,
  name text not null
);

create table if not exists resource_group_tables (
  group_id uuid not null references resource_groups(id) on delete cascade,
  table_name text not null,
  primary key (group_id, table_name)
);

create table if not exists sessions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  token_hash text not null unique,
  expires_at timestamptz not null,
  ip text null,
  user_agent text null,
  created_at timestamptz not null default now()
);

create table if not exists audit_events (
  id uuid primary key default gen_random_uuid(),
  created_at timestamptz not null default now(),
  actor_user_id uuid null references users(id) on delete set null,
  action text not null,
  resource_type text not null,
  resource_name text null,
  group_id text null,
  target_connection_id text null,
  sql_text_masked text null,
  payload_before jsonb null,
  payload_after jsonb null,
  ip text null,
  user_agent text null,
  status text not null
);

create table if not exists db_connections (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  host text not null,
  port integer not null,
  database_name text not null,
  is_default boolean not null default false,
  created_at timestamptz not null default now()
);

insert into permissions (resource, action) values
  ('database', 'read'),
  ('database', 'write'),
  ('database', 'delete'),
  ('database', 'schema_change'),
  ('group', 'read'),
  ('group', 'write'),
  ('group', 'delete'),
  ('group', 'schema_change'),
  ('table', 'read'),
  ('table', 'write'),
  ('table', 'delete'),
  ('table', 'schema_change'),
  ('sql_console', 'execute_sql'),
  ('logs', 'view_logs'),
  ('users', 'manage_users'),
  ('roles', 'manage_roles'),
  ('audit', 'read')
on conflict do nothing;

insert into roles (slug, name, description) values
  ('root', 'Root', 'Full access to the control plane'),
  ('folder_admin', 'Folder Admin', 'Admin for selected groups'),
  ('user', 'User', 'Read-only or restricted access')
on conflict do nothing;

insert into resource_groups (slug, name) values
  ('finance', 'Finance'),
  ('users', 'Users'),
  ('logs', 'Logs')
on conflict do nothing;

insert into resource_group_tables (group_id, table_name)
select g.id, x.table_name
from (
  values
    ('finance', 'finance__invoices'),
    ('users', 'users__accounts'),
    ('logs', 'logs__events')
) as x(group_slug, table_name)
join resource_groups g on g.slug = x.group_slug
on conflict do nothing;

insert into db_connections (name, host, port, database_name, is_default)
values ('Default Target DB', 'postgres-target', 5432, 'appdb', true)
on conflict do nothing;