CREATE TABLE ttl_index_table (
    table_name TEXT NOT NULL,
    column_name TEXT NOT NULL,
    expire_after_seconds INTEGER NOT NULL,
    active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    last_run TIMESTAMPTZ,
    PRIMARY KEY (table_name, column_name)
);

CREATE FUNCTION ttl_create_index(
    table_name TEXT,
    column_name TEXT,
    expire_after_seconds INTEGER
) RETURNS BOOLEAN
LANGUAGE C STRICT
AS 'MODULE_PATHNAME';

CREATE FUNCTION ttl_drop_index(
    table_name TEXT,
    column_name TEXT
) RETURNS BOOLEAN
LANGUAGE C STRICT
AS 'MODULE_PATHNAME';

CREATE FUNCTION ttl_runner() RETURNS INTEGER
LANGUAGE C STRICT
AS 'MODULE_PATHNAME';

CREATE OR REPLACE FUNCTION ttl_runner_safe() RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
    delete_count INTEGER;
    total_deleted INTEGER := 0;
    delete_query TEXT;
BEGIN
    FOR rec IN SELECT table_name, column_name, expire_after_seconds 
               FROM ttl_index_table WHERE active = true
    LOOP
        delete_query := format('DELETE FROM %I WHERE %I < NOW() - INTERVAL ''%s seconds''',
                              rec.table_name, rec.column_name, rec.expire_after_seconds);
        
        EXECUTE delete_query;
        GET DIAGNOSTICS delete_count = ROW_COUNT;
        total_deleted := total_deleted + delete_count;
        
        IF delete_count > 0 THEN
            RAISE LOG 'TTL: Deleted % rows from %.%', 
                      delete_count, rec.table_name, rec.column_name;
        END IF;
    END LOOP;
    
    UPDATE ttl_index_table SET last_run = NOW();
    
    RETURN total_deleted;
END;
$$;

CREATE FUNCTION ttl_start_worker() RETURNS BOOLEAN
LANGUAGE C STRICT
AS 'MODULE_PATHNAME';