-- BinByte Database Initialization Script

-- Create extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "timescaledb";

-- Create indexes for full-text search
-- These will be created after migrations run

-- Function to create partitions automatically
CREATE OR REPLACE FUNCTION create_quarterly_partitions(
    table_name text,
    start_date date
) RETURNS void AS $
DECLARE
    end_date date;
    partition_name text;
    quarter_num int;
BEGIN
    end_date := start_date + interval '3 months';
    quarter_num := EXTRACT(quarter FROM start_date);
    partition_name := format('%s_%s_q%s', 
                           table_name,
                           EXTRACT(year FROM start_date), 
                           quarter_num);
    
    -- Create main partition
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
                   FOR VALUES FROM (%L) TO (%L)',
                   partition_name, table_name, start_date, end_date);
END;
$ LANGUAGE plpgsql;

-- Performance monitoring function
CREATE OR REPLACE FUNCTION get_table_stats()
RETURNS TABLE (
    table_name text,
    row_count bigint,
    table_size text,
    index_size text
) AS $
BEGIN
    RETURN QUERY
    SELECT 
        t.table_name::text,
        t.n_tup_ins - t.n_tup_del as row_count,
        pg_size_pretty(pg_total_relation_size(c.oid)) as table_size,
        pg_size_pretty(pg_indexes_size(c.oid)) as index_size
    FROM pg_stat_user_tables t
    JOIN pg_class c ON c.relname = t.relname
    WHERE t.schemaname = 'public'
    ORDER BY pg_total_relation_size(c.oid) DESC;
END;
$ LANGUAGE plpgsql;
