-- =============================================================================
-- BeKind Backend — Notification Stats Caching (Future)
-- Table: user_notification_stats
-- Purpose: Cache unread counts for performance optimization
-- Depends on: 003-notification-schema.sql (notification_receipts)
--
-- FUTURE: Uncomment and run this migration when implementing stats caching
-- =============================================================================

-- User notification stats table (cached unread counts for performance)
-- Uncomment below when implementing stats caching

-- CREATE TABLE IF NOT EXISTS user_notification_stats (
--     user_id         UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
--     total_count     INTEGER DEFAULT 0,
--     unread_count    INTEGER DEFAULT 0,
--     last_updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
--     created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
--     updated_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
-- );

-- =============================================================================
-- INDEXES
-- =============================================================================

-- CREATE INDEX IF NOT EXISTS idx_user_notification_stats_last_updated
--     ON user_notification_stats(last_updated_at);

-- =============================================================================
-- FUNCTIONS
-- =============================================================================

-- Rebuild user notification stats (reconciliation function)
-- Run nightly via cron to prevent drift from bulk imports/manual updates/migration failures
-- Uncomment below when implementing stats caching

-- CREATE OR REPLACE FUNCTION rebuild_user_notification_stats()
-- RETURNS INTEGER AS $$
-- DECLARE
--     rebuilt_count INTEGER;
-- BEGIN
--     -- Rebuild stats from scratch using actual data
--     INSERT INTO user_notification_stats (user_id, total_count, unread_count, last_updated_at)
--     SELECT
--         user_id,
--         COUNT(*) as total_count,
--         COUNT(*) FILTER (WHERE read_at IS NULL AND deleted_at IS NULL) as unread_count,
--         CURRENT_TIMESTAMP as last_updated_at
--     FROM notification_receipts
--     WHERE deleted_at IS NULL
--     GROUP BY user_id
--     ON CONFLICT (user_id) DO UPDATE SET
--         total_count = EXCLUDED.total_count,
--         unread_count = EXCLUDED.unread_count,
--         last_updated_at = CURRENT_TIMESTAMP;
--
--     GET DIAGNOSTICS rebuilt_count = ROW_COUNT;
--     RETURN rebuilt_count;
-- END;
-- $$ LANGUAGE plpgsql;

-- Get unread notification count (uses cached stats table for performance)
-- Uncomment below when implementing stats caching

-- CREATE OR REPLACE FUNCTION get_unread_notification_count_cached(user_uuid UUID)
-- RETURNS INTEGER AS $$
-- DECLARE
--     count_result INTEGER;
-- BEGIN
--     -- Try to get from stats table first
--     SELECT unread_count INTO count_result
--     FROM user_notification_stats
--     WHERE user_id = user_uuid;
--
--     -- If stats row doesn't exist, fall back to query and create stats
--     IF count_result IS NULL THEN
--         SELECT COUNT(*) INTO count_result
--         FROM notification_receipts
--         WHERE user_id = user_uuid
--           AND read_at IS NULL
--           AND deleted_at IS NULL;
--
--         -- Create stats row for future queries
--         INSERT INTO user_notification_stats (user_id, total_count, unread_count, last_updated_at)
--         VALUES (
--             user_uuid,
--             (SELECT COUNT(*) FROM notification_receipts WHERE user_id = user_uuid AND deleted_at IS NULL),
--             count_result,
--             CURRENT_TIMESTAMP
--         )
--         ON CONFLICT (user_id) DO NOTHING;
--     END IF;
--
--     RETURN count_result;
-- END;
-- $$ LANGUAGE plpgsql;
