-- Migration: Create sync_hooks table
-- Date: 2025-11-11
-- Description: Creates sync_hooks table for declarative sync operations

BEGIN;

-- Table: sync_hooks
-- Declarative hooks for sync operations (auto-aggregation, cascades, validations)
CREATE TABLE IF NOT EXISTS sync_hooks (
  hook_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  name varchar(100) NOT NULL UNIQUE,
  description text,
  trigger_table varchar(100) NOT NULL,
  trigger_operations text[] NOT NULL,
  hook_type varchar(50) NOT NULL,
  priority INTEGER NOT NULL DEFAULT 100,
  config jsonb NOT NULL DEFAULT '{}'::jsonb,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_sync_hooks_trigger ON sync_hooks(trigger_table, is_active);
CREATE INDEX IF NOT EXISTS idx_sync_hooks_active ON sync_hooks(is_active, priority);

-- Table: sync_hook_logs
-- Audit log for sync hook executions
CREATE TABLE IF NOT EXISTS sync_hook_logs (
  log_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  hook_id uuid NOT NULL REFERENCES sync_hooks(hook_id) ON DELETE CASCADE,
  trigger_table varchar(100) NOT NULL,
  trigger_operation varchar(50) NOT NULL,
  execution_status varchar(50) NOT NULL,
  execution_result jsonb,
  execution_time_ms numeric(10,2),
  executed_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_sync_hook_logs_hook ON sync_hook_logs(hook_id, executed_at);
CREATE INDEX IF NOT EXISTS idx_sync_hook_logs_status ON sync_hook_logs(execution_status, executed_at);

-- Add update trigger for sync_hooks
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_sync_hooks_updated_at ON sync_hooks;
CREATE TRIGGER trg_sync_hooks_updated_at
    BEFORE UPDATE ON sync_hooks
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();

COMMIT;