/**
 * Provisioning Manager for VC-SYS CLI Supabase integration
 * Implements 7-step workflow with rollback capabilities
 */
import { SupabaseManagementClient, SupabaseManagementError } from './management-client';
import { Logger } from '../core/logger';
import { ConfigManager } from '../core/config-manager';
import { ProvisioningError, ErrorCategory } from '../../types/errors';
import { 
  ProvisioningConfig, 
  ProvisioningResult, 
  ProvisioningStep,
  SupabaseProject,
  CreateProjectRequest 
} from '../../types/provisioning';
import ora from 'ora';
import chalk from 'chalk';
import fs from 'fs/promises';
import path from 'path';

export class ProvisioningManager {
  private client: SupabaseManagementClient;
  private logger: Logger;
  private config: ConfigManager;
  private steps: ProvisioningStep[] = [];

  constructor() {
    this.client = new SupabaseManagementClient();
    this.logger = new Logger('ProvisioningManager');
    this.config = new ConfigManager();
  }

  /**
   * Complete project provisioning workflow
   */
  async provisionProject(config: ProvisioningConfig): Promise<ProvisioningResult> {
    this.logger.info('Starting project provisioning', { projectName: config.projectName });
    
    // Verify authentication before starting
    try {
      const isAuthenticated = await this.client.isAuthenticated();
      if (!isAuthenticated) {
        throw new ProvisioningError(
          'Not authenticated with Supabase. Please run: vcsys auth login',
          ErrorCategory.AUTHENTICATION
        );
      }
    } catch (error) {
      if (error instanceof SupabaseManagementError && error.requiresReauth) {
        throw new ProvisioningError(
          'Authentication required. Please run: vcsys auth login',
          ErrorCategory.AUTHENTICATION
        );
      }
      throw error;
    }
    
    // Initialize steps
    this.initializeSteps();
    
    let project: SupabaseProject | null = null;
    let apiKeys: any = null;
    
    try {
      // Step 1: Validate configuration
      await this.executeStep('validate-config', async () => {
        await this.validateConfiguration(config);
      });

      // Step 2: Create Supabase project
      await this.executeStep('create-project', async () => {
        project = await this.createProject(config);
        return { projectId: project.id, projectRef: project.ref };
      });

      if (!project) {
        throw new ProvisioningError('Project creation failed', ErrorCategory.PROVISIONING);
      }

      // Step 3: Wait for project to be ready
      await this.executeStep('wait-ready', async () => {
        if (!project) throw new ProvisioningError('Project not created', ErrorCategory.PROVISIONING);
        await this.waitForProjectReady(project.ref);
        return { status: 'ready' };
      });

      // Step 4: Deploy database schema
      await this.executeStep('deploy-schema', async () => {
        if (!project) throw new ProvisioningError('Project not created', ErrorCategory.PROVISIONING);
        if (config.schemaPath) {
          await this.deploySchema(project.ref, config.schemaPath);
          return { schemaDeployed: true };
        }
        return { schemaDeployed: false };
      });

      // Step 5: Configure RLS
      await this.executeStep('configure-rls', async () => {
        if (!project) throw new ProvisioningError('Project not created', ErrorCategory.PROVISIONING);
        if (config.enableRLS) {
          await this.configureRLS(project.ref);
          return { rlsEnabled: true };
        }
        return { rlsEnabled: false };
      });

      // Step 6: Retrieve API keys
      await this.executeStep('retrieve-keys', async () => {
        if (!project) throw new ProvisioningError('Project not created', ErrorCategory.PROVISIONING);
        apiKeys = await this.retrieveApiKeys(project.ref);
        return { keysRetrieved: true };
      });

      // Step 7: Generate environment files
      await this.executeStep('generate-env', async () => {
        if (!project) throw new ProvisioningError('Project not created', ErrorCategory.PROVISIONING);
        const envFile = await this.generateEnvironmentFile(project, apiKeys, config.outputDir);
        return { envFile };
      });

      const result: ProvisioningResult = {
        project,
        apiKeys,
        databaseUrl: this.buildDatabaseUrl(project, config.databasePassword),
        envFile: path.join(config.outputDir, '.env.local'),
        success: true,
        steps: this.steps
      };

      this.logger.info('Project provisioning completed successfully', {
        projectName: config.projectName,
        projectRef: project.ref
      });

      return result;

    } catch (error) {
      this.logger.error('Project provisioning failed', error);
      
      // Handle authentication errors specifically
      if (error instanceof SupabaseManagementError && error.requiresReauth) {
        throw new ProvisioningError(
          'Authentication expired during provisioning. Please run: vcsys auth login',
          ErrorCategory.AUTHENTICATION
        );
      }
      
      // Attempt cleanup if project was created
      if (project) {
        this.logger.info('Attempting cleanup of partially created resources');
        // Note: Supabase doesn't support project deletion via API in free tier
        // This would need to be done manually or through the dashboard
      }

      // Convert SupabaseManagementError to ProvisioningError
      if (error instanceof SupabaseManagementError) {
        throw new ProvisioningError(
          error.message,
          error.status === 401 ? ErrorCategory.AUTHENTICATION : 
          error.status === 403 ? ErrorCategory.AUTHORIZATION : 
          error.status === 429 ? ErrorCategory.API :
          ErrorCategory.PROVISIONING
        );
      }

      throw error;
    }
  }

  /**
   * Initialize provisioning steps
   */
  private initializeSteps(): void {
    this.steps = [
      { name: 'validate-config', status: 'pending' },
      { name: 'create-project', status: 'pending' },
      { name: 'wait-ready', status: 'pending' },
      { name: 'deploy-schema', status: 'pending' },
      { name: 'configure-rls', status: 'pending' },
      { name: 'retrieve-keys', status: 'pending' },
      { name: 'generate-env', status: 'pending' }
    ];
  }

  /**
   * Execute a single provisioning step with progress tracking
   */
  private async executeStep(stepName: string, operation: () => Promise<any>): Promise<void> {
    const step = this.steps.find(s => s.name === stepName);
    if (!step) {
      throw new ProvisioningError(`Step ${stepName} not found`, ErrorCategory.INTERNAL);
    }

    const spinner = ora(chalk.blue(this.getStepDescription(stepName))).start();
    step.status = 'running';
    const startTime = Date.now();

    try {
      const result = await operation();
      const duration = Date.now() - startTime;
      
      step.status = 'completed';
      step.duration = duration;
      step.details = result;
      
      spinner.succeed(chalk.green(`${this.getStepDescription(stepName)} (${duration}ms)`));
      this.logger.info(`Step ${stepName} completed`, { duration, result });

    } catch (error) {
      const duration = Date.now() - startTime;
      step.status = 'failed';
      step.duration = duration;
      step.error = error instanceof Error ? error.message : String(error);
      
      spinner.fail(chalk.red(`${this.getStepDescription(stepName)} failed`));
      this.logger.error(`Step ${stepName} failed`, { duration, error });
      
      throw error;
    }
  }

  /**
   * Get human-readable description for each step
   */
  private getStepDescription(stepName: string): string {
    const descriptions: Record<string, string> = {
      'validate-config': 'Validating configuration',
      'create-project': 'Creating Supabase project',
      'wait-ready': 'Waiting for project to be ready',
      'deploy-schema': 'Deploying database schema',
      'configure-rls': 'Configuring Row Level Security',
      'retrieve-keys': 'Retrieving API keys',
      'generate-env': 'Generating environment files'
    };
    return descriptions[stepName] || stepName;
  }

  /**
   * Validate provisioning configuration
   */
  private async validateConfiguration(config: ProvisioningConfig): Promise<void> {
    // Validate project name
    if (!config.projectName || config.projectName.length < 3) {
      throw new ProvisioningError('Project name must be at least 3 characters', ErrorCategory.VALIDATION);
    }

    // Validate organization ID
    const orgs = await this.client.listOrganizations();
    const orgExists = orgs.some(org => org.id === config.organizationId);
    if (!orgExists) {
      throw new ProvisioningError('Invalid organization ID', ErrorCategory.VALIDATION);
    }

    // Validate region
    const validRegions = ['us-east-1', 'us-west-1', 'eu-west-1', 'ap-southeast-1', 'ap-northeast-1'];
    if (!validRegions.includes(config.region)) {
      throw new ProvisioningError(`Invalid region. Must be one of: ${validRegions.join(', ')}`, ErrorCategory.VALIDATION);
    }

    // Validate password strength
    if (config.databasePassword.length < 12) {
      throw new ProvisioningError('Database password must be at least 12 characters', ErrorCategory.VALIDATION);
    }

    // Validate schema file if provided
    if (config.schemaPath) {
      try {
        await fs.access(config.schemaPath);
      } catch {
        throw new ProvisioningError(`Schema file not found: ${config.schemaPath}`, ErrorCategory.VALIDATION);
      }
    }

    // Validate output directory
    try {
      await fs.mkdir(config.outputDir, { recursive: true });
    } catch (error) {
      throw new ProvisioningError(`Cannot create output directory: ${config.outputDir}`, ErrorCategory.FILE_SYSTEM);
    }
  }

  /**
   * Create Supabase project
   */
  private async createProject(config: ProvisioningConfig): Promise<SupabaseProject> {
    const projectData: CreateProjectRequest = {
      organization_id: config.organizationId,
      name: config.projectName,
      region: config.region,
      db_pass: config.databasePassword,
      kps_enabled: true,
      plan: 'free'
    };

    const result = await this.client.createProject(projectData);
    
    // Debug logging to understand the actual response structure
    this.logger.info('Create project response', { 
      id: result.id, 
      ref: result.ref,
      fullResult: result 
    });
    
    // If ref is missing, use id as ref (common Supabase pattern)
    if (!result.ref && result.id) {
      result.ref = result.id;
    }
    
    return result;
  }

  /**
   * Wait for project to be ready (can take 2-5 minutes)
   */
  private async waitForProjectReady(projectRef: string, maxWaitTime: number = 300000): Promise<void> {
    const startTime = Date.now();
    const pollInterval = 10000; // 10 seconds

    while (Date.now() - startTime < maxWaitTime) {
      try {
        const project = await this.client.getProject(projectRef);
        
        if (project.status === 'ACTIVE_HEALTHY') {
          this.logger.info('Project is ready', { projectRef, status: project.status });
          return;
        }

        this.logger.debug('Project still initializing', { projectRef, status: project.status });
        await new Promise(resolve => setTimeout(resolve, pollInterval));

      } catch (error) {
        this.logger.debug('Project not yet accessible', { projectRef, error });
        await new Promise(resolve => setTimeout(resolve, pollInterval));
      }
    }

    throw new ProvisioningError('Project did not become ready within timeout period', ErrorCategory.TIMEOUT);
  }

  /**
   * Deploy database schema
   */
  private async deploySchema(projectRef: string, schemaPath: string): Promise<void> {
    try {
      const schemaContent = await fs.readFile(schemaPath, 'utf-8');
      
      // Normalize line endings to Unix format (\n) to prevent PostgreSQL parsing issues
      const normalizedContent = schemaContent.replace(/\r\n/g, '\n').replace(/\r/g, '\n');
      
      // Split schema into individual statements using proper SQL parsing that respects dollar-quoted strings
      const statements = this.parseSQL(normalizedContent);

      this.logger.info(`Deploying ${statements.length} SQL statements`, { projectRef });

      for (let i = 0; i < statements.length; i++) {
        const statement = statements[i];
        this.logger.debug(`Executing statement ${i + 1}/${statements.length}`, { 
          projectRef, 
          preview: statement.substring(0, 100) + (statement.length > 100 ? '...' : '')
        });
        
        try {
          await this.client.executeSQL(projectRef, statement);
        } catch (error) {
          this.logger.error(`Statement ${i + 1} failed`, { 
            projectRef, 
            statement: statement.substring(0, 200),
            error 
          });
          throw error;
        }
      }

      this.logger.info('Schema deployed successfully', { projectRef, statements: statements.length });

    } catch (error) {
      throw new ProvisioningError(`Schema deployment failed: ${error}`, ErrorCategory.DATABASE);
    }
  }

  /**
   * Parse SQL content into individual statements, respecting dollar-quoted strings
   * This prevents breaking PostgreSQL functions and procedures that use $$ quoting
   */
  private parseSQL(content: string): string[] {
    const statements: string[] = [];
    let currentStatement = '';
    let inDollarQuote = false;
    let dollarTag = '';
    let inSingleQuote = false;
    let inDoubleQuote = false;
    let inComment = false;
    let inMultiLineComment = false;
    
    for (let i = 0; i < content.length; i++) {
      const char = content[i];
      const nextChar = content[i + 1];
      const prevChar = content[i - 1];
      
      // Handle multi-line comments /* */
      if (!inSingleQuote && !inDoubleQuote && !inDollarQuote) {
        if (char === '/' && nextChar === '*') {
          inMultiLineComment = true;
          currentStatement += char;
          continue;
        }
        if (inMultiLineComment && char === '*' && nextChar === '/') {
          inMultiLineComment = false;
          currentStatement += char;
          continue;
        }
      }
      
      // Handle single-line comments --
      if (!inSingleQuote && !inDoubleQuote && !inDollarQuote && !inMultiLineComment) {
        if (char === '-' && nextChar === '-') {
          inComment = true;
          currentStatement += char;
          continue;
        }
        if (inComment && char === '\n') {
          inComment = false;
        }
      }
      
      // Skip processing if we're in a comment
      if (inComment || inMultiLineComment) {
        currentStatement += char;
        continue;
      }
      
      // Handle dollar-quoted strings (PostgreSQL functions/procedures)
      if (!inSingleQuote && !inDoubleQuote && char === '$') {
        if (!inDollarQuote) {
          // Starting dollar quote - find the tag
          const dollarMatch = content.substring(i).match(/^\$([^$]*)\$/);
          if (dollarMatch) {
            dollarTag = dollarMatch[1];
            inDollarQuote = true;
            currentStatement += char;
            continue;
          }
        } else {
          // Check if this ends the dollar quote
          const endPattern = `$${dollarTag}$`;
          if (content.substring(i, i + endPattern.length) === endPattern) {
            inDollarQuote = false;
            dollarTag = '';
            currentStatement += endPattern;
            i += endPattern.length - 1;
            continue;
          }
        }
      }
      
      // Handle single quotes (ignore if in dollar quote)
      if (!inDollarQuote && !inDoubleQuote && char === "'" && prevChar !== '\\') {
        inSingleQuote = !inSingleQuote;
      }
      
      // Handle double quotes (ignore if in dollar quote)
      if (!inDollarQuote && !inSingleQuote && char === '"' && prevChar !== '\\') {
        inDoubleQuote = !inDoubleQuote;
      }
      
      // Handle statement termination
      if (!inSingleQuote && !inDoubleQuote && !inDollarQuote && char === ';') {
        currentStatement += char;
        const trimmed = currentStatement.trim();
        if (trimmed && !trimmed.match(/^--/)) { // Skip comment-only statements
          statements.push(trimmed);
        }
        currentStatement = '';
        continue;
      }
      
      currentStatement += char;
    }
    
    // Add any remaining statement
    const trimmed = currentStatement.trim();
    if (trimmed && !trimmed.match(/^--/)) {
      statements.push(trimmed);
    }
    
    return statements.filter(stmt => stmt.length > 0);
  }

  /**
   * Configure Row Level Security
   */
  private async configureRLS(projectRef: string): Promise<void> {
    const rlsStatements = [
      'ALTER TABLE IF EXISTS public.projects ENABLE ROW LEVEL SECURITY;',
      'ALTER TABLE IF EXISTS public.chat_messages ENABLE ROW LEVEL SECURITY;',
      `CREATE POLICY IF NOT EXISTS "Users can only see their own projects" ON public.projects
       FOR ALL USING (user_id = auth.uid());`,
      `CREATE POLICY IF NOT EXISTS "Users can only see their own messages" ON public.chat_messages
       FOR ALL USING (project_id IN (SELECT id FROM public.projects WHERE user_id = auth.uid()));`
    ];

    for (const statement of rlsStatements) {
      try {
        await this.client.executeSQL(projectRef, statement);
      } catch (error) {
        this.logger.warn('RLS statement failed, continuing', { statement, error });
      }
    }

    this.logger.info('RLS configured', { projectRef });
  }

  /**
   * Retrieve API keys with retry logic
   */
  private async retrieveApiKeys(projectRef: string): Promise<{ anon: string; serviceRole: string }> {
    const maxRetries = 5;
    const retryDelay = 2000;

    for (let attempt = 1; attempt <= maxRetries; attempt++) {
      try {
        const keys = await this.client.getProjectApiKeys(projectRef);
        this.logger.info('API keys retrieved successfully', { projectRef, attempt });
        return {
          anon: keys.anon,
          serviceRole: keys.service_role
        };
      } catch (error) {
        if (attempt === maxRetries) {
          throw new ProvisioningError(`Failed to retrieve API keys after ${maxRetries} attempts`, ErrorCategory.API);
        }
        
        this.logger.warn('API key retrieval failed, retrying', { projectRef, attempt, error });
        await new Promise(resolve => setTimeout(resolve, retryDelay * attempt));
      }
    }

    throw new ProvisioningError('Unexpected error in API key retrieval', ErrorCategory.INTERNAL);
  }

  /**
   * Generate environment file
   */
  private async generateEnvironmentFile(
    project: SupabaseProject,
    apiKeys: { anon: string; serviceRole: string },
    outputDir: string
  ): Promise<string> {
    const envContent = `# Supabase Configuration - Generated by VC-SYS CLI
# Project: ${project.name} (${project.ref})
# Generated: ${new Date().toISOString()}

NEXT_PUBLIC_SUPABASE_URL=https://${project.ref}.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=${apiKeys.anon}
SUPABASE_SERVICE_ROLE_KEY=${apiKeys.serviceRole}
SUPABASE_PROJECT_REF=${project.ref}
SUPABASE_PROJECT_ID=${project.id}

# Database Configuration
DATABASE_URL=postgresql://postgres:[YOUR_PASSWORD]@db.${project.ref}.supabase.co:5432/postgres
DIRECT_URL=postgresql://postgres:[YOUR_PASSWORD]@db.${project.ref}.supabase.co:5432/postgres

# Development
NODE_ENV=development
`;

    const envFilePath = path.join(outputDir, '.env.local');
    await fs.writeFile(envFilePath, envContent, 'utf-8');
    
    this.logger.info('Environment file generated', { envFilePath });
    return envFilePath;
  }

  /**
   * Build database URL
   */
  private buildDatabaseUrl(project: SupabaseProject, password: string): string {
    return `postgresql://postgres:${password}@db.${project.ref}.supabase.co:5432/postgres`;
  }

  /**
   * Get provisioning status
   */
  getProvisioningStatus(): ProvisioningStep[] {
    return [...this.steps];
  }
}