complete-database-setup.sql

-- ========================================
-- COMPLETE DATABASE SETUP FOR TENDLY
-- ========================================
-- This migration sets up the entire database from scratch for new developers
-- It includes all tables, functions, storage, rules, and policies with existence checks
-- Version: 2.0 (Complete Setup)
-- Date: 2025-01-28

-- ========================================
-- 1. EXTENSIONS
-- ========================================

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ========================================
-- 1.1 FUNCTIONS REQUIRED BY TABLE DEFAULTS
-- ========================================

-- Define expiry default functions BEFORE tables that reference them
CREATE OR REPLACE FUNCTION get_drop_expiry_default() RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
    v_minutes INTEGER;
BEGIN
    -- Will read from app_settings later if present; fallback to 5 minutes
    BEGIN
        SELECT int_value INTO v_minutes FROM app_settings WHERE key = 'drop_default_expiry_minutes';
    EXCEPTION WHEN undefined_table THEN
        v_minutes := NULL;
    END;
    RETURN NOW() + ((COALESCE(v_minutes, 5)) || ' minutes')::INTERVAL;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION get_encrypted_content_expiry_default() RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
    v_minutes INTEGER;
BEGIN
    BEGIN
        SELECT int_value INTO v_minutes FROM app_settings WHERE key = 'encrypted_content_default_expiry_minutes';
    EXCEPTION WHEN undefined_table THEN
        v_minutes := NULL;
    END;
    RETURN NOW() + ((COALESCE(v_minutes, 5)) || ' minutes')::INTERVAL;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION get_share_code_expiry_default() RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
    v_minutes INTEGER;
BEGIN
    BEGIN
        SELECT int_value INTO v_minutes FROM app_settings WHERE key = 'drop_default_expiry_minutes';
    EXCEPTION WHEN undefined_table THEN
        v_minutes := NULL;
    END;
    RETURN NOW() + ((COALESCE(v_minutes, 5)) || ' minutes')::INTERVAL;
END;
$$ LANGUAGE plpgsql STABLE;

-- ========================================
-- 2. CORE TABLES
-- ========================================

-- Core drops table with all necessary columns
CREATE TABLE IF NOT EXISTS drops (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    share_code TEXT NOT NULL UNIQUE,
    title TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE DEFAULT get_drop_expiry_default(),
    view_count INTEGER DEFAULT 0,
    ip_address TEXT, -- Legacy column for backward compatibility
    user_agent TEXT, -- Legacy column for backward compatibility
    session_token_hash TEXT,
    created_by_ip TEXT, -- Legacy column for backward compatibility
    last_accessed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    -- Security additions (privacy-first)
    ip_hash TEXT, -- Hashed IP only
    created_at_secure TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at_secure TIMESTAMP WITH TIME ZONE DEFAULT (NOW() + INTERVAL '5 minutes')
);

-- Ensure expiry is never null or past on insert
DROP FUNCTION IF EXISTS set_drop_expiry_defaults() CASCADE;
CREATE OR REPLACE FUNCTION set_drop_expiry_defaults() RETURNS trigger AS $$
BEGIN
    IF NEW.expires_at IS NULL OR NEW.expires_at <= NOW() THEN
        NEW.expires_at := get_drop_expiry_default();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tr_set_drop_expiry ON drops;
CREATE TRIGGER tr_set_drop_expiry
BEFORE INSERT ON drops
FOR EACH ROW
EXECUTE FUNCTION set_drop_expiry_defaults();

-- Core drop_items table with all necessary columns
CREATE TABLE IF NOT EXISTS drop_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    drop_id UUID NOT NULL REFERENCES drops(id) ON DELETE CASCADE,
    item_type TEXT NOT NULL CHECK (item_type IN ('text', 'file')),
    content_data TEXT,
    file_name TEXT,
    file_size INTEGER,
    file_type TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    -- Security additions
    content_size INTEGER,
    content_hash TEXT,
    sanitized_content TEXT,
    -- Zero-knowledge encryption columns
    encrypted_key TEXT,
    key_iv TEXT,
    key_tag TEXT,
    encryption_version TEXT DEFAULT '1.0'
);

-- Pro waitlist table (preserve existing users - do not truncate)
CREATE TABLE IF NOT EXISTS pro_waitlist (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Privacy-first rate limiting table (hashed IPs only)
CREATE TABLE IF NOT EXISTS rate_limits_private (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ip_hash TEXT NOT NULL, -- Hashed IP, never store actual IP
    action_type TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    request_count INTEGER DEFAULT 1,
    UNIQUE(ip_hash, action_type)
);

-- Temporary session storage (expires quickly, no user tracking)
CREATE TABLE IF NOT EXISTS temp_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_hash TEXT NOT NULL UNIQUE, -- Hashed session data
    drop_id UUID REFERENCES drops(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    -- NO IP storage, NO user agent storage, NO fingerprinting
    CONSTRAINT temp_sessions_expires_check CHECK (expires_at <= created_at + INTERVAL '1 hour')
);

-- Blocked IP hashes (not actual IPs)
CREATE TABLE IF NOT EXISTS blocked_ip_hashes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ip_hash TEXT NOT NULL UNIQUE, -- Hashed IP only
    blocked_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,
    is_permanent BOOLEAN DEFAULT false
);

-- Ensure drops expiration defaults to 5 minutes
ALTER TABLE drops ALTER COLUMN expires_at SET DEFAULT get_drop_expiry_default();
-- Backfill existing rows that are NULL or already expired to 5 minutes from now
UPDATE drops 
SET expires_at = get_drop_expiry_default()
WHERE expires_at IS NULL OR expires_at <= NOW();

-- Security events table with hashed IPs (privacy-preserving)
CREATE TABLE IF NOT EXISTS security_events (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    event_type TEXT NOT NULL,
    ip_hash TEXT NOT NULL, -- Hashed IP address (privacy-preserving)
    details JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Encrypted content table for zero-knowledge encryption
CREATE TABLE IF NOT EXISTS encrypted_content (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    content_hash TEXT NOT NULL UNIQUE,
    encrypted_data TEXT NOT NULL,
    iv TEXT NOT NULL,
    salt TEXT NOT NULL,
    tag TEXT NOT NULL,
    file_name TEXT,
    file_size BIGINT,
    file_type TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE DEFAULT get_encrypted_content_expiry_default(),
    encryption_version TEXT DEFAULT '4.0',
    view_count INTEGER DEFAULT 0,
    is_pro_content BOOLEAN DEFAULT false,
    pro_expiry_hours INTEGER,
    ip_hash TEXT,
    user_agent_hash TEXT,
    last_accessed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Share code mappings for encrypted content
CREATE TABLE IF NOT EXISTS share_code_mappings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    share_code TEXT NOT NULL UNIQUE,
    content_hash TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE DEFAULT get_share_code_expiry_default()
);

-- ========================================
-- 2.1 APPLICATION SETTINGS (CONFIGURABLE DEFAULTS)
-- ========================================

-- Simple key->integer settings store (idempotent)
CREATE TABLE IF NOT EXISTS app_settings (
    key TEXT PRIMARY KEY,
    int_value INTEGER,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Seed default settings
INSERT INTO app_settings (key, int_value)
VALUES ('drop_default_expiry_minutes', 5)
ON CONFLICT (key) DO NOTHING;

INSERT INTO app_settings (key, int_value)
VALUES ('encrypted_content_default_expiry_minutes', 5)
ON CONFLICT (key) DO NOTHING;

-- ========================================
-- 3. INDEXES FOR PERFORMANCE
-- ========================================

-- Core table indexes
CREATE INDEX IF NOT EXISTS idx_drops_share_code ON drops(share_code);
CREATE INDEX IF NOT EXISTS idx_drops_expires_at ON drops(expires_at);
CREATE INDEX IF NOT EXISTS idx_drops_created_at ON drops(created_at);
CREATE INDEX IF NOT EXISTS idx_drop_items_drop_id ON drop_items(drop_id);
CREATE INDEX IF NOT EXISTS idx_drop_items_created_at ON drop_items(created_at);
CREATE INDEX IF NOT EXISTS idx_pro_waitlist_email ON pro_waitlist(email);

-- Security table indexes
CREATE INDEX IF NOT EXISTS idx_rate_limits_private_hash ON rate_limits_private(ip_hash, action_type);
CREATE INDEX IF NOT EXISTS idx_rate_limits_private_expires ON rate_limits_private(expires_at);
CREATE INDEX IF NOT EXISTS idx_temp_sessions_hash ON temp_sessions(session_hash);
CREATE INDEX IF NOT EXISTS idx_temp_sessions_expires ON temp_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_blocked_ip_hashes_hash ON blocked_ip_hashes(ip_hash);
CREATE INDEX IF NOT EXISTS idx_blocked_ip_hashes_expires ON blocked_ip_hashes(expires_at) WHERE expires_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_security_events_event_type ON security_events(event_type);
CREATE INDEX IF NOT EXISTS idx_security_events_created_at ON security_events(created_at);
CREATE INDEX IF NOT EXISTS idx_security_events_ip_hash ON security_events(ip_hash);

-- Encrypted content indexes
CREATE INDEX IF NOT EXISTS idx_encrypted_content_hash ON encrypted_content(content_hash);
CREATE INDEX IF NOT EXISTS idx_encrypted_content_expires_at ON encrypted_content(expires_at);
CREATE INDEX IF NOT EXISTS idx_share_code_mappings_share_code ON share_code_mappings(share_code);
CREATE INDEX IF NOT EXISTS idx_share_code_mappings_content_hash ON share_code_mappings(content_hash);
CREATE INDEX IF NOT EXISTS idx_share_code_mappings_expires_at ON share_code_mappings(expires_at);

-- ========================================
-- 4. UTILITY FUNCTIONS
-- ========================================

-- Drop existing utility functions first to handle signature changes
DROP FUNCTION IF EXISTS hash_ip(INET) CASCADE;
DROP FUNCTION IF EXISTS is_ip_hash_blocked(INET) CASCADE;
DROP FUNCTION IF EXISTS check_rate_limit_private(INET, TEXT, INTEGER, INTEGER) CASCADE;

-- Function to hash IP addresses (privacy-preserving)
CREATE OR REPLACE FUNCTION hash_ip(p_ip_address INET) RETURNS TEXT AS $$
BEGIN
    -- Hash IP with salt for additional privacy
    RETURN encode(digest(p_ip_address::TEXT || 'tendly_salt_2025', 'sha256'), 'hex');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Config-driven expiry defaults
DROP FUNCTION IF EXISTS get_drop_expiry_default() CASCADE;
CREATE OR REPLACE FUNCTION get_drop_expiry_default() RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
    v_minutes INTEGER;
BEGIN
    SELECT int_value INTO v_minutes FROM app_settings WHERE key = 'drop_default_expiry_minutes';
    RETURN NOW() + ((COALESCE(v_minutes, 5)) || ' minutes')::INTERVAL;
END;
$$ LANGUAGE plpgsql STABLE;

DROP FUNCTION IF EXISTS get_encrypted_content_expiry_default() CASCADE;
CREATE OR REPLACE FUNCTION get_encrypted_content_expiry_default() RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
    v_minutes INTEGER;
BEGIN
    SELECT int_value INTO v_minutes FROM app_settings WHERE key = 'encrypted_content_default_expiry_minutes';
    RETURN NOW() + ((COALESCE(v_minutes, 5)) || ' minutes')::INTERVAL;
END;
$$ LANGUAGE plpgsql STABLE;

DROP FUNCTION IF EXISTS get_share_code_expiry_default() CASCADE;
CREATE OR REPLACE FUNCTION get_share_code_expiry_default() RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
    v_minutes INTEGER;
BEGIN
    SELECT int_value INTO v_minutes FROM app_settings WHERE key = 'drop_default_expiry_minutes';
    RETURN NOW() + ((COALESCE(v_minutes, 5)) || ' minutes')::INTERVAL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Function to check if IP hash is blocked
CREATE OR REPLACE FUNCTION is_ip_hash_blocked(p_ip_address INET) RETURNS BOOLEAN AS $$
DECLARE
    v_ip_hash TEXT;
    v_is_blocked BOOLEAN;
BEGIN
    v_ip_hash := hash_ip(p_ip_address);
    
    SELECT EXISTS(
        SELECT 1 FROM blocked_ip_hashes 
        WHERE ip_hash = v_ip_hash 
        AND (expires_at IS NULL OR expires_at > NOW())
    ) INTO v_is_blocked;
    
    RETURN v_is_blocked;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Privacy-preserving rate limiting (no user tracking)
CREATE OR REPLACE FUNCTION check_rate_limit_private(
    p_ip_address INET,
    p_action_type TEXT,
    p_max_requests INTEGER DEFAULT 10,
    p_window_minutes INTEGER DEFAULT 15
) RETURNS BOOLEAN AS $$
DECLARE
    v_ip_hash TEXT;
    v_count INTEGER;
    v_expires_at TIMESTAMP WITH TIME ZONE;
BEGIN
    -- Hash IP immediately - we never store the actual IP
    v_ip_hash := hash_ip(p_ip_address);
    
    -- Check if IP hash is blocked
    IF is_ip_hash_blocked(p_ip_address) THEN
        RETURN false;
    END IF;
    
    -- Clean up expired rate limit records
    DELETE FROM rate_limits_private 
    WHERE expires_at < NOW();
    
    -- Set expiration time
    v_expires_at := NOW() + (p_window_minutes || ' minutes')::INTERVAL;
    
    -- Try to insert or update rate limit record
    INSERT INTO rate_limits_private (ip_hash, action_type, expires_at, request_count)
    VALUES (v_ip_hash, p_action_type, v_expires_at, 1)
    ON CONFLICT (ip_hash, action_type)
    DO UPDATE SET 
        request_count = rate_limits_private.request_count + 1,
        expires_at = v_expires_at
    WHERE rate_limits_private.expires_at > NOW();
    
    -- Get current count
    SELECT request_count INTO v_count
    FROM rate_limits_private
    WHERE ip_hash = v_ip_hash 
    AND action_type = p_action_type
    AND expires_at > NOW();
    
    -- Return true if under limit
    RETURN COALESCE(v_count, 0) <= p_max_requests;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Privacy-preserving content validation (no user tracking)
-- Drop existing function first to handle parameter name changes
DROP FUNCTION IF EXISTS validate_content_private(TEXT, TEXT, INTEGER, TEXT) CASCADE;

CREATE OR REPLACE FUNCTION validate_content_private(
    p_content TEXT,
    p_content_type TEXT DEFAULT 'text',
    p_max_size_kb INTEGER DEFAULT 10,
    p_file_name TEXT DEFAULT NULL
) RETURNS JSON AS $$
DECLARE
    v_content_size INTEGER;
    v_sanitized_content TEXT;
    v_suspicious_patterns TEXT[] DEFAULT ARRAY[]::TEXT[];
BEGIN
    -- Check content size
    v_content_size := LENGTH(p_content);
    
    IF v_content_size > (p_max_size_kb * 1024) THEN
        RETURN json_build_object(
            'valid', false,
            'error', 'Content too large',
            'size', v_content_size,
            'max_size', p_max_size_kb * 1024
        );
    END IF;
    
    -- Initialize sanitized content
    v_sanitized_content := p_content;
    
    -- Basic security checks (no user tracking)
    IF p_content_type = 'text' THEN
        -- Check for script tags
        IF p_content ~* '<script[^>]*>.*?</script>' THEN
            v_suspicious_patterns := array_append(v_suspicious_patterns, 'script_tag');
        END IF;
        
        -- Check for javascript: protocol
        IF p_content ~* 'javascript:' THEN
            v_suspicious_patterns := array_append(v_suspicious_patterns, 'javascript_protocol');
        END IF;
        
        -- Check for event handlers
        IF p_content ~* 'on\w+\s*=' THEN
            v_suspicious_patterns := array_append(v_suspicious_patterns, 'event_handler');
        END IF;
        
        -- Check for iframe/object/embed tags
        IF p_content ~* '<(iframe|object|embed)' THEN
            v_suspicious_patterns := array_append(v_suspicious_patterns, 'embedding_tag');
        END IF;
        
        -- Check for data: protocol
        IF p_content ~* 'data:text/html' THEN
            v_suspicious_patterns := array_append(v_suspicious_patterns, 'data_protocol');
        END IF;
        
        -- Check for excessive URLs (spam detection)
        IF (LENGTH(p_content) - LENGTH(REPLACE(p_content, 'http', ''))) / 4 > 5 THEN
            v_suspicious_patterns := array_append(v_suspicious_patterns, 'excessive_urls');
        END IF;
        
        -- Apply sanitization
        v_sanitized_content := REGEXP_REPLACE(v_sanitized_content, '<script[^>]*>.*?</script>', '', 'gi');
        v_sanitized_content := REGEXP_REPLACE(v_sanitized_content, 'javascript:', '', 'gi');
        v_sanitized_content := REGEXP_REPLACE(v_sanitized_content, 'on\w+\s*=', '', 'gi');
        v_sanitized_content := REGEXP_REPLACE(v_sanitized_content, '<(iframe|object|embed)', '', 'gi');
        v_sanitized_content := REGEXP_REPLACE(v_sanitized_content, 'data:text/html', '', 'gi');
        v_sanitized_content := TRIM(v_sanitized_content);
    END IF;
    
    -- File type validation for file uploads
    IF p_content_type = 'file' AND p_file_name IS NOT NULL THEN
        -- Check for dangerous file extensions
        IF p_file_name ~* '\.(exe|bat|cmd|com|pif|scr|vbs|js|jar|war|ear|apk|dmg|deb|rpm|msi|app)$' THEN
            v_suspicious_patterns := array_append(v_suspicious_patterns, 'dangerous_file_type');
        END IF;
        
        -- Check for double extensions
        IF p_file_name ~* '\.(txt|jpg|png|gif|pdf)\.(exe|bat|cmd|com|pif|scr|vbs|js)$' THEN
            v_suspicious_patterns := array_append(v_suspicious_patterns, 'double_extension');
        END IF;
    END IF;
    
    -- Determine if content is valid
    IF array_length(v_suspicious_patterns, 1) > 0 THEN
        RETURN json_build_object(
            'valid', false,
            'error', 'Content contains suspicious patterns',
            'suspicious_patterns', v_suspicious_patterns,
            'sanitized_content', v_sanitized_content
        );
    ELSE
        RETURN json_build_object(
            'valid', true,
            'sanitized_content', v_sanitized_content,
            'size', v_content_size
        );
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ========================================
-- 5. SESSION MANAGEMENT FUNCTIONS
-- ========================================

-- Drop existing session functions first to handle signature changes
DROP FUNCTION IF EXISTS generate_temp_session(UUID) CASCADE;
DROP FUNCTION IF EXISTS validate_temp_session(TEXT, UUID) CASCADE;

-- Privacy-preserving session token generation (temporary, no tracking)
CREATE OR REPLACE FUNCTION generate_temp_session(
    p_drop_id UUID
) RETURNS TEXT AS $$
DECLARE
    v_token TEXT;
    v_session_hash TEXT;
    v_expires_at TIMESTAMP WITH TIME ZONE;
BEGIN
    -- Generate secure token
    v_token := encode(gen_random_bytes(32), 'hex');
    v_session_hash := encode(digest(v_token, 'sha256'), 'hex');
    
    -- Set short expiration (1 hour max for privacy)
    v_expires_at := NOW() + INTERVAL '1 hour';
    
    -- Store hashed session (no IP, no user agent, no tracking)
    INSERT INTO temp_sessions (session_hash, drop_id, expires_at)
    VALUES (v_session_hash, p_drop_id, v_expires_at);
    
    -- Update drop with hashed session (no IP tracking)
    UPDATE drops 
    SET session_token_hash = v_session_hash
    WHERE id = p_drop_id;
    
    RETURN v_token;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Privacy-preserving session validation (no tracking)
CREATE OR REPLACE FUNCTION validate_temp_session(
    p_token TEXT,
    p_drop_id UUID
) RETURNS BOOLEAN AS $$
DECLARE
    v_session_hash TEXT;
    v_is_valid BOOLEAN;
BEGIN
    -- Hash the provided token
    v_session_hash := encode(digest(p_token, 'sha256'), 'hex');
    
    -- Check if session is valid and active
    SELECT EXISTS(
        SELECT 1 FROM temp_sessions 
        WHERE session_hash = v_session_hash 
        AND drop_id = p_drop_id 
        AND expires_at > NOW()
    ) INTO v_is_valid;
    
    RETURN v_is_valid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ========================================
-- 6. SECURITY EVENT LOGGING
-- ========================================

-- Drop existing security event function first to handle signature changes
DROP FUNCTION IF EXISTS log_security_event(TEXT, TEXT, JSONB) CASCADE;

-- Function to log security events with hashed IPs
CREATE OR REPLACE FUNCTION log_security_event(
    p_event_type TEXT,
    p_ip_address TEXT,
    p_details JSONB DEFAULT NULL
)
RETURNS void AS $$
DECLARE
    v_ip_hash TEXT;
BEGIN
    -- Hash the IP address for privacy
    v_ip_hash := hash_ip(p_ip_address::INET);
    
    -- Insert security event with hashed IP
    INSERT INTO security_events (
        event_type,
        ip_hash,
        details
    ) VALUES (
        p_event_type,
        v_ip_hash,
        p_details
    );
    
    -- Optional: Clean up old events (keep last 30 days)
    DELETE FROM security_events 
    WHERE created_at < NOW() - INTERVAL '30 days';
    
EXCEPTION
    WHEN OTHERS THEN
        -- Silently ignore errors to avoid blocking main functionality
        NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ========================================
-- 7. CORE DROP MANAGEMENT FUNCTIONS
-- ========================================

-- Drop existing functions first to handle any signature changes
DROP FUNCTION IF EXISTS secure_create_drop(TEXT, INET) CASCADE;
DROP FUNCTION IF EXISTS secure_add_drop_item(UUID, TEXT, TEXT, TEXT, INET, TEXT, INTEGER, TEXT) CASCADE;
DROP FUNCTION IF EXISTS secure_update_drop_item(UUID, TEXT, TEXT, INET) CASCADE;
DROP FUNCTION IF EXISTS update_drop_item_with_code(UUID, TEXT, TEXT) CASCADE;

-- Privacy-first create drop function (no user tracking)
CREATE OR REPLACE FUNCTION secure_create_drop(
    p_share_code TEXT,
    p_ip_address INET
) RETURNS JSON AS $$
DECLARE
    v_drop_id UUID;
    v_session_token TEXT;
    v_ip_hash TEXT;
BEGIN
    -- Hash IP for privacy
    v_ip_hash := hash_ip(p_ip_address);
    
    -- Check if IP hash is blocked
    IF is_ip_hash_blocked(p_ip_address) THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Access denied'
        );
    END IF;
    
    -- Check rate limiting
    IF NOT check_rate_limit_private(p_ip_address, 'create_drop', 3, 10) THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Rate limit exceeded. Please wait before creating more drops.'
        );
    END IF;
    
    -- Validate share code
    IF LENGTH(p_share_code) < 6 OR LENGTH(p_share_code) > 20 THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Invalid share code length'
        );
    END IF;
    
    IF NOT p_share_code ~ '^[A-Za-z0-9]+$' THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Share code contains invalid characters'
        );
    END IF;
    
    -- Create the drop (no user tracking)
    INSERT INTO drops (share_code, ip_hash, created_at_secure, expires_at_secure)
    VALUES (p_share_code, v_ip_hash, NOW(), NOW() + INTERVAL '5 minutes')
    RETURNING id INTO v_drop_id;
    
    -- Generate temporary session token
    v_session_token := generate_temp_session(v_drop_id);
    
    RETURN json_build_object(
        'success', true,
        'drop_id', v_drop_id,
        'session_token', v_session_token
    );
    
EXCEPTION
    WHEN OTHERS THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Failed to create drop'
        );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Privacy-first add item function (no user tracking)
CREATE OR REPLACE FUNCTION secure_add_drop_item(
    p_drop_id UUID,
    p_item_type TEXT,
    p_content_data TEXT,
    p_session_token TEXT,
    p_ip_address INET,
    p_file_name TEXT DEFAULT NULL,
    p_file_size INTEGER DEFAULT NULL,
    p_file_type TEXT DEFAULT NULL
) RETURNS JSON AS $$
DECLARE
    v_validation_result JSON;
    v_content_size INTEGER;
    v_sanitized_content TEXT;
    v_content_hash TEXT;
    v_item_id UUID;
BEGIN
    -- Check if IP hash is blocked
    IF is_ip_hash_blocked(p_ip_address) THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Access denied'
        );
    END IF;
    
    -- Check rate limiting
    IF NOT check_rate_limit_private(p_ip_address, 'add_item', 10, 5) THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Rate limit exceeded. Please wait before adding more items.'
        );
    END IF;
    
    -- Validate session token
    IF NOT validate_temp_session(p_session_token, p_drop_id) THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Invalid or expired session token'
        );
    END IF;
    
    -- Validate content
    v_validation_result := validate_content_private(p_content_data, p_item_type, 10, p_file_name);
    
    IF (v_validation_result->>'valid')::BOOLEAN = false THEN
        RETURN json_build_object(
            'success', false,
            'error', v_validation_result->>'error'
        );
    END IF;
    
    -- Extract validated data
    v_sanitized_content := v_validation_result->>'sanitized_content';
    v_content_size := (v_validation_result->>'size')::INTEGER;
    v_content_hash := encode(digest(v_sanitized_content, 'sha256'), 'hex');
    
    -- Insert the item
    INSERT INTO drop_items (
        drop_id, 
        item_type, 
        content_data, 
        file_name, 
        file_size, 
        file_type,
        content_size,
        content_hash,
        sanitized_content
    )
    VALUES (
        p_drop_id, 
        p_item_type, 
        v_sanitized_content, 
        p_file_name, 
        p_file_size, 
        p_file_type,
        v_content_size,
        v_content_hash,
        v_sanitized_content
    )
    RETURNING id INTO v_item_id;
    
    RETURN json_build_object(
        'success', true,
        'item_id', v_item_id,
        'message', 'Item added successfully'
    );
    
EXCEPTION
    WHEN OTHERS THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Failed to add item'
        );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Privacy-first update function (no user tracking)
CREATE OR REPLACE FUNCTION secure_update_drop_item(
    p_drop_item_id UUID,
    p_new_content_data TEXT,
    p_session_token TEXT,
    p_ip_address INET
) RETURNS JSON AS $$
DECLARE
    v_drop_id UUID;
    v_validation_result JSON;
    v_content_size INTEGER;
    v_sanitized_content TEXT;
    v_content_hash TEXT;
BEGIN
    -- Check if IP hash is blocked
    IF is_ip_hash_blocked(p_ip_address) THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Access denied'
        );
    END IF;
    
    -- Check rate limiting
    IF NOT check_rate_limit_private(p_ip_address, 'update_content', 5, 5) THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Rate limit exceeded. Please wait before making more changes.'
        );
    END IF;
    
    -- Get drop_id for the item
    SELECT drop_id INTO v_drop_id
    FROM drop_items
    WHERE id = p_drop_item_id;
    
    IF v_drop_id IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Drop item not found'
        );
    END IF;
    
    -- Validate session token
    IF NOT validate_temp_session(p_session_token, v_drop_id) THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Invalid or expired session token'
        );
    END IF;
    
    -- Validate content
    v_validation_result := validate_content_private(p_new_content_data, 'text', 10);
    
    IF (v_validation_result->>'valid')::BOOLEAN = false THEN
        RETURN json_build_object(
            'success', false,
            'error', v_validation_result->>'error'
        );
    END IF;
    
    -- Extract validated data
    v_sanitized_content := v_validation_result->>'sanitized_content';
    v_content_size := (v_validation_result->>'size')::INTEGER;
    v_content_hash := encode(digest(v_sanitized_content, 'sha256'), 'hex');
    
    -- Update the drop item
    UPDATE drop_items 
    SET content_data = v_sanitized_content,
        content_size = v_content_size,
        content_hash = v_content_hash,
        sanitized_content = v_sanitized_content
    WHERE id = p_drop_item_id;
    
    RETURN json_build_object(
        'success', true,
        'message', 'Content updated successfully'
    );
    
EXCEPTION
    WHEN OTHERS THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Database error occurred'
        );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Update drop item using share code (for public editing)
CREATE OR REPLACE FUNCTION update_drop_item_with_code(
    p_drop_item_id UUID,
    p_new_content_data TEXT,
    p_share_code TEXT
) RETURNS JSON AS $$
DECLARE
    v_drop_id UUID;
    v_validation_result JSON;
    v_content_size INTEGER;
    v_sanitized_content TEXT;
    v_content_hash TEXT;
BEGIN
    -- Get drop_id and verify share code
    SELECT di.drop_id INTO v_drop_id
    FROM drop_items di
    JOIN drops d ON d.id = di.drop_id
    WHERE di.id = p_drop_item_id
    AND d.share_code = p_share_code
    AND d.expires_at > NOW();
    
    IF v_drop_id IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Drop item not found or share code invalid'
        );
    END IF;
    
    -- Validate content
    v_validation_result := validate_content_private(p_new_content_data, 'text', 10);
    
    IF (v_validation_result->>'valid')::BOOLEAN = false THEN
        RETURN json_build_object(
            'success', false,
            'error', v_validation_result->>'error'
        );
    END IF;
    
    -- Extract validated data
    v_sanitized_content := v_validation_result->>'sanitized_content';
    v_content_size := (v_validation_result->>'size')::INTEGER;
    v_content_hash := encode(digest(v_sanitized_content, 'sha256'), 'hex');
    
    -- Update the drop item
    UPDATE drop_items 
    SET content_data = v_sanitized_content,
        content_size = v_content_size,
        content_hash = v_content_hash,
        sanitized_content = v_sanitized_content
    WHERE id = p_drop_item_id;
    
    RETURN json_build_object(
        'success', true,
        'message', 'Content updated successfully'
    );
    
EXCEPTION
    WHEN OTHERS THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Database error occurred'
        );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ========================================
-- 8. ENCRYPTED CONTENT FUNCTIONS
-- ========================================

-- Drop existing encrypted content functions first to handle signature changes
DROP FUNCTION IF EXISTS store_encrypted_content(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, BIGINT, TEXT, BOOLEAN, INTEGER) CASCADE;
DROP FUNCTION IF EXISTS store_encrypted_text_content(TEXT, TEXT, TEXT, TEXT, TEXT) CASCADE;
DROP FUNCTION IF EXISTS store_encrypted_file_content(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, BIGINT, TEXT) CASCADE;
DROP FUNCTION IF EXISTS get_encrypted_content_by_hash(TEXT) CASCADE;
DROP FUNCTION IF EXISTS get_encrypted_content_by_share_code(TEXT) CASCADE;

-- Function to store encrypted content securely
CREATE OR REPLACE FUNCTION store_encrypted_content(
    p_content_hash TEXT,
    p_encrypted_data TEXT,
    p_iv TEXT,
    p_salt TEXT,
    p_tag TEXT,
    p_file_name TEXT DEFAULT NULL,
    p_file_size BIGINT DEFAULT NULL,
    p_file_type TEXT DEFAULT NULL,
    p_is_pro_content BOOLEAN DEFAULT false,
    p_pro_expiry_hours INTEGER DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    v_id UUID;
BEGIN
    -- Validate content hash format
    IF p_content_hash IS NULL OR LENGTH(p_content_hash) != 64 OR p_content_hash !~ '^[a-f0-9]+$' THEN
        RAISE EXCEPTION 'Invalid content hash format';
    END IF;
    
    -- Validate pro plan parameters
    IF p_is_pro_content = true AND (p_pro_expiry_hours IS NULL OR p_pro_expiry_hours < 1 OR p_pro_expiry_hours > 168) THEN
        RAISE EXCEPTION 'Pro plan expiry must be between 1 and 168 hours';
    END IF;
    
    -- Insert encrypted content
    INSERT INTO encrypted_content (
        content_hash,
        encrypted_data,
        iv,
        salt,
        tag,
        file_name,
        file_size,
        file_type,
        created_at,
        expires_at,
        encryption_version,
        is_pro_content,
        pro_expiry_hours,
        ip_hash,
        user_agent_hash
    ) VALUES (
        p_content_hash,
        p_encrypted_data,
        p_iv,
        p_salt,
        p_tag,
        p_file_name,
        p_file_size,
        p_file_type,
        NOW(),
        CASE 
            WHEN p_is_pro_content = true THEN NOW() + (p_pro_expiry_hours || ' hours')::INTERVAL
            ELSE NOW() + INTERVAL '5 minutes'
        END,
        '4.0',
        p_is_pro_content,
        p_pro_expiry_hours,
        encode(sha256(inet_client_addr()::text::bytea), 'hex'),
        encode(sha256(current_setting('request.headers')::text::bytea), 'hex')
    )
    RETURNING id INTO v_id;
    
    RETURN v_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to store encrypted text content (simplified version)
CREATE OR REPLACE FUNCTION store_encrypted_text_content(
    p_content_hash TEXT,
    p_encrypted_data TEXT,
    p_iv TEXT,
    p_salt TEXT,
    p_tag TEXT
)
RETURNS UUID AS $$
BEGIN
    RETURN store_encrypted_content(
        p_content_hash,
        p_encrypted_data,
        p_iv,
        p_salt,
        p_tag,
        NULL, -- file_name
        NULL, -- file_size
        NULL, -- file_type
        false, -- is_pro_content
        NULL  -- pro_expiry_hours
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to store encrypted file content (simplified version)
CREATE OR REPLACE FUNCTION store_encrypted_file_content(
    p_content_hash TEXT,
    p_encrypted_data TEXT,
    p_iv TEXT,
    p_salt TEXT,
    p_tag TEXT,
    p_file_name TEXT,
    p_file_size BIGINT,
    p_file_type TEXT
)
RETURNS UUID AS $$
BEGIN
    RETURN store_encrypted_content(
        p_content_hash,
        p_encrypted_data,
        p_iv,
        p_salt,
        p_tag,
        p_file_name,
        p_file_size,
        p_file_type,
        false, -- is_pro_content
        NULL  -- pro_expiry_hours
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get encrypted content by content hash
CREATE OR REPLACE FUNCTION get_encrypted_content_by_hash(p_content_hash TEXT)
RETURNS TABLE (
    id UUID,
    content_hash TEXT,
    encrypted_data TEXT,
    iv TEXT,
    salt TEXT,
    tag TEXT,
    file_name TEXT,
    file_size BIGINT,
    file_type TEXT,
    created_at TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE,
    encryption_version TEXT,
    view_count INTEGER,
    is_pro_content BOOLEAN,
    pro_expiry_hours INTEGER,
    ip_hash TEXT,
    user_agent_hash TEXT,
    last_accessed_at TIMESTAMP WITH TIME ZONE
) AS $$
BEGIN
    -- Validate content hash format
    IF p_content_hash IS NULL OR LENGTH(p_content_hash) != 64 OR p_content_hash !~ '^[a-f0-9]+$' THEN
        RAISE EXCEPTION 'Invalid content hash format';
    END IF;
    
    -- Return encrypted content data
    RETURN QUERY
    SELECT 
        ec.id,
        ec.content_hash,
        ec.encrypted_data,
        ec.iv,
        ec.salt,
        ec.tag,
        ec.file_name,
        ec.file_size,
        ec.file_type,
        ec.created_at,
        ec.expires_at,
        ec.encryption_version,
        ec.view_count,
        ec.is_pro_content,
        ec.pro_expiry_hours,
        ec.ip_hash,
        ec.user_agent_hash,
        ec.last_accessed_at
    FROM encrypted_content ec
    WHERE ec.content_hash = p_content_hash
    AND ec.expires_at > NOW();
    
    -- Update last accessed timestamp
    UPDATE encrypted_content 
    SET last_accessed_at = NOW()
    WHERE content_hash = p_content_hash;
    
EXCEPTION
    WHEN OTHERS THEN
        -- Return empty result set on error
        RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get encrypted content by share code (combines both operations)
CREATE OR REPLACE FUNCTION get_encrypted_content_by_share_code(p_share_code TEXT)
RETURNS TABLE (
    id UUID,
    content_hash TEXT,
    encrypted_data TEXT,
    iv TEXT,
    salt TEXT,
    tag TEXT,
    file_name TEXT,
    file_size BIGINT,
    file_type TEXT,
    created_at TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE,
    encryption_version TEXT,
    view_count INTEGER,
    is_pro_content BOOLEAN,
    pro_expiry_hours INTEGER,
    ip_hash TEXT,
    user_agent_hash TEXT,
    last_accessed_at TIMESTAMP WITH TIME ZONE
) AS $$
DECLARE
    v_content_hash TEXT;
BEGIN
    -- Validate share code format
    IF p_share_code IS NULL OR LENGTH(p_share_code) < 8 OR LENGTH(p_share_code) > 12 THEN
        RAISE EXCEPTION 'Invalid share code format';
    END IF;
    
    -- Get content hash from share code mapping
    SELECT content_hash INTO v_content_hash
    FROM share_code_mappings
    WHERE share_code = p_share_code
    AND expires_at > NOW();
    
    IF v_content_hash IS NULL THEN
        -- Return empty result set if no mapping found
        RETURN;
    END IF;
    
    -- Return encrypted content data using the content hash
    RETURN QUERY
    SELECT * FROM get_encrypted_content_by_hash(v_content_hash);
    
EXCEPTION
    WHEN OTHERS THEN
        -- Return empty result set on error
        RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ========================================
-- 9. CLEANUP FUNCTIONS
-- ========================================

-- Drop existing cleanup functions first to handle signature changes
DROP FUNCTION IF EXISTS cleanup_expired_data() CASCADE;
DROP FUNCTION IF EXISTS cleanup_expired_content() CASCADE;

-- Privacy-first cleanup function (no user tracking)
CREATE OR REPLACE FUNCTION cleanup_expired_data() RETURNS INTEGER AS $$
DECLARE
    v_deleted_drops INTEGER;
    v_deleted_sessions INTEGER;
    v_deleted_rate_limits INTEGER;
    v_deleted_files TEXT[];
BEGIN
    -- Delete expired drops and their items
    DELETE FROM drops WHERE expires_at < NOW();
    GET DIAGNOSTICS v_deleted_drops = ROW_COUNT;
    
    -- Delete expired temporary sessions
    DELETE FROM temp_sessions WHERE expires_at < NOW();
    GET DIAGNOSTICS v_deleted_sessions = ROW_COUNT;
    
    -- Delete expired rate limits
    DELETE FROM rate_limits_private WHERE expires_at < NOW();
    GET DIAGNOSTICS v_deleted_rate_limits = ROW_COUNT;
    
    -- Get file paths for cleanup (for storage cleanup script)
    SELECT ARRAY_AGG(content_data) INTO v_deleted_files
    FROM drop_items 
    WHERE drop_id NOT IN (SELECT id FROM drops);
    
    RETURN v_deleted_drops + v_deleted_sessions + v_deleted_rate_limits;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Backward compatibility function for cleanup_expired_content (for GitHub Actions)
CREATE OR REPLACE FUNCTION cleanup_expired_content() RETURNS TEXT[] AS $$
DECLARE
    v_deleted_files TEXT[];
BEGIN
    -- Get file paths for cleanup
    SELECT ARRAY_AGG(content_data) INTO v_deleted_files
    FROM drop_items 
    WHERE drop_id NOT IN (SELECT id FROM drops)
    AND item_type = 'file'
    AND content_data IS NOT NULL;
    
    -- Call the enhanced cleanup function
    PERFORM cleanup_expired_data();
    
    RETURN COALESCE(v_deleted_files, ARRAY[]::TEXT[]);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ========================================
-- 10. ENABLE ROW LEVEL SECURITY
-- ========================================

-- Enable RLS on all tables
ALTER TABLE drops ENABLE ROW LEVEL SECURITY;
ALTER TABLE drop_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE pro_waitlist ENABLE ROW LEVEL SECURITY;
ALTER TABLE rate_limits_private ENABLE ROW LEVEL SECURITY;
ALTER TABLE temp_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE blocked_ip_hashes ENABLE ROW LEVEL SECURITY;
ALTER TABLE security_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE encrypted_content ENABLE ROW LEVEL SECURITY;
ALTER TABLE share_code_mappings ENABLE ROW LEVEL SECURITY;

-- ========================================
-- 11. ROW LEVEL SECURITY POLICIES
-- ========================================

-- Drop all existing policies to avoid conflicts
DROP POLICY IF EXISTS "Allow public read access to drops" ON drops;
DROP POLICY IF EXISTS "Allow public insert access to drops" ON drops;
DROP POLICY IF EXISTS "Allow public read access to drop_items" ON drop_items;
DROP POLICY IF EXISTS "Allow public insert access to drop_items" ON drop_items;
DROP POLICY IF EXISTS "Privacy-first read access to drops" ON drops;
DROP POLICY IF EXISTS "Privacy-first insert access to drops" ON drops;
DROP POLICY IF EXISTS "Privacy-first read access to drop_items" ON drop_items;
DROP POLICY IF EXISTS "Privacy-first insert access to drop_items" ON drop_items;
-- Drop role-targeted dev policies if they exist
DROP POLICY IF EXISTS "Anon can read drops" ON drops;
DROP POLICY IF EXISTS "Anon can insert drops" ON drops;
DROP POLICY IF EXISTS "Anon can update drops" ON drops;
DROP POLICY IF EXISTS "Auth can read drops" ON drops;
DROP POLICY IF EXISTS "Auth can insert drops" ON drops;
DROP POLICY IF EXISTS "Auth can update drops" ON drops;
DROP POLICY IF EXISTS "Anon can read drop_items" ON drop_items;
DROP POLICY IF EXISTS "Anon can insert drop_items" ON drop_items;
DROP POLICY IF EXISTS "Anon can update drop_items" ON drop_items;
DROP POLICY IF EXISTS "Auth can read drop_items" ON drop_items;
DROP POLICY IF EXISTS "Auth can insert drop_items" ON drop_items;
DROP POLICY IF EXISTS "Auth can update drop_items" ON drop_items;
DROP POLICY IF EXISTS "Allow read access to drops" ON drops;
DROP POLICY IF EXISTS "Allow insert access to drops" ON drops;
DROP POLICY IF EXISTS "Allow update access to drops" ON drops;
DROP POLICY IF EXISTS "Allow read access to drop_items" ON drop_items;
DROP POLICY IF EXISTS "Allow insert access to drop_items" ON drop_items;
DROP POLICY IF EXISTS "Allow update access to drop_items" ON drop_items;
DROP POLICY IF EXISTS "Allow public insert to pro waitlist" ON pro_waitlist;
DROP POLICY IF EXISTS "Allow service role full access to pro waitlist" ON pro_waitlist;
DROP POLICY IF EXISTS "Allow service role full access to security events" ON security_events;
DROP POLICY IF EXISTS "Allow public read access to encrypted content" ON encrypted_content;
DROP POLICY IF EXISTS "Allow public insert access to encrypted content" ON encrypted_content;
DROP POLICY IF EXISTS "Allow public read access to share code mappings" ON share_code_mappings;
DROP POLICY IF EXISTS "Allow public insert access to share code mappings" ON share_code_mappings;

-- Simplified read policy for drops (basic security)
CREATE POLICY "Allow read access to drops" ON drops
    FOR SELECT USING (
        -- Allow reading all drops (RLS still enabled for write paths)
        true
    );

-- Simplified insert policy for drops (basic validation only)
CREATE POLICY "Allow insert access to drops" ON drops
    FOR INSERT WITH CHECK (
        -- Basic validation only - rate limiting handled by RPC functions
        LENGTH(share_code) >= 6 AND
        LENGTH(share_code) <= 20 AND
        share_code ~ '^[A-Za-z0-9]+$'
    );

-- Allow update access to drops (for view count, etc.)
CREATE POLICY "Allow update access to drops" ON drops
    FOR UPDATE USING (
        expires_at > NOW()
    );

-- Simplified read policy for drop_items
CREATE POLICY "Allow read access to drop_items" ON drop_items
    FOR SELECT USING (
        -- Allow reading items if parent drop is not expired
        EXISTS (
            SELECT 1 FROM drops 
            WHERE id = drop_items.drop_id 
            AND expires_at > NOW()
        )
    );

-- Simplified insert policy for drop_items
CREATE POLICY "Allow insert access to drop_items" ON drop_items
    FOR INSERT WITH CHECK (
        -- Allow inserting items if parent drop exists and not expired
        EXISTS (
            SELECT 1 FROM drops 
            WHERE id = drop_items.drop_id 
            AND expires_at > NOW()
        )
    );

-- Allow update access to drop_items
CREATE POLICY "Allow update access to drop_items" ON drop_items
    FOR UPDATE USING (
        -- Allow updating items if parent drop is not expired
        EXISTS (
            SELECT 1 FROM drops 
            WHERE id = drop_items.drop_id 
            AND expires_at > NOW()
        )
    );

-- Explicit role-targeted policies to satisfy PostgREST insert+return behavior
-- anon role
CREATE POLICY "Anon can read drops" ON drops
    FOR SELECT TO anon USING (true);

CREATE POLICY "Anon can insert drops" ON drops
    FOR INSERT TO anon WITH CHECK (
        LENGTH(share_code) >= 6 AND
        LENGTH(share_code) <= 20 AND
        share_code ~ '^[A-Za-z0-9]+'
    );

CREATE POLICY "Anon can update drops" ON drops
    FOR UPDATE TO anon USING (true);

CREATE POLICY "Anon can read drop_items" ON drop_items
    FOR SELECT TO anon USING (
        EXISTS (SELECT 1 FROM drops WHERE id = drop_items.drop_id)
    );

CREATE POLICY "Anon can insert drop_items" ON drop_items
    FOR INSERT TO anon WITH CHECK (
        EXISTS (SELECT 1 FROM drops WHERE id = drop_items.drop_id)
    );

CREATE POLICY "Anon can update drop_items" ON drop_items
    FOR UPDATE TO anon USING (
        EXISTS (SELECT 1 FROM drops WHERE id = drop_items.drop_id)
    );

-- authenticated role
CREATE POLICY "Auth can read drops" ON drops
    FOR SELECT TO authenticated USING (true);

CREATE POLICY "Auth can insert drops" ON drops
    FOR INSERT TO authenticated WITH CHECK (
        LENGTH(share_code) >= 6 AND
        LENGTH(share_code) <= 20 AND
        share_code ~ '^[A-Za-z0-9]+'
    );

CREATE POLICY "Auth can update drops" ON drops
    FOR UPDATE TO authenticated USING (true);

CREATE POLICY "Auth can read drop_items" ON drop_items
    FOR SELECT TO authenticated USING (
        EXISTS (SELECT 1 FROM drops WHERE id = drop_items.drop_id)
    );

CREATE POLICY "Auth can insert drop_items" ON drop_items
    FOR INSERT TO authenticated WITH CHECK (
        EXISTS (SELECT 1 FROM drops WHERE id = drop_items.drop_id)
    );

CREATE POLICY "Auth can update drop_items" ON drop_items
    FOR UPDATE TO authenticated USING (
        EXISTS (SELECT 1 FROM drops WHERE id = drop_items.drop_id)
    );

-- Pro waitlist policies
CREATE POLICY "Allow public insert to pro waitlist" ON pro_waitlist
    FOR INSERT WITH CHECK (true);

CREATE POLICY "Allow service role full access to pro waitlist" ON pro_waitlist
    FOR ALL USING (auth.role() = 'service_role');

-- Security events policies
CREATE POLICY "Allow service role full access to security events" ON security_events
    FOR ALL USING (auth.role() = 'service_role');

-- Encrypted content policies
CREATE POLICY "Allow public read access to encrypted content" ON encrypted_content
    FOR SELECT USING (expires_at > NOW());

CREATE POLICY "Allow public insert access to encrypted content" ON encrypted_content
    FOR INSERT WITH CHECK (true);

-- Share code mappings policies
CREATE POLICY "Allow public read access to share code mappings" ON share_code_mappings
    FOR SELECT USING (expires_at > NOW());

CREATE POLICY "Allow public insert access to share code mappings" ON share_code_mappings
    FOR INSERT WITH CHECK (true);

-- ========================================
-- 12. STORAGE BUCKET AND POLICIES
-- ========================================

-- Create storage bucket if it doesn't exist
INSERT INTO storage.buckets (id, name, public) 
VALUES ('shared-files', 'shared-files', false)
ON CONFLICT (id) DO NOTHING;

-- Drop all existing storage policies to avoid conflicts
DROP POLICY IF EXISTS "Allow public uploads" ON storage.objects;
DROP POLICY IF EXISTS "Allow public downloads" ON storage.objects;
DROP POLICY IF EXISTS "Privacy-first uploads" ON storage.objects;
DROP POLICY IF EXISTS "Privacy-first downloads" ON storage.objects;
DROP POLICY IF EXISTS "Allow uploads to shared files" ON storage.objects;
DROP POLICY IF EXISTS "Allow downloads from shared files" ON storage.objects;

-- Create new storage policies (simplified)
CREATE POLICY "Allow uploads to shared files" ON storage.objects
    FOR INSERT WITH CHECK (
        bucket_id = 'shared-files'
    );

CREATE POLICY "Allow downloads from shared files" ON storage.objects
    FOR SELECT USING (
        bucket_id = 'shared-files'
    );

-- ========================================
-- 13. PERMISSIONS AND GRANTS
-- ========================================

-- Grant function permissions to anon users
GRANT EXECUTE ON FUNCTION hash_ip(INET) TO anon;
GRANT EXECUTE ON FUNCTION is_ip_hash_blocked(INET) TO anon;
GRANT EXECUTE ON FUNCTION check_rate_limit_private(INET, TEXT, INTEGER, INTEGER) TO anon;
GRANT EXECUTE ON FUNCTION validate_content_private(TEXT, TEXT, INTEGER, TEXT) TO anon;
GRANT EXECUTE ON FUNCTION generate_temp_session(UUID) TO anon;
GRANT EXECUTE ON FUNCTION validate_temp_session(TEXT, UUID) TO anon;
GRANT EXECUTE ON FUNCTION log_security_event(TEXT, TEXT, JSONB) TO anon;
GRANT EXECUTE ON FUNCTION secure_create_drop(TEXT, INET) TO anon;
GRANT EXECUTE ON FUNCTION secure_add_drop_item(UUID, TEXT, TEXT, TEXT, INET, TEXT, INTEGER, TEXT) TO anon;
GRANT EXECUTE ON FUNCTION secure_update_drop_item(UUID, TEXT, TEXT, INET) TO anon;
GRANT EXECUTE ON FUNCTION update_drop_item_with_code(UUID, TEXT, TEXT) TO anon;
GRANT EXECUTE ON FUNCTION store_encrypted_content(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, BIGINT, TEXT, BOOLEAN, INTEGER) TO anon;
GRANT EXECUTE ON FUNCTION store_encrypted_text_content(TEXT, TEXT, TEXT, TEXT, TEXT) TO anon;
GRANT EXECUTE ON FUNCTION store_encrypted_file_content(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, BIGINT, TEXT) TO anon;
GRANT EXECUTE ON FUNCTION get_encrypted_content_by_hash(TEXT) TO anon;
GRANT EXECUTE ON FUNCTION get_encrypted_content_by_share_code(TEXT) TO anon;

-- Add direct table grants for anon/authenticated to avoid privilege issues under RLS
GRANT SELECT, INSERT, UPDATE ON drops TO anon;
GRANT SELECT, INSERT, UPDATE ON drops TO authenticated;
GRANT SELECT, INSERT, UPDATE ON drop_items TO anon;
GRANT SELECT, INSERT, UPDATE ON drop_items TO authenticated;

-- Grant function permissions to service role
GRANT EXECUTE ON FUNCTION cleanup_expired_data() TO service_role;
GRANT EXECUTE ON FUNCTION cleanup_expired_content() TO service_role;

-- Grant table permissions to service role
GRANT SELECT, INSERT, DELETE ON security_events TO service_role;
GRANT ALL ON pro_waitlist TO service_role;

-- Disable direct table access to security infrastructure tables
-- These tables should only be accessed through RPC functions, not directly
REVOKE ALL ON rate_limits_private FROM anon;
REVOKE ALL ON rate_limits_private FROM authenticated;
REVOKE ALL ON rate_limits_private FROM service_role;

REVOKE ALL ON blocked_ip_hashes FROM anon;
REVOKE ALL ON blocked_ip_hashes FROM authenticated;
REVOKE ALL ON blocked_ip_hashes FROM service_role;

REVOKE ALL ON temp_sessions FROM anon;
REVOKE ALL ON temp_sessions FROM authenticated;
REVOKE ALL ON temp_sessions FROM service_role;

REVOKE ALL ON encrypted_content FROM anon;
REVOKE ALL ON encrypted_content FROM authenticated;
REVOKE ALL ON encrypted_content FROM service_role;

-- Grant USAGE on schema to allow function calls
GRANT USAGE ON SCHEMA public TO anon;
GRANT USAGE ON SCHEMA public TO authenticated;
GRANT USAGE ON SCHEMA public TO service_role;

-- ========================================
-- 14. INITIAL DATA AND TEST RECORDS
-- ========================================

-- Insert initial test data for rate limiting
INSERT INTO rate_limits_private (ip_hash, action_type, expires_at, request_count)
VALUES 
    (hash_ip('127.0.0.1'::INET), 'test', NOW() + INTERVAL '1 hour', 1)
ON CONFLICT DO NOTHING;

-- ========================================
-- 15. COMPLETION VERIFICATION
-- ========================================

-- Verify all essential tables exist
SELECT 'DATABASE SETUP VERIFICATION:' as verification_type;

SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'drops') THEN '✅ drops'
        ELSE '❌ drops MISSING'
    END as table_status
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'drop_items') THEN '✅ drop_items'
        ELSE '❌ drop_items MISSING'
    END
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'pro_waitlist') THEN '✅ pro_waitlist (PRESERVED)'
        ELSE '❌ pro_waitlist MISSING'
    END
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'rate_limits_private') THEN '✅ rate_limits_private'
        ELSE '❌ rate_limits_private MISSING'
    END
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'temp_sessions') THEN '✅ temp_sessions'
        ELSE '❌ temp_sessions MISSING'
    END
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'blocked_ip_hashes') THEN '✅ blocked_ip_hashes'
        ELSE '❌ blocked_ip_hashes MISSING'
    END
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'security_events') THEN '✅ security_events'
        ELSE '❌ security_events MISSING'
    END
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'encrypted_content') THEN '✅ encrypted_content'
        ELSE '❌ encrypted_content MISSING'
    END
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'share_code_mappings') THEN '✅ share_code_mappings'
        ELSE '❌ share_code_mappings MISSING'
    END;

-- Verify essential functions exist
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'secure_create_drop') THEN '✅ secure_create_drop'
        ELSE '❌ secure_create_drop MISSING'
    END as function_status
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'secure_add_drop_item') THEN '✅ secure_add_drop_item'
        ELSE '❌ secure_add_drop_item MISSING'
    END
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'update_drop_item_with_code') THEN '✅ update_drop_item_with_code'
        ELSE '❌ update_drop_item_with_code MISSING'
    END
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'cleanup_expired_content') THEN '✅ cleanup_expired_content'
        ELSE '❌ cleanup_expired_content MISSING'
    END
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'log_security_event') THEN '✅ log_security_event'
        ELSE '❌ log_security_event MISSING'
    END
UNION ALL
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'store_encrypted_content') THEN '✅ store_encrypted_content'
        ELSE '❌ store_encrypted_content MISSING'
    END;

-- Verify storage bucket exists
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM storage.buckets WHERE id = 'shared-files') THEN '✅ shared-files bucket'
        ELSE '❌ shared-files bucket MISSING'
    END as bucket_status;

-- Final success message
SELECT 'COMPLETE DATABASE SETUP SUCCESSFUL! ✅' as setup_status;
SELECT 'All tables, functions, storage buckets, and policies have been created.' as completion_message;
SELECT 'The pro_waitlist table has been preserved with existing users.' as preservation_note;
SELECT 'New developers can now continue working on the project.' as developer_note;