-- Migration: Sync Hooks System
-- Date: 2025-10-08
-- Description: Declarative hooks for data aggregation, derived calculations, and cross-table updates
--              This system allows auto-aggregation, inventory updates, financial calculations, etc.
--              to be configured declaratively in the database instead of hard-coded logic.

-- Table to store hook definitions
CREATE TABLE IF NOT EXISTS sync_hooks (
    hook_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    trigger_table VARCHAR(100) NOT NULL,
    trigger_operations TEXT[] DEFAULT ARRAY['INSERT', 'UPDATE', 'DELETE'],
    hook_type VARCHAR(50) NOT NULL CHECK (hook_type IN ('aggregate', 'derive', 'cascade', 'validate', 'custom')),
    config JSONB NOT NULL DEFAULT '{}',
    priority INT DEFAULT 100,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT valid_trigger_ops CHECK (trigger_operations <@ ARRAY['INSERT', 'UPDATE', 'DELETE', 'PULL'])
);

COMMENT ON TABLE sync_hooks IS 'Declarative hooks for sync operations - enables auto-aggregation, derivations, cascades without code changes';
COMMENT ON COLUMN sync_hooks.name IS 'Unique identifier for the hook';
COMMENT ON COLUMN sync_hooks.trigger_table IS 'Table that triggers this hook (e.g., time_clocks, inventory_txn)';
COMMENT ON COLUMN sync_hooks.trigger_operations IS 'Which operations trigger the hook: INSERT, UPDATE, DELETE, PULL';
COMMENT ON COLUMN sync_hooks.hook_type IS 'Type: aggregate (sum/count), derive (calculated fields), cascade (update related), validate, custom';
COMMENT ON COLUMN sync_hooks.config IS 'JSON config specific to hook type - see documentation below';
COMMENT ON COLUMN sync_hooks.priority IS 'Execution order (lower = earlier), default 100. Lower numbers run first.';

-- Index for fast lookup during sync operations
CREATE INDEX idx_sync_hooks_trigger ON sync_hooks(trigger_table, is_active) WHERE is_active = true;
CREATE INDEX idx_sync_hooks_priority ON sync_hooks(priority, hook_id) WHERE is_active = true;

-- Log table for hook executions (debugging and audit)
CREATE TABLE IF NOT EXISTS sync_hook_logs (
    log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    hook_id UUID REFERENCES sync_hooks(hook_id) ON DELETE CASCADE,
    hook_name VARCHAR(100),
    trigger_table VARCHAR(100) NOT NULL,
    trigger_operation VARCHAR(20) NOT NULL,
    affected_records INT DEFAULT 0,
    execution_time_ms INT,
    status VARCHAR(20) DEFAULT 'success' CHECK (status IN ('success', 'error', 'skipped')),
    error_message TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sync_hook_logs_hook ON sync_hook_logs(hook_id, created_at DESC);
CREATE INDEX idx_sync_hook_logs_status ON sync_hook_logs(status, created_at DESC) WHERE status = 'error';
CREATE INDEX idx_sync_hook_logs_created ON sync_hook_logs(created_at DESC);

COMMENT ON TABLE sync_hook_logs IS 'Audit log of all hook executions for debugging and performance monitoring';

/*
 * HOOK TYPE DOCUMENTATION
 * 
 * 1. AGGREGATE HOOKS
 *    Purpose: Auto-generate aggregated data (sums, counts, averages, etc.)
 *    Use Cases: Time clock aggregates, inventory summaries, financial rollups
 *    
 *    Config Schema:
 *    {
 *        "source_table": "time_clocks",           // Optional: Override source (default: trigger_table)
 *        "target_table": "time_clock_aggregates", // Required: Where to store results
 *        "group_by": ["user_id"],                 // Required: Group by these columns
 *        "date_range_source": "filter",           // Required: "filter" or "data"
 *        "date_field": "clocked_at",              // Optional: Date field to filter on
 *        "aggregations": {                        // Required: Field name -> SQL expression
 *            "total_net_hours": "SUM(hours)",
 *            "total_days_worked": "COUNT(DISTINCT work_date)"
 *        },
 *        "conditions": {                          // Optional: WHERE clause conditions
 *            "clock_out IS NOT NULL": true,
 *            "isdeleted": false
 *        },
 *        "upsert_keys": ["user_id", "start_date", "end_date"],  // Required: Unique constraint keys
 *        "additional_fields": {                   // Optional: Copy calculated fields
 *            "total_payable_hours": "total_net_hours"
 *        }
 *    }
 * 
 * 2. CASCADE HOOKS
 *    Purpose: Update related tables when data changes
 *    Use Cases: inventory_txn -> inventory, order_items -> orders.total
 *    
 *    Config Schema:
 *    {
 *        "target_table": "inventory",
 *        "operation": "UPDATE",                   // UPDATE, INSERT, DELETE
 *        "match_on": {                            // How to find target record
 *            "product_id": "product_id",
 *            "location_id": "location_id"
 *        },
 *        "update_fields": {                       // SQL expressions for updates
 *            "quantity_on_hand": "quantity_on_hand + NEW.quantity_change",
 *            "last_txn_date": "NEW.txn_date"
 *        },
 *        "conditions": {
 *            "isdeleted": false
 *        }
 *    }
 * 
 * 3. DERIVE HOOKS
 *    Purpose: Calculate derived/computed fields
 *    Use Cases: order.total from items, case.total_hours from time_entries
 *    
 *    Config Schema:
 *    {
 *        "target_table": "orders",
 *        "calculations": {
 *            "total": "SELECT SUM(price * quantity) FROM order_items WHERE order_id = orders.id",
 *            "item_count": "SELECT COUNT(*) FROM order_items WHERE order_id = orders.id"
 *        }
 *    }
 * 
 * 4. VALIDATE HOOKS
 *    Purpose: Run validation checks during sync
 *    Use Cases: Check inventory levels, validate budget constraints
 *    
 *    Config Schema:
 *    {
 *        "validations": [
 *            {
 *                "check": "SELECT quantity_on_hand >= 0 FROM inventory WHERE product_id = NEW.product_id",
 *                "error_message": "Insufficient inventory"
 *            }
 *        ]
 *    }
 * 
 * 5. CUSTOM HOOKS
 *    Purpose: Complex logic that can't be expressed declaratively
 *    Use Cases: Complex business rules, external API calls
 *    
 *    Config Schema:
 *    {
 *        "class": "App\\Services\\CustomHooks\\InventoryReorderHook",
 *        "method": "execute",
 *        "parameters": {
 *            "reorder_threshold": 10
 *        }
 *    }
 */

-- Insert time clock aggregation hook
-- This hook automatically calculates time clock aggregates when time_clock_aggregates are requested
INSERT INTO sync_hooks (name, trigger_table, trigger_operations, hook_type, priority, config, is_active)
VALUES (
    'time_clock_aggregation',
    'time_clock_aggregates',    -- Triggers when time_clock_aggregates table is pulled

/*
 * FUTURE HOOK EXAMPLES
 * 
 * Example 1: Inventory Stock Updates
 * INSERT INTO sync_hooks (name, description, trigger_table, trigger_operations, hook_type, config, priority) VALUES
 * (
 *     'inventory_stock_update',
 *     'Update inventory.quantity_on_hand when inventory_txn records are created',
 *     'inventory_txn',
 *     ARRAY['INSERT', 'UPDATE'],
 *     'cascade',
 *     '{
 *         "target_table": "inventory",
 *         "operation": "UPDATE",
 *         "match_on": {"product_id": "product_id", "location_id": "location_id"},
 *         "update_fields": {
 *             "quantity_on_hand": "quantity_on_hand + NEW.quantity_change",
 *             "last_txn_date": "NEW.txn_date"
 *         },
 *         "conditions": {
 *             "isdeleted": false
 *         }
 *     }'::jsonb,
 *     100
 * );
 * 
 * Example 2: Case Total Hours
 * INSERT INTO sync_hooks (name, description, trigger_table, trigger_operations, hook_type, config, priority) VALUES
 * (
 *     'case_hours_summary',
 *     'Update case total hours when time entries change',
 *     'time_entries',
 *     ARRAY['INSERT', 'UPDATE', 'DELETE'],
 *     'derive',
 *     '{
 *         "target_table": "cases",
 *         "match_on": {"case_id": "reference_id"},
 *         "calculations": {
 *             "total_hours": "SELECT SUM(hours) FROM time_entries WHERE reference_id = cases.case_id AND reference_table = ''cases'' AND isdeleted = false",
 *             "billable_hours": "SELECT SUM(hours) FROM time_entries WHERE reference_id = cases.case_id AND reference_table = ''cases'' AND billable = true AND isdeleted = false"
 *         }
 *     }'::jsonb,
 *     100
 * );
 */

-- Grant permissions
GRANT SELECT, INSERT, UPDATE ON sync_hooks TO taf;
GRANT SELECT, INSERT ON sync_hook_logs TO taf;
