-- Create log table for trigger executions
-- Tracks all trigger fires, successes, failures, and execution details

CREATE TABLE IF NOT EXISTS trigger_execution_log (
    id BIGSERIAL PRIMARY KEY,
    trigger_name TEXT NOT NULL,
    task_id INTEGER,
    customer_id INTEGER,
    store_code TEXT,
    week_start_date TEXT,
    old_status TEXT,
    new_status TEXT,
    execution_started_at TIMESTAMPTZ DEFAULT NOW(),
    execution_completed_at TIMESTAMPTZ,
    execution_duration_ms INTEGER,
    status TEXT NOT NULL, -- 'success', 'error', 'skipped'
    error_message TEXT,
    error_code TEXT,
    update_store_metrics_result JSONB,
    update_product_store_data_called BOOLEAN DEFAULT FALSE,
    availability_found BIGINT,
    store_visibility_updated BOOLEAN DEFAULT FALSE,
    note TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes for efficient querying
CREATE INDEX IF NOT EXISTS idx_trigger_log_task_id ON trigger_execution_log(task_id);
CREATE INDEX IF NOT EXISTS idx_trigger_log_customer_store ON trigger_execution_log(customer_id, store_code);
CREATE INDEX IF NOT EXISTS idx_trigger_log_status ON trigger_execution_log(status);
CREATE INDEX IF NOT EXISTS idx_trigger_log_created_at ON trigger_execution_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_trigger_log_week ON trigger_execution_log(week_start_date);

-- Add comment
COMMENT ON TABLE trigger_execution_log IS 'Logs all executions of trg_task_fixed_update_visibility trigger';
COMMENT ON COLUMN trigger_execution_log.status IS 'success, error, or skipped';
COMMENT ON COLUMN trigger_execution_log.execution_duration_ms IS 'Execution time in milliseconds';

