CREATE SCHEMA IF NOT EXISTS instawebhook;

CREATE TABLE IF NOT EXISTS instawebhook.webhook_event_payloads (
  id uuid PRIMARY KEY NOT NULL,
  event_id uuid UNIQUE NOT NULL,
  event_uid varchar(80) NOT NULL,
  organization_id uuid NOT NULL,
  project_id uuid NOT NULL,
  endpoint_id uuid NOT NULL,
  payload_sha256 varchar(96) NOT NULL,
  content_type varchar(180),
  encrypted_payload text NOT NULL,
  encryption_key_id varchar(80) NOT NULL,
  received_at timestamp with time zone NOT NULL,
  created_at timestamp with time zone DEFAULT now() NOT NULL,
  deleted_at timestamp with time zone
);

CREATE INDEX IF NOT EXISTS webhook_event_payloads_event_uid_idx
ON instawebhook.webhook_event_payloads (event_uid);

CREATE INDEX IF NOT EXISTS webhook_event_payloads_received_at_idx
ON instawebhook.webhook_event_payloads (received_at);

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'instawebhook_worker') THEN
    CREATE USER instawebhook_worker WITH PASSWORD 'replace-with-strong-password' NOSUPERUSER NOCREATEDB NOCREATEROLE;
  END IF;
END $$;

GRANT USAGE ON SCHEMA instawebhook TO instawebhook_worker;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA instawebhook TO instawebhook_worker;
ALTER DEFAULT PRIVILEGES IN SCHEMA instawebhook
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO instawebhook_worker;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'instawebhook_audit') THEN
    CREATE USER instawebhook_audit WITH PASSWORD 'replace-with-strong-password' NOSUPERUSER NOCREATEDB NOCREATEROLE;
  END IF;
END $$;

GRANT USAGE ON SCHEMA instawebhook TO instawebhook_audit;
GRANT SELECT ON ALL TABLES IN SCHEMA instawebhook TO instawebhook_audit;