-- Database initialization script for Virtual Board Member AI System

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

-- Create custom types
DO $$ BEGIN
    CREATE TYPE user_role AS ENUM (
        'board_member',
        'executive', 
        'executive_assistant',
        'analyst',
        'auditor',
        'admin'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE document_type AS ENUM (
        'report',
        'presentation',
        'minutes',
        'financial',
        'legal',
        'other'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE commitment_status AS ENUM (
        'pending',
        'in_progress',
        'completed',
        'overdue',
        'cancelled'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE commitment_priority AS ENUM (
        'low',
        'medium',
        'high',
        'critical'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE tenant_status AS ENUM (
        'active',
        'inactive',
        'suspended',
        'pending'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE tenant_tier AS ENUM (
        'basic',
        'professional',
        'enterprise'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE audit_event_type AS ENUM (
        'login',
        'logout',
        'document_upload',
        'document_download',
        'query_executed',
        'commitment_created',
        'commitment_updated',
        'user_created',
        'user_updated',
        'system_event'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Grant permissions (tables will be created by SQLAlchemy)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO vbm_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO vbm_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO vbm_user;