-- Initial compatibility schema for the local PostgreSQL backend.
-- The source_record columns retain source fields that are not normalized yet,
-- so a Supabase export can be imported without discarding information.

CREATE SCHEMA IF NOT EXISTS app_meta;
CREATE SCHEMA IF NOT EXISTS app_auth;
CREATE SCHEMA IF NOT EXISTS local_storage;

CREATE TABLE IF NOT EXISTS app_meta.schema_migrations (
  version text PRIMARY KEY,
  name text NOT NULL UNIQUE,
  checksum text NOT NULL CHECK (checksum ~ '^[0-9a-f]{64}$'),
  applied_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  execution_time_ms integer NOT NULL DEFAULT 0 CHECK (execution_time_ms >= 0)
);

-- Keep this table shape identical to the Supabase KV table used by the ERP.
CREATE TABLE IF NOT EXISTS public.kv_store_7249dcd9 (
  key text PRIMARY KEY,
  value jsonb NOT NULL
);

CREATE TABLE IF NOT EXISTS app_auth.users (
  instance_id uuid,
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  aud text,
  role text,
  email text,
  encrypted_password text,
  email_confirmed_at timestamptz,
  invited_at timestamptz,
  confirmation_token text,
  confirmation_sent_at timestamptz,
  recovery_token text,
  recovery_sent_at timestamptz,
  email_change_token_new text,
  email_change text,
  email_change_sent_at timestamptz,
  last_sign_in_at timestamptz,
  raw_app_meta_data jsonb DEFAULT '{}'::jsonb,
  raw_user_meta_data jsonb DEFAULT '{}'::jsonb,
  is_super_admin boolean,
  created_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  updated_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  phone text,
  phone_confirmed_at timestamptz,
  phone_change text,
  phone_change_token text,
  phone_change_sent_at timestamptz,
  confirmed_at timestamptz,
  email_change_token_current text,
  email_change_confirm_status smallint NOT NULL DEFAULT 0,
  banned_until timestamptz,
  reauthentication_token text,
  reauthentication_sent_at timestamptz,
  is_sso_user boolean NOT NULL DEFAULT false,
  deleted_at timestamptz,
  is_anonymous boolean NOT NULL DEFAULT false,
  source_record jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS app_auth.identities (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES app_auth.users(id) ON DELETE CASCADE,
  provider_id text NOT NULL,
  identity_data jsonb NOT NULL DEFAULT '{}'::jsonb,
  provider text NOT NULL,
  email text,
  last_sign_in_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  updated_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  source_record jsonb NOT NULL DEFAULT '{}'::jsonb,
  CONSTRAINT identities_provider_provider_id_key UNIQUE (provider, provider_id)
);

CREATE TABLE IF NOT EXISTS app_auth.sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES app_auth.users(id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  updated_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  factor_id uuid,
  aal text,
  not_after timestamptz,
  refreshed_at timestamp without time zone,
  user_agent text,
  ip inet,
  tag text,
  revoked_at timestamptz,
  source_record jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS app_auth.refresh_tokens (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  instance_id uuid,
  user_id uuid NOT NULL REFERENCES app_auth.users(id) ON DELETE CASCADE,
  session_id uuid REFERENCES app_auth.sessions(id) ON DELETE CASCADE,
  token text,
  token_hash text,
  parent text,
  revoked boolean NOT NULL DEFAULT false,
  revoked_at timestamptz,
  expires_at timestamptz,
  used_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  updated_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  source_record jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS local_storage.buckets (
  id text PRIMARY KEY,
  name text NOT NULL UNIQUE,
  owner uuid REFERENCES app_auth.users(id) ON DELETE SET NULL,
  owner_id text,
  public boolean NOT NULL DEFAULT false,
  file_size_limit bigint CHECK (file_size_limit IS NULL OR file_size_limit >= 0),
  allowed_mime_types text[],
  avif_autodetection boolean NOT NULL DEFAULT false,
  type text NOT NULL DEFAULT 'STANDARD',
  created_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  updated_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  source_record jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS local_storage.objects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  bucket_id text NOT NULL REFERENCES local_storage.buckets(id) ON DELETE CASCADE,
  name text NOT NULL,
  owner uuid REFERENCES app_auth.users(id) ON DELETE SET NULL,
  owner_id text,
  created_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  updated_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  last_accessed_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
  metadata jsonb,
  user_metadata jsonb,
  path_tokens text[],
  version text,
  local_path text,
  size_bytes bigint CHECK (size_bytes IS NULL OR size_bytes >= 0),
  content_type text,
  etag text,
  content_checksum text,
  source_record jsonb NOT NULL DEFAULT '{}'::jsonb,
  CONSTRAINT objects_bucket_id_name_key UNIQUE (bucket_id, name)
);

CREATE OR REPLACE FUNCTION app_meta.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
SET search_path = pg_catalog
AS $$
BEGIN
  -- Preserve an explicitly imported source timestamp. Normal updates that do
  -- not set updated_at receive the current transaction timestamp.
  IF NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at THEN
    NEW.updated_at := transaction_timestamp();
  END IF;

  RETURN NEW;
END;
$$;

DO $$
DECLARE
  target record;
BEGIN
  FOR target IN
    SELECT *
    FROM (VALUES
      ('app_auth', 'users'),
      ('app_auth', 'identities'),
      ('app_auth', 'sessions'),
      ('app_auth', 'refresh_tokens'),
      ('local_storage', 'buckets'),
      ('local_storage', 'objects')
    ) AS targets(schema_name, table_name)
  LOOP
    IF NOT EXISTS (
      SELECT 1
      FROM pg_catalog.pg_trigger
      WHERE tgrelid = format('%I.%I', target.schema_name, target.table_name)::regclass
        AND tgname = 'set_updated_at'
        AND NOT tgisinternal
    ) THEN
      EXECUTE format(
        'CREATE TRIGGER set_updated_at BEFORE UPDATE ON %I.%I '
        'FOR EACH ROW EXECUTE FUNCTION app_meta.set_updated_at()',
        target.schema_name,
        target.table_name
      );
    END IF;
  END LOOP;
END;
$$;

CREATE INDEX IF NOT EXISTS users_instance_id_idx
  ON app_auth.users (instance_id);
CREATE INDEX IF NOT EXISTS users_email_lookup_idx
  ON app_auth.users (lower(email))
  WHERE email IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS users_phone_lookup_idx
  ON app_auth.users (phone)
  WHERE phone IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS users_created_at_idx
  ON app_auth.users (created_at);

CREATE INDEX IF NOT EXISTS identities_user_id_idx
  ON app_auth.identities (user_id);
CREATE INDEX IF NOT EXISTS identities_email_lookup_idx
  ON app_auth.identities (lower(email))
  WHERE email IS NOT NULL;

CREATE INDEX IF NOT EXISTS sessions_user_id_idx
  ON app_auth.sessions (user_id);
CREATE INDEX IF NOT EXISTS sessions_active_user_idx
  ON app_auth.sessions (user_id, not_after)
  WHERE revoked_at IS NULL;
CREATE INDEX IF NOT EXISTS sessions_not_after_idx
  ON app_auth.sessions (not_after)
  WHERE not_after IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS refresh_tokens_token_key
  ON app_auth.refresh_tokens (token)
  WHERE token IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS refresh_tokens_token_hash_key
  ON app_auth.refresh_tokens (token_hash)
  WHERE token_hash IS NOT NULL;
CREATE INDEX IF NOT EXISTS refresh_tokens_user_id_idx
  ON app_auth.refresh_tokens (user_id);
CREATE INDEX IF NOT EXISTS refresh_tokens_session_id_idx
  ON app_auth.refresh_tokens (session_id)
  WHERE session_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS refresh_tokens_active_idx
  ON app_auth.refresh_tokens (user_id, expires_at)
  WHERE revoked = false;

CREATE INDEX IF NOT EXISTS buckets_owner_idx
  ON local_storage.buckets (owner)
  WHERE owner IS NOT NULL;
CREATE INDEX IF NOT EXISTS objects_owner_idx
  ON local_storage.objects (owner)
  WHERE owner IS NOT NULL;
CREATE INDEX IF NOT EXISTS objects_bucket_created_at_idx
  ON local_storage.objects (bucket_id, created_at);
CREATE INDEX IF NOT EXISTS objects_bucket_name_pattern_idx
  ON local_storage.objects (bucket_id, name text_pattern_ops);

COMMENT ON TABLE public.kv_store_7249dcd9 IS
  'Compatibility KV store copied from Supabase without key or JSON reshaping.';
COMMENT ON COLUMN app_auth.users.encrypted_password IS
  'Password hash imported verbatim from Supabase Auth; never contains plaintext.';
COMMENT ON COLUMN app_auth.refresh_tokens.token IS
  'Legacy Supabase refresh token retained for migration compatibility.';
COMMENT ON COLUMN app_auth.refresh_tokens.token_hash IS
  'Hash used for refresh tokens issued by the local backend.';
COMMENT ON COLUMN local_storage.objects.local_path IS
  'Path relative to the configured local storage root; never a client supplied absolute path.';