scheduler/tests/test_database_setup.sql

-- Test Database Setup for Scheduler Tests
-- Creates a dedicated test database with the same schema as production

-- Create test database and user (run as postgres superuser)
CREATE DATABASE test_scheduler;
CREATE USER test_scheduler_user WITH PASSWORD 'test_password_12345';
GRANT ALL PRIVILEGES ON DATABASE test_scheduler TO test_scheduler_user;

-- Connect to test database
\c test_scheduler

-- Grant schema permissions
GRANT ALL ON SCHEMA public TO test_scheduler_user;

-- Create scheduled_tasks table (same as production)
CREATE TABLE scheduled_tasks (
    id SERIAL PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL UNIQUE,
    service VARCHAR(50) NOT NULL,
    executor VARCHAR(100) NOT NULL,
    priority INTEGER NOT NULL DEFAULT 30,

    -- Scheduling (supports wildcards: -1 = any)
    minute INTEGER DEFAULT -1,
    hour INTEGER DEFAULT -1,
    day_of_month INTEGER DEFAULT -1,
    month INTEGER DEFAULT -1,
    day_of_week INTEGER DEFAULT -1,

    enabled BOOLEAN DEFAULT true,
    description TEXT,
    config JSONB,

    -- Execution tracking
    last_run TIMESTAMP,
    last_status VARCHAR(20),
    last_duration_seconds INTEGER,
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    timeout_seconds INTEGER DEFAULT 3600,

    -- Metadata
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    created_by VARCHAR(50),

    -- Constraints
    CHECK (priority >= 1 AND priority <= 100),
    CHECK (minute >= -1 AND minute <= 59),
    CHECK (hour >= -1 AND hour <= 23),
    CHECK (day_of_month >= -1 AND day_of_month <= 31),
    CHECK (month >= -1 AND month <= 12),
    CHECK (day_of_week >= -1 AND day_of_week <= 6)
);

-- Create task_executions table (same as production)
CREATE TABLE task_executions (
    id SERIAL PRIMARY KEY,
    task_id INTEGER NOT NULL REFERENCES scheduled_tasks(id),
    task_name VARCHAR(100) NOT NULL,
    service VARCHAR(50) NOT NULL,
    executor VARCHAR(100) NOT NULL,
    priority INTEGER NOT NULL,

    status VARCHAR(20) NOT NULL,
    triggered_by VARCHAR(50),
    triggered_at TIMESTAMP DEFAULT NOW(),
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    duration_seconds INTEGER,

    output TEXT,
    error TEXT,
    retry_count INTEGER DEFAULT 0,
    metadata JSONB
);

-- Create indexes
CREATE INDEX idx_tasks_enabled ON scheduled_tasks(enabled) WHERE enabled = true;
CREATE INDEX idx_tasks_priority ON scheduled_tasks(priority);
CREATE INDEX idx_tasks_service ON scheduled_tasks(service);
CREATE INDEX idx_tasks_schedule ON scheduled_tasks(minute, hour, day_of_month, month, day_of_week) WHERE enabled = true;

CREATE INDEX idx_executions_task_id ON task_executions(task_id);
CREATE INDEX idx_executions_task_name ON task_executions(task_name);
CREATE INDEX idx_executions_status ON task_executions(status);
CREATE INDEX idx_executions_triggered_at ON task_executions(triggered_at DESC);
CREATE INDEX idx_executions_service ON task_executions(service);
CREATE INDEX idx_executions_running ON task_executions(task_id) WHERE status = 'running';

-- Grant permissions
GRANT ALL ON ALL TABLES IN SCHEMA public TO test_scheduler_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO test_scheduler_user;

-- Insert test data
INSERT INTO scheduled_tasks
    (task_name, service, executor, priority, minute, hour, description, config, created_by)
VALUES
    ('test_fixture_task', 'scheduler', 'example_executor', 50, -1, -1,
     'Test fixture task for integration tests',
     '{"message": "Test fixture", "delay_seconds": 0}'::jsonb,
     'test_setup');

-- Verify
SELECT COUNT(*) as task_count FROM scheduled_tasks;
SELECT COUNT(*) as execution_count FROM task_executions;