-- Migration: Add webhook_logs table for tracking webhook requests
-- Created: 2026-05-27
-- Description: Tracks all incoming webhook requests with processing status, payload, and response data

-- Create enum type for webhook status
CREATE TYPE webhook_status AS ENUM ('PENDING', 'PROCESSING', 'SUCCESS', 'FAILED');

CREATE TABLE IF NOT EXISTS webhook_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_id VARCHAR(255),
    webhook_type VARCHAR(50) NOT NULL,
    status webhook_status NOT NULL DEFAULT 'PROCESSING',
    payload JSONB,
    response_data JSONB,
    error_message TEXT,
    resident_id UUID REFERENCES residents(id) ON DELETE SET NULL,
    ip_address INET,
    user_agent TEXT,
    retry_count INTEGER DEFAULT 0,
    processed_at TIMESTAMP,
    failed_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Add indexes for common queries
CREATE INDEX IF NOT EXISTS idx_webhook_logs_event_id ON webhook_logs(event_id);
CREATE INDEX IF NOT EXISTS idx_webhook_logs_status ON webhook_logs(status);
CREATE INDEX IF NOT EXISTS idx_webhook_logs_webhook_type ON webhook_logs(webhook_type);
CREATE INDEX IF NOT EXISTS idx_webhook_logs_created_at ON webhook_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_webhook_logs_resident_id ON webhook_logs(resident_id);

-- Add comment on table
COMMENT ON TABLE webhook_logs IS 'Tracks all incoming webhook requests for audit and debugging purposes';

-- Add comments on columns
COMMENT ON COLUMN webhook_logs.event_id IS 'Event ID from partner system for tracking and reconciliation';
COMMENT ON COLUMN webhook_logs.webhook_type IS 'Type of webhook (e.g., RESIDENT_KTX, RESIDENT_TRO)';
COMMENT ON COLUMN webhook_logs.status IS 'Processing status: PENDING, PROCESSING, SUCCESS, FAILED';
COMMENT ON COLUMN webhook_logs.payload IS 'Original webhook payload received from partner';
COMMENT ON COLUMN webhook_logs.response_data IS 'Response data sent back to partner';
COMMENT ON COLUMN webhook_logs.error_message IS 'Error message if processing failed';
COMMENT ON COLUMN webhook_logs.resident_id IS 'ID of the resident created/updated by this webhook';
COMMENT ON COLUMN webhook_logs.ip_address IS 'IP address of the webhook sender';
COMMENT ON COLUMN webhook_logs.user_agent IS 'User agent of the webhook sender';
COMMENT ON COLUMN webhook_logs.retry_count IS 'Number of retry attempts';
COMMENT ON COLUMN webhook_logs.processed_at IS 'Timestamp when processing completed successfully';
COMMENT ON COLUMN webhook_logs.failed_at IS 'Timestamp when processing failed';

-- Add trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_webhook_logs_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_webhook_logs_updated_at
    BEFORE UPDATE ON webhook_logs
    FOR EACH ROW
    EXECUTE FUNCTION update_webhook_logs_updated_at();
