-- Migration: Create license_heartbeats partitioned table
-- Date: 2025-06-19
-- Description: Creates the license_heartbeats table with monthly partitioning

-- Create the partitioned table
CREATE TABLE IF NOT EXISTS license_heartbeats (
    id BIGSERIAL,
    license_id INTEGER NOT NULL,
    hardware_id VARCHAR(255) NOT NULL,
    ip_address INET NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    session_data JSONB,
    PRIMARY KEY (id, timestamp),
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE
) PARTITION BY RANGE (timestamp);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_license_heartbeats_license_id_timestamp 
    ON license_heartbeats (license_id, timestamp DESC);

CREATE INDEX IF NOT EXISTS idx_license_heartbeats_timestamp 
    ON license_heartbeats (timestamp DESC);

CREATE INDEX IF NOT EXISTS idx_license_heartbeats_hardware_id 
    ON license_heartbeats (hardware_id);

CREATE INDEX IF NOT EXISTS idx_license_heartbeats_ip_address 
    ON license_heartbeats (ip_address);

-- Create partitions for current and next month
DO $$
DECLARE
    current_year INTEGER;
    current_month INTEGER;
    next_year INTEGER;
    next_month INTEGER;
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    -- Get current date info
    current_year := EXTRACT(YEAR FROM CURRENT_DATE);
    current_month := EXTRACT(MONTH FROM CURRENT_DATE);
    
    -- Calculate next month
    IF current_month = 12 THEN
        next_year := current_year + 1;
        next_month := 1;
    ELSE
        next_year := current_year;
        next_month := current_month + 1;
    END IF;
    
    -- Create current month partition
    partition_name := 'license_heartbeats_' || current_year || '_' || LPAD(current_month::TEXT, 2, '0');
    start_date := DATE_TRUNC('month', CURRENT_DATE);
    end_date := DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';
    
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF license_heartbeats FOR VALUES FROM (%L) TO (%L)',
                   partition_name, start_date, end_date);
    
    -- Create next month partition
    partition_name := 'license_heartbeats_' || next_year || '_' || LPAD(next_month::TEXT, 2, '0');
    start_date := end_date;
    end_date := start_date + INTERVAL '1 month';
    
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF license_heartbeats FOR VALUES FROM (%L) TO (%L)',
                   partition_name, start_date, end_date);
    
    RAISE NOTICE 'Created partitions for current and next month';
END $$;

-- Add comment to the table
COMMENT ON TABLE license_heartbeats IS 'Stores heartbeat data from license validations for real-time monitoring';
COMMENT ON COLUMN license_heartbeats.license_id IS 'Foreign key to licenses table';
COMMENT ON COLUMN license_heartbeats.hardware_id IS 'Hardware identifier of the device';
COMMENT ON COLUMN license_heartbeats.ip_address IS 'IP address from which the heartbeat was sent';
COMMENT ON COLUMN license_heartbeats.timestamp IS 'Timestamp of the heartbeat';
COMMENT ON COLUMN license_heartbeats.session_data IS 'Additional session data in JSON format';