-- Create table for caching index calculations
CREATE TABLE IF NOT EXISTS index_cache (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    cache_key VARCHAR(32) UNIQUE NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    locale VARCHAR(50) NOT NULL,
    market_type VARCHAR(50) NOT NULL,
    index_data JSONB NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_cache_key ON index_cache(cache_key);
CREATE INDEX IF NOT EXISTS idx_cache_dates ON index_cache(start_date, end_date);

-- Create table for user-specific weights
CREATE TABLE IF NOT EXISTS user_weights (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    locale VARCHAR(50) NOT NULL,
    market_type VARCHAR(50) NOT NULL,
    weights_data JSONB NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for user weights
CREATE INDEX IF NOT EXISTS idx_user_session ON user_weights(session_id);
CREATE INDEX IF NOT EXISTS idx_user_params ON user_weights(session_id, locale, market_type);

-- Optional: Create table for user sessions (if you want persistent user management)
CREATE TABLE IF NOT EXISTS user_sessions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    session_id VARCHAR(255) UNIQUE NOT NULL,
    user_email VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_active TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable Row Level Security (RLS) for better data isolation
ALTER TABLE index_cache ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_weights ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_sessions ENABLE ROW LEVEL SECURITY;

-- Create policies for public access (adjust based on your auth strategy)
-- For now, allowing all authenticated users to read cached data
CREATE POLICY "Public read access for index cache" ON index_cache
    FOR SELECT USING (true);

CREATE POLICY "Public insert access for index cache" ON index_cache
    FOR INSERT WITH CHECK (true);

CREATE POLICY "Public update access for index cache" ON index_cache
    FOR UPDATE USING (true);

-- User weights should be session-specific
CREATE POLICY "Session-specific access for user weights" ON user_weights
    FOR ALL USING (true);

-- Function to clean up old cache entries (optional)
CREATE OR REPLACE FUNCTION cleanup_old_cache()
RETURNS void AS $$
BEGIN
    DELETE FROM index_cache WHERE created_at < NOW() - INTERVAL '30 days';
    DELETE FROM user_weights WHERE created_at < NOW() - INTERVAL '7 days';
END;
$$ LANGUAGE plpgsql;

-- Optional: Create a scheduled job to run cleanup (requires pg_cron extension)
-- SELECT cron.schedule('cleanup-old-cache', '0 0 * * *', 'SELECT cleanup_old_cache();');