-- Migration: Add cron_logs table
-- Run this manually: psql -U your_user -d your_db -f this_file.sql

CREATE TABLE IF NOT EXISTS cron_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_name VARCHAR(50) NOT NULL,
    success INTEGER DEFAULT 1 NOT NULL,
    duration_ms INTEGER DEFAULT 0 NOT NULL,
    message TEXT,
    stats TEXT,
    error TEXT,
    executed_at TIMESTAMP DEFAULT NOW() NOT NULL
);

CREATE INDEX IF NOT EXISTS cron_logs_job_name_idx ON cron_logs(job_name);
CREATE INDEX IF NOT EXISTS cron_logs_executed_at_idx ON cron_logs(executed_at);
CREATE INDEX IF NOT EXISTS cron_logs_success_idx ON cron_logs(success);

-- Clean up old logs periodically (keep 30 days)
-- You can run this as a cron job or manually
-- DELETE FROM cron_logs WHERE executed_at < NOW() - INTERVAL '30 days';
