-- =============================================================================
-- Audit Log Partition Management Script
-- =============================================================================
-- This script creates monthly partitions for audit log tables.
-- Run this script monthly to create new partitions.
-- =============================================================================

-- Create a function to create monthly partitions for auth_audit_logs
CREATE OR REPLACE FUNCTION create_auth_audit_log_partition()
RETURNS void AS $$
DECLARE
    partition_name text;
    start_date text;
    end_date text;
BEGIN
    -- Get the first day of next month
    start_date := date_trunc('month', CURRENT_DATE + INTERVAL '1 month');
    end_date := date_trunc('month', CURRENT_DATE + INTERVAL '2 months');
    
    -- Format partition name (e.g., auth_audit_logs_2024_02)
    partition_name := 'auth_audit_logs_' || to_char(start_date, 'YYYY_MM');
    
    -- Check if partition already exists in audit schema
    IF NOT EXISTS (
        SELECT 1 FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname = partition_name
        AND n.nspname = 'audit'
    ) THEN
        -- Create the partition in audit schema
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS audit.%I PARTITION OF audit.auth_audit_logs
             FOR VALUES FROM (%L) TO (%L)',
            partition_name,
            start_date,
            end_date
        );
        
        RAISE NOTICE 'Created partition: audit.%', partition_name;
    ELSE
        RAISE NOTICE 'Partition already exists: audit.%', partition_name;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Create a function to create monthly partitions for refresh_token_audit_logs
CREATE OR REPLACE FUNCTION create_refresh_token_audit_log_partition()
RETURNS void AS $$
DECLARE
    partition_name text;
    start_date text;
    end_date text;
BEGIN
    -- Get the first day of next month
    start_date := date_trunc('month', CURRENT_DATE + INTERVAL '1 month');
    end_date := date_trunc('month', CURRENT_DATE + INTERVAL '2 months');
    
    -- Format partition name (e.g., refresh_token_audit_logs_2024_02)
    partition_name := 'refresh_token_audit_logs_' || to_char(start_date, 'YYYY_MM');
    
    -- Check if partition already exists in audit schema
    IF NOT EXISTS (
        SELECT 1 FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname = partition_name
        AND n.nspname = 'audit'
    ) THEN
        -- Create the partition in audit schema
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS audit.%I PARTITION OF audit.refresh_token_audit_logs
             FOR VALUES FROM (%L) TO (%L)',
            partition_name,
            start_date,
            end_date
        );
        
        RAISE NOTICE 'Created partition: audit.%', partition_name;
    ELSE
        RAISE NOTICE 'Partition already exists: audit.%', partition_name;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Create a function to drop old partitions (older than 90 days)
CREATE OR REPLACE FUNCTION drop_old_audit_log_partitions(retention_months integer DEFAULT 3)
RETURNS void AS $$
DECLARE
    partition_record record;
    cutoff_date date;
BEGIN
    cutoff_date := CURRENT_DATE - (retention_months || ' months')::interval;
    
    -- Drop old auth_audit_logs partitions from audit schema
    FOR partition_record IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'audit' 
        AND tablename LIKE 'auth_audit_logs_%'
        AND tablename < 'auth_audit_logs_' || to_char(cutoff_date, 'YYYY_MM')
    LOOP
        EXECUTE 'DROP TABLE IF EXISTS audit.' || quote_ident(partition_record.tablename);
        RAISE NOTICE 'Dropped partition: audit.%', partition_record.tablename;
    END LOOP;
    
    -- Drop old refresh_token_audit_logs partitions from audit schema
    FOR partition_record IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'audit' 
        AND tablename LIKE 'refresh_token_audit_logs_%'
        AND tablename < 'refresh_token_audit_logs_' || to_char(cutoff_date, 'YYYY_MM')
    LOOP
        EXECUTE 'DROP TABLE IF EXISTS audit.' || quote_ident(partition_record.tablename);
        RAISE NOTICE 'Dropped partition: audit.%', partition_record.tablename;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- =============================================================================
-- Initial Partition Creation
-- =============================================================================

-- Create partitions for current and next 3 months
DO $$
DECLARE
    i integer;
    start_date date;
    end_date date;
    partition_name text;
BEGIN
    FOR i IN 0..3 LOOP
        start_date := date_trunc('month', CURRENT_DATE + (i || ' months')::interval);
        end_date := date_trunc('month', CURRENT_DATE + ((i + 1) || ' months')::interval);
        
        -- Create auth_audit_logs partition
        partition_name := 'auth_audit_logs_' || to_char(start_date, 'YYYY_MM');
        IF NOT EXISTS (
            SELECT 1 FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relname = partition_name
            AND n.nspname = 'audit'
        ) THEN
            EXECUTE format(
                'CREATE TABLE IF NOT EXISTS audit.%I PARTITION OF audit.auth_audit_logs
                 FOR VALUES FROM (%L) TO (%L)',
                partition_name,
                start_date,
                end_date
            );
            RAISE NOTICE 'Created partition: audit.%', partition_name;
        END IF;
        
        -- Create refresh_token_audit_logs partition
        partition_name := 'refresh_token_audit_logs_' || to_char(start_date, 'YYYY_MM');
        IF NOT EXISTS (
            SELECT 1 FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relname = partition_name
            AND n.nspname = 'audit'
        ) THEN
            EXECUTE format(
                'CREATE TABLE IF NOT EXISTS audit.%I PARTITION OF audit.refresh_token_audit_logs
                 FOR VALUES FROM (%L) TO (%L)',
                partition_name,
                start_date,
                end_date
            );
            RAISE NOTICE 'Created partition: audit.%', partition_name;
        END IF;
    END LOOP;
END $$;
