Phase 5 Database Migrations

-- Add access_token_hash column
ALTER TABLE hr_references 
  ADD COLUMN access_token_hash TEXT;

-- Create index for fast hash lookups during token validation
CREATE INDEX idx_hr_references_access_token_hash 
  ON hr_references(access_token_hash) 
  WHERE access_token_hash IS NOT NULL;

-- Document deprecation of plaintext token
COMMENT ON COLUMN hr_references.access_token IS 
  'DEPRECATED (v2026-02-08): Use access_token_hash for security. Plaintext tokens will be removed after migration period.';

-- Ensure audit columns exist
ALTER TABLE hr_references 
  ADD COLUMN IF NOT EXISTS created_by UUID REFERENCES pf_profiles(id),
  ADD COLUMN IF NOT EXISTS updated_by UUID REFERENCES pf_profiles(id);

-- Create trigger for updated_at maintenance
CREATE OR REPLACE FUNCTION public.update_hr_references_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_hr_references_updated_at ON hr_references;
CREATE TRIGGER update_hr_references_updated_at
  BEFORE UPDATE ON hr_references
  FOR EACH ROW
  EXECUTE FUNCTION public.update_hr_references_updated_at();

// On token CREATION:
// 1. Generate random 16-byte salt
// 2. Hash token: SHA-256(token + salt)
// 3. Store: Base64(salt + hash)

const crypto = await import('https://deno.land/std/crypto/mod.ts');

function hashToken(token: string, salt: Uint8Array): string {
  const encoder = new TextEncoder();
  const tokenBytes = encoder.encode(token);
  const combined = new Uint8Array([...salt, ...tokenBytes]);
  const hash = crypto.subtle.digestSync('SHA-256', combined);
  const combined64 = btoa(String.fromCharCode(...new Uint8Array(combined)));
  return combined64; // Base64(salt + hash)
}

// On token VALIDATION:
// 1. Receive plaintext token from referee
// 2. Decode stored Base64 to get salt
// 3. Hash presented token with same salt
// 4. Compare hashes

function validateToken(presentedToken: string, storedHash: string): boolean {
  const decoded = atob(storedHash);
  const salt = new Uint8Array(16);
  for (let i = 0; i < 16; i++) {
    salt[i] = decoded.charCodeAt(i);
  }
  
  const presented = hashToken(presentedToken, salt);
  return presented === storedHash;
}

-- Add organization_id column (initially nullable for existing rows)
ALTER TABLE hr_candidate_portal_sessions
  ADD COLUMN organization_id UUID;

-- Create foreign key constraint
ALTER TABLE hr_candidate_portal_sessions
  ADD CONSTRAINT fk_hr_candidate_portal_sessions_organization_id
  FOREIGN KEY (organization_id) REFERENCES pf_organizations(id) ON DELETE CASCADE;

-- Create index for fast org-scoped lookups
CREATE INDEX idx_hr_candidate_portal_sessions_organization_id 
  ON hr_candidate_portal_sessions(organization_id);

-- Backfill organization_id from candidate relationship
-- (assumes hr_candidates table has organization_id)
UPDATE hr_candidate_portal_sessions s
SET organization_id = c.organization_id
FROM hr_candidates c
WHERE s.candidate_id = c.id
  AND s.organization_id IS NULL;

-- Update RLS policies for organization isolation
DROP POLICY IF EXISTS "Sessions org isolation" ON hr_candidate_portal_sessions;
CREATE POLICY "Sessions org isolation" 
  ON hr_candidate_portal_sessions
  USING (
    organization_id = (
      current_setting('request.jwt.claims', true)::json->>'organization_id'
    )::uuid
  );

-- Make organization_id NOT NULL after backfill succeeds
ALTER TABLE hr_candidate_portal_sessions
  ALTER COLUMN organization_id SET NOT NULL;

-- Create trigger for updated_at maintenance
CREATE OR REPLACE FUNCTION public.update_hr_candidate_portal_sessions_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_hr_candidate_portal_sessions_updated_at ON hr_candidate_portal_sessions;
CREATE TRIGGER update_hr_candidate_portal_sessions_updated_at
  BEFORE UPDATE ON hr_candidate_portal_sessions
  FOR EACH ROW
  EXECUTE FUNCTION public.update_hr_candidate_portal_sessions_updated_at();

// BEFORE: No organization_id required
const createSession = async (candidateId: string) => {
  return supabase
    .from('hr_candidate_portal_sessions')
    .insert({ candidate_id: candidateId });
};

// AFTER: Include organization_id
const createSession = async (candidateId: string, organizationId: string) => {
  return supabase
    .from('hr_candidate_portal_sessions')
    .insert({ 
      candidate_id: candidateId,
      organization_id: organizationId  // NEW
    });
};

-- Add credential_id column (initially nullable)
ALTER TABLE hr_job_board_integrations
  ADD COLUMN credential_id UUID;

-- Create foreign key constraint
ALTER TABLE hr_job_board_integrations
  ADD CONSTRAINT fk_hr_job_board_integrations_credential_id
  FOREIGN KEY (credential_id) REFERENCES pf_credentials(id) ON DELETE SET NULL;

-- Create index for credential lookups
CREATE INDEX idx_hr_job_board_integrations_credential_id 
  ON hr_job_board_integrations(credential_id);

-- Document deprecation of plaintext columns
COMMENT ON COLUMN hr_job_board_integrations.api_key IS 
  'DEPRECATED (v2026-02-08): Use credential_id with pf_credentials table. Plaintext key will be removed after migration period.';
COMMENT ON COLUMN hr_job_board_integrations.api_secret IS 
  'DEPRECATED (v2026-02-08): Use credential_id with pf_credentials table. Plaintext secret will be removed after migration period.';
COMMENT ON COLUMN hr_job_board_integrations.refresh_token IS 
  'DEPRECATED (v2026-02-08): Use credential_id with pf_credentials table. Plaintext token will be removed after migration period.';

-- Create migration log table for audit trail
CREATE TABLE IF NOT EXISTS pf_credential_migrations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name TEXT NOT NULL,
  record_id UUID NOT NULL,
  credential_type TEXT NOT NULL,
  migrated_at TIMESTAMPTZ DEFAULT now(),
  migrated_by UUID REFERENCES pf_profiles(id),
  notes TEXT
);

ALTER TABLE pf_credential_migrations ENABLE ROW LEVEL SECURITY;

// Script: migrate-job-board-credentials.ts
// Run after migration to move plaintext credentials to pf_credentials

import { createClient } from '@supabase/supabase-js';

const supabaseAdmin = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_SERVICE_ROLE_KEY
);

async function migrateJobBoardIntegrations() {
  // Fetch all integrations with plaintext credentials
  const { data: integrations } = await supabaseAdmin
    .from('hr_job_board_integrations')
    .select('*')
    .is('credential_id', null)
    .not('api_key', 'is', null);

  for (const integration of integrations || []) {
    // Create credential record
    const { data: credential } = await supabaseAdmin
      .from('pf_credentials')
      .insert({
        organization_id: integration.organization_id,
        credential_type: `job_board_${integration.job_board_type}`,
        name: `${integration.job_board_type} - Migrated`,
        encrypted_value: JSON.stringify({
          api_key: integration.api_key,
          api_secret: integration.api_secret,
          refresh_token: integration.refresh_token,
        }),
        created_by: integration.created_by,
      })
      .select()
      .single();

    // Link integration to credential
    await supabaseAdmin
      .from('hr_job_board_integrations')
      .update({ credential_id: credential.id })
      .eq('id', integration.id);

    // Log migration
    await supabaseAdmin
      .from('pf_credential_migrations')
      .insert({
        table_name: 'hr_job_board_integrations',
        record_id: integration.id,
        credential_type: `job_board_${integration.job_board_type}`,
        migrated_by: 'system',
        notes: `Migrated plaintext credentials to pf_credentials`,
      });
  }
}

// BEFORE: Read plaintext from table
const fetchIntegrations = async (orgId: string) => {
  return supabase
    .from('hr_job_board_integrations')
    .select('id, job_board_type, api_key, api_secret')
    .eq('organization_id', orgId);
};

// AFTER: Fetch via credential relationship
const fetchIntegrations = async (orgId: string) => {
  return supabase
    .from('hr_job_board_integrations')
    .select(`
      id,
      job_board_type,
      credential_id,
      credential:pf_credentials!credential_id(id, encrypted_value)
    `)
    .eq('organization_id', orgId);
};

// Decrypt credentials when needed (server-side edge function)
async function decryptCredential(credentialId: string) {
  const { data } = await supabase
    .from('pf_credentials')
    .select('encrypted_value')
    .eq('id', credentialId)
    .single();
  
  return decryptAES256GCM(data.encrypted_value);
}

npx supabase gen types typescript --local > src/integrations/supabase/types.ts

// hr_references.ts
export interface HRReference {
  id: string;
  access_token?: string; // DEPRECATED
  access_token_hash?: string; // NEW
  // ... other fields
}

// hr_candidate_portal_sessions.ts
export interface HRCandidatePortalSession {
  id: string;
  organization_id: string; // NEW
  candidate_id: string;
  // ... other fields
}

// hr_job_board_integrations.ts
export interface HRJobBoardIntegration {
  id: string;
  credential_id?: string; // NEW
  api_key?: string; // DEPRECATED
  api_secret?: string; // DEPRECATED
  refresh_token?: string; // DEPRECATED
  // ... other fields
}