/**
 * Schema Manager for VC-SYS CLI Supabase integration
 * Manages database schema templates and deployment
 */
import { Logger } from '../core/logger';
import { ProvisioningError, ErrorCategory } from '../../types/errors';
import { SchemaTemplate, SchemaValidationResult } from '../../types/provisioning';
import fs from 'fs/promises';
import path from 'path';

export class SchemaManager {
  private logger: Logger;
  private templatesDir: string;

  constructor(templatesDir: string = 'src/templates/schemas') {
    this.logger = new Logger('SchemaManager');
    this.templatesDir = templatesDir;
  }

  /**
   * List available schema templates
   */
  async listTemplates(): Promise<SchemaTemplate[]> {
    try {
      const templates: SchemaTemplate[] = [];
      
      // Built-in templates
      templates.push(
        {
          name: 'saas-starter',
          description: 'Complete SaaS application schema with users, projects, subscriptions',
          path: path.join(this.templatesDir, 'saas-starter.sql'),
          tables: ['profiles', 'projects', 'subscriptions', 'usage_metrics'],
          features: ['Auth', 'Multi-tenancy', 'Billing', 'Usage tracking']
        },
        {
          name: 'minimal',
          description: 'Minimal schema with just user profiles',
          path: path.join(this.templatesDir, 'minimal.sql'),
          tables: ['profiles'],
          features: ['Auth']
        },
        {
          name: 'chat-app',
          description: 'Real-time chat application schema',
          path: path.join(this.templatesDir, 'chat-app.sql'),
          tables: ['profiles', 'channels', 'messages', 'channel_members'],
          features: ['Auth', 'Real-time', 'Chat']
        }
      );

      return templates;
    } catch (error) {
      throw new ProvisioningError(`Failed to list schema templates: ${error}`, ErrorCategory.FILE_SYSTEM);
    }
  }

  /**
   * Get schema template content
   */
  async getTemplate(templateName: string): Promise<string> {
    const templates = await this.listTemplates();
    const template = templates.find(t => t.name === templateName);
    
    if (!template) {
      throw new ProvisioningError(`Schema template '${templateName}' not found`, ErrorCategory.VALIDATION);
    }

    try {
      return await fs.readFile(template.path, 'utf-8');
    } catch (error) {
      throw new ProvisioningError(`Failed to read schema template: ${error}`, ErrorCategory.FILE_SYSTEM);
    }
  }

  /**
   * Validate schema syntax
   */
  async validateSchema(schemaContent: string): Promise<SchemaValidationResult> {
    const errors: string[] = [];
    const warnings: string[] = [];

    // Basic SQL validation
    const lines = schemaContent.split('\n');
    let inComment = false;

    for (let i = 0; i < lines.length; i++) {
      const line = lines[i].trim();
      
      // Skip empty lines
      if (!line) continue;
      
      // Handle comments
      if (line.startsWith('/*')) inComment = true;
      if (line.endsWith('*/')) inComment = false;
      if (inComment || line.startsWith('--')) continue;

      // Check for common issues
      if (line.toLowerCase().includes('drop table') && !line.toLowerCase().includes('if exists')) {
        warnings.push(`Line ${i + 1}: Consider using 'DROP TABLE IF EXISTS' for safety`);
      }

      if (line.toLowerCase().includes('create table') && !line.toLowerCase().includes('if not exists')) {
        warnings.push(`Line ${i + 1}: Consider using 'CREATE TABLE IF NOT EXISTS' for idempotency`);
      }

      // Check for potential security issues
      if (line.toLowerCase().includes('alter table') && line.toLowerCase().includes('disable row level security')) {
        errors.push(`Line ${i + 1}: Disabling RLS is not recommended for security`);
      }
    }

    return {
      valid: errors.length === 0,
      errors,
      warnings
    };
  }

  /**
   * Generate custom schema from template
   */
  async generateCustomSchema(
    templateName: string,
    customizations: Record<string, any>
  ): Promise<string> {
    let schema = await this.getTemplate(templateName);

    // Apply customizations
    for (const [key, value] of Object.entries(customizations)) {
      const placeholder = `{{${key}}}`;
      schema = schema.replace(new RegExp(placeholder, 'g'), String(value));
    }

    return schema;
  }

  /**
   * Create schema templates directory structure and initialize templates
   */
  async initializeTemplates(): Promise<void> {
    await fs.mkdir(this.templatesDir, { recursive: true });

    // Create SaaS starter template
    const saasStarterSchema = `-- VC-SYS SaaS Starter Schema
-- Generated by VC-SYS CLI

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

-- Profiles table (extends auth.users)
CREATE TABLE IF NOT EXISTS public.profiles (
    id UUID REFERENCES auth.users(id) PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    full_name TEXT,
    avatar_url TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL
);

-- Projects table
CREATE TABLE IF NOT EXISTS public.projects (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL
);

-- Subscriptions table for billing
CREATE TABLE IF NOT EXISTS public.subscriptions (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) NOT NULL,
    plan_name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    current_period_start TIMESTAMP WITH TIME ZONE,
    current_period_end TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL
);

-- Usage metrics table
CREATE TABLE IF NOT EXISTS public.usage_metrics (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) NOT NULL,
    metric_name TEXT NOT NULL,
    metric_value INTEGER DEFAULT 0,
    period_start TIMESTAMP WITH TIME ZONE NOT NULL,
    period_end TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL
);

-- Enable Row Level Security
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.usage_metrics ENABLE ROW LEVEL SECURITY;

-- Profiles policies
CREATE POLICY "Public profiles are viewable by everyone"
    ON public.profiles FOR SELECT
    USING (true);

CREATE POLICY "Users can insert their own profile"
    ON public.profiles FOR INSERT
    WITH CHECK (auth.uid() = id);

CREATE POLICY "Users can update own profile"
    ON public.profiles FOR UPDATE
    USING (auth.uid() = id);

-- Projects policies
CREATE POLICY "Users can view their own projects"
    ON public.projects FOR SELECT
    USING (auth.uid() = user_id);

CREATE POLICY "Users can insert their own projects"
    ON public.projects FOR INSERT
    WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own projects"
    ON public.projects FOR UPDATE
    USING (auth.uid() = user_id);

CREATE POLICY "Users can delete their own projects"
    ON public.projects FOR DELETE
    USING (auth.uid() = user_id);

-- Subscriptions policies
CREATE POLICY "Users can view their own subscriptions"
    ON public.subscriptions FOR SELECT
    USING (auth.uid() = user_id);

CREATE POLICY "Users can manage their own subscriptions"
    ON public.subscriptions FOR ALL
    USING (auth.uid() = user_id);

-- Usage metrics policies
CREATE POLICY "Users can view their own usage metrics"
    ON public.usage_metrics FOR SELECT
    USING (auth.uid() = user_id);

-- Note: Timestamp management and user profile creation will be handled at the application level
-- to avoid issues with Supabase's metadata injection system that can break dollar-quoted functions.
-- This approach is more compatible with the Supabase Management API deployment process.
`;

    // Normalize line endings to Unix format (\n) to prevent PostgreSQL parsing issues
    const normalizedSaasSchema = saasStarterSchema.replace(/\r\n/g, '\n').replace(/\r/g, '\n');
    
    await fs.writeFile(
      path.join(this.templatesDir, 'saas-starter.sql'),
      normalizedSaasSchema,
      'utf-8'
    );

    // Create minimal template
    const minimalSchema = `-- VC-SYS Minimal Schema
-- Generated by VC-SYS CLI

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

-- Profiles table (extends auth.users)
CREATE TABLE IF NOT EXISTS public.profiles (
    id UUID REFERENCES auth.users(id) PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    full_name TEXT,
    avatar_url TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL
);

-- Enable Row Level Security
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Profiles policies
CREATE POLICY "Public profiles are viewable by everyone"
    ON public.profiles FOR SELECT
    USING (true);

CREATE POLICY "Users can insert their own profile"
    ON public.profiles FOR INSERT
    WITH CHECK (auth.uid() = id);

CREATE POLICY "Users can update own profile"
    ON public.profiles FOR UPDATE
    USING (auth.uid() = id);

-- Note: Timestamp management and user profile creation will be handled at the application level
-- to avoid issues with Supabase's metadata injection system that can break dollar-quoted functions.
-- This approach is more compatible with the Supabase Management API deployment process.
`;

    // Normalize line endings to Unix format (\n) to prevent PostgreSQL parsing issues
    const normalizedMinimalSchema = minimalSchema.replace(/\r\n/g, '\n').replace(/\r/g, '\n');
    
    await fs.writeFile(
      path.join(this.templatesDir, 'minimal.sql'),
      normalizedMinimalSchema,
      'utf-8'
    );

    // Create chat app template
    const chatAppSchema = `-- VC-SYS Chat App Schema
-- Generated by VC-SYS CLI

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

-- Profiles table (extends auth.users)
CREATE TABLE IF NOT EXISTS public.profiles (
    id UUID REFERENCES auth.users(id) PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    full_name TEXT,
    avatar_url TEXT,
    status TEXT DEFAULT 'online',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL
);

-- Channels table
CREATE TABLE IF NOT EXISTS public.channels (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    is_private BOOLEAN DEFAULT false,
    created_by UUID REFERENCES auth.users(id) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL
);

-- Messages table
CREATE TABLE IF NOT EXISTS public.messages (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    channel_id UUID REFERENCES public.channels(id) ON DELETE CASCADE NOT NULL,
    user_id UUID REFERENCES auth.users(id) NOT NULL,
    content TEXT NOT NULL,
    message_type TEXT DEFAULT 'text',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL
);

-- Channel members table
CREATE TABLE IF NOT EXISTS public.channel_members (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    channel_id UUID REFERENCES public.channels(id) ON DELETE CASCADE NOT NULL,
    user_id UUID REFERENCES auth.users(id) NOT NULL,
    role TEXT DEFAULT 'member',
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    UNIQUE(channel_id, user_id)
);

-- Enable Row Level Security
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.channels ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.channel_members ENABLE ROW LEVEL SECURITY;

-- Profiles policies
CREATE POLICY "Profiles are viewable by authenticated users"
    ON public.profiles FOR SELECT
    TO authenticated
    USING (true);

CREATE POLICY "Users can update own profile"
    ON public.profiles FOR UPDATE
    USING (auth.uid() = id);

-- Channels policies
CREATE POLICY "Users can view channels they are members of"
    ON public.channels FOR SELECT
    USING (
        NOT is_private OR
        auth.uid() IN (
            SELECT user_id FROM public.channel_members 
            WHERE channel_id = id
        )
    );

-- Messages policies
CREATE POLICY "Users can view messages in channels they are members of"
    ON public.messages FOR SELECT
    USING (
        channel_id IN (
            SELECT channel_id FROM public.channel_members 
            WHERE user_id = auth.uid()
        )
    );

CREATE POLICY "Users can insert messages in channels they are members of"
    ON public.messages FOR INSERT
    WITH CHECK (
        auth.uid() = user_id AND
        channel_id IN (
            SELECT channel_id FROM public.channel_members 
            WHERE user_id = auth.uid()
        )
    );

-- Channel members policies
CREATE POLICY "Users can view channel members for channels they are in"
    ON public.channel_members FOR SELECT
    USING (
        channel_id IN (
            SELECT channel_id FROM public.channel_members 
            WHERE user_id = auth.uid()
        )
    );

-- Note: Timestamp management and user profile creation will be handled at the application level
-- to avoid issues with Supabase's metadata injection system that can break dollar-quoted functions.
-- This approach is more compatible with the Supabase Management API deployment process.

-- Enable real-time for messages
ALTER PUBLICATION supabase_realtime ADD TABLE public.messages;
ALTER PUBLICATION supabase_realtime ADD TABLE public.channel_members;
`;

    // Normalize line endings to Unix format (\n) to prevent PostgreSQL parsing issues
    const normalizedChatAppSchema = chatAppSchema.replace(/\r\n/g, '\n').replace(/\r/g, '\n');
    
    await fs.writeFile(
      path.join(this.templatesDir, 'chat-app.sql'),
      normalizedChatAppSchema,
      'utf-8'
    );

    this.logger.info('Schema templates initialized', { templatesDir: this.templatesDir });
  }
}