-- Add capture_requests flag to api_keys
ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS capture_requests boolean NOT NULL DEFAULT false;

-- Create request_capture_logs table (monthly range-partitioned by created_at)
-- PRIMARY KEY must include the partition key, so we use (id, created_at).
CREATE TABLE IF NOT EXISTS request_capture_logs (
    id            bigserial     NOT NULL,
    api_key_id    bigint        NOT NULL,
    user_id       bigint        NOT NULL,
    request_id    varchar(64),
    path          varchar(100),
    method        varchar(10),
    ip_address    varchar(45),
    request_body  text,
    response_body text,
    nfs_file_path varchar(500),
    created_at    timestamptz   NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE INDEX IF NOT EXISTS idx_rcl_api_key_created ON request_capture_logs (api_key_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_rcl_user_id         ON request_capture_logs (user_id);

-- Pre-create partitions for previous, current, and next month
DO $$
DECLARE
    month_start DATE;
    prev_month  DATE;
    next_month  DATE;
BEGIN
    month_start := date_trunc('month', now() AT TIME ZONE 'UTC')::date;
    prev_month  := (month_start - INTERVAL '1 month')::date;
    next_month  := (month_start + INTERVAL '1 month')::date;

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS request_capture_logs_%s PARTITION OF request_capture_logs FOR VALUES FROM (%L) TO (%L)',
        to_char(prev_month,  'YYYYMM'), prev_month,  month_start
    );
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS request_capture_logs_%s PARTITION OF request_capture_logs FOR VALUES FROM (%L) TO (%L)',
        to_char(month_start, 'YYYYMM'), month_start, next_month
    );
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS request_capture_logs_%s PARTITION OF request_capture_logs FOR VALUES FROM (%L) TO (%L)',
        to_char(next_month,  'YYYYMM'), next_month,  (next_month + INTERVAL '1 month')::date
    );
END $$;