#!/bin/bash
set -e

echo "🚀 Initializing DataShield PostgreSQL database..."

# Create additional extensions
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    -- Enable required extensions
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
    CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
    CREATE EXTENSION IF NOT EXISTS "pg_trgm";

    -- Create optimized schema for DataShield
    CREATE SCHEMA IF NOT EXISTS datashield;
    SET search_path TO datashield, public;

    -- Create users table
    CREATE TABLE IF NOT EXISTS users (
        id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
        email VARCHAR(255) UNIQUE NOT NULL,
        domain VARCHAR(255) NOT NULL,
        is_trusted BOOLEAN DEFAULT FALSE,
        is_active BOOLEAN DEFAULT TRUE,
        last_active TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    );

    -- Create files table
    CREATE TABLE IF NOT EXISTS files (
        id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
        owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        name VARCHAR(500) NOT NULL,
        mime_type VARCHAR(100) NOT NULL,
        size_bytes BIGINT DEFAULT 0,
        trashed BOOLEAN DEFAULT FALSE,
        shared BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    );

    -- Create file_sharing table
    CREATE TABLE IF NOT EXISTS file_sharing (
        id SERIAL PRIMARY KEY,
        file_id UUID NOT NULL REFERENCES files(id) ON DELETE CASCADE,
        shared_with UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        permission_level VARCHAR(20) NOT NULL CHECK (permission_level IN ('viewer', 'commenter', 'editor', 'owner')),
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        UNIQUE(file_id, shared_with)
    );

    -- Create indexes for performance
    CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
    CREATE INDEX IF NOT EXISTS idx_users_domain ON users(domain);
    CREATE INDEX IF NOT EXISTS idx_users_last_active ON users(last_active);

    CREATE INDEX IF NOT EXISTS idx_files_owner_id ON files(owner_id);
    CREATE INDEX IF NOT EXISTS idx_files_trashed ON files(trashed);
    CREATE INDEX IF NOT EXISTS idx_files_shared ON files(shared);
    CREATE INDEX IF NOT EXISTS idx_files_created_at ON files(created_at);
    CREATE INDEX IF NOT EXISTS idx_files_owner_trashed ON files(owner_id, trashed);

    CREATE INDEX IF NOT EXISTS idx_file_sharing_file_id ON file_sharing(file_id);
    CREATE INDEX IF NOT EXISTS idx_file_sharing_shared_with ON file_sharing(shared_with);
    CREATE INDEX IF NOT EXISTS idx_file_sharing_permission ON file_sharing(permission_level);

    -- Create composite indexes for common queries
    CREATE INDEX IF NOT EXISTS idx_files_owner_shared_trashed ON files(owner_id, shared, trashed);
    CREATE INDEX IF NOT EXISTS idx_files_created_trashed ON files(created_at, trashed);

    -- Create updated_at trigger function
    CREATE OR REPLACE FUNCTION update_updated_at_column()
    RETURNS TRIGGER AS \$\$
    BEGIN
        NEW.updated_at = NOW();
        RETURN NEW;
    END;
    \$\$ language 'plpgsql';

    -- Add triggers for updated_at
    CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

    CREATE TRIGGER update_files_updated_at BEFORE UPDATE ON files
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

    -- Create materialized view for dashboard analytics
    CREATE MATERIALIZED VIEW IF NOT EXISTS user_analytics AS
    SELECT
        u.domain,
        COUNT(DISTINCT u.id) as user_count,
        COUNT(f.id) as file_count,
        COALESCE(SUM(f.size_bytes), 0) as total_storage,
        COUNT(DISTINCT CASE WHEN f.shared = TRUE THEN f.id END) as shared_files,
        COUNT(DISTINCT fs.id) as total_shares,
        ROUND(COUNT(f.id) * 1.0 / NULLIF(COUNT(DISTINCT u.id), 0), 2) as avg_files_per_user,
        ROUND(COUNT(DISTINCT CASE WHEN f.shared = TRUE THEN f.id END) * 100.0 / NULLIF(COUNT(f.id), 0), 2) as sharing_percentage,
        MAX(u.last_active) as last_activity
    FROM users u
    LEFT JOIN files f ON u.id = f.owner_id AND f.trashed = FALSE
    LEFT JOIN file_sharing fs ON f.id = fs.file_id
    WHERE u.is_active = TRUE
    GROUP BY u.domain;

    -- Create unique index for concurrent refresh
    CREATE UNIQUE INDEX IF NOT EXISTS idx_user_analytics_domain
        ON user_analytics(domain);

    -- Grant permissions
    GRANT USAGE ON SCHEMA datashield TO $POSTGRES_USER;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA datashield TO $POSTGRES_USER;
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA datashield TO $POSTGRES_USER;
    GRANT SELECT ON ALL VIEWS IN SCHEMA datashield TO $POSTGRES_USER;

    -- Set up proper permissions for future tables
    ALTER DEFAULT PRIVILEGES IN SCHEMA datashield GRANT ALL ON TABLES TO $POSTGRES_USER;
    ALTER DEFAULT PRIVILEGES IN SCHEMA datashield GRANT ALL ON SEQUENCES TO $POSTGRES_USER;

    -- Create refresh function for materialized view
    CREATE OR REPLACE FUNCTION refresh_user_analytics()
    RETURNS void AS \$\$
    BEGIN
        REFRESH MATERIALIZED VIEW CONCURRENTLY user_analytics;
    END;
    \$\$ LANGUAGE plpgsql;

    -- Create stored procedures for common operations
    CREATE OR REPLACE FUNCTION get_user_files(
        p_user_id UUID,
        p_include_trashed BOOLEAN DEFAULT FALSE,
        p_limit INTEGER DEFAULT 50,
        p_offset INTEGER DEFAULT 0
    )
    RETURNS TABLE (
        id UUID,
        name VARCHAR(500),
        mime_type VARCHAR(100),
        size_bytes BIGINT,
        shared BOOLEAN,
        trashed BOOLEAN,
        created_at TIMESTAMP WITH TIME ZONE
    ) AS \$\$
    BEGIN
        RETURN QUERY
        SELECT
            f.id,
            f.name,
            f.mime_type,
            f.size_bytes,
            f.shared,
            f.trashed,
            f.created_at
        FROM files f
        WHERE f.owner_id = p_user_id
        AND (p_include_trashed = TRUE OR f.trashed = FALSE)
        ORDER BY f.created_at DESC
        LIMIT p_limit
        OFFSET p_offset;
    END;
    \$\$ LANGUAGE plpgsql;

    echo "✅ DataShield database initialization completed successfully!"
EOSQL

echo "🎉 Database ready for DataShield!"