-- Vault secret manager tables

CREATE TABLE vault_config (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  master_key_salt TEXT NOT NULL,
  master_key_hash TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE vault_category (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_by UUID REFERENCES "user"(id),
  is_public BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE vault_category_permission (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category_id UUID NOT NULL REFERENCES vault_category(id) ON DELETE CASCADE,
  role_id UUID REFERENCES role(id),
  user_id UUID REFERENCES "user"(id),
  access_level storage_folder_access NOT NULL DEFAULT 'view',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE vault_entry (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category_id UUID NOT NULL REFERENCES vault_category(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  username VARCHAR(255),
  url VARCHAR(1000),
  notes TEXT,
  encrypted_value TEXT,
  iv TEXT,
  created_by UUID REFERENCES "user"(id),
  updated_by UUID REFERENCES "user"(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_vault_category_permission_category ON vault_category_permission(category_id);
CREATE INDEX idx_vault_entry_category ON vault_entry(category_id);
