import database from "../config/db";

export async function createAccountsTable() {
  try {
    const query = `
        CREATE TABLE IF NOT EXISTS accounts (
          -- Primary Key
          id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
          
          -- Account Type & Basic Info
          account_type VARCHAR(20) NOT NULL DEFAULT 'user' 
            CHECK (account_type IN ('user', 'company')),
          username VARCHAR(50) UNIQUE NOT NULL 
            CHECK (username ~ '^[a-zA-Z0-9_]{3,50}$'),
          email VARCHAR(100) UNIQUE NOT NULL,
          
          -- Authentication
          password_hash TEXT,
          is_email_verified BOOLEAN DEFAULT false,
          email_verified_at TIMESTAMP,
          
          -- OTP & Security
          otp_code VARCHAR(6),
          otp_expires_at TIMESTAMP,
          reset_password_token TEXT,
          reset_password_expires_at TIMESTAMP,
          two_factor_enabled BOOLEAN DEFAULT false,
          failed_login_attempts INTEGER DEFAULT 0,
          last_failed_login TIMESTAMP,
          
          -- Social Authentication
          google_id VARCHAR(100) UNIQUE,
          github_id VARCHAR(100) UNIQUE,
          social_auth_providers TEXT[] DEFAULT '{}',
          
          -- Profile Information (Common)
          display_name VARCHAR(100),
          avatar JSONB DEFAULT NULL,
          cover_image JSONB DEFAULT NULL,
          bio TEXT,
          website VARCHAR(500),
          
          -- Contact Information
          phone_number VARCHAR(20),
          location_city VARCHAR(100),
          location_country VARCHAR(100),
          address_street TEXT,
          address_state VARCHAR(100),
          address_zip VARCHAR(20),
          
          -- User Specific Fields (NULL for companies)
          user_full_name VARCHAR(200),
          user_gender VARCHAR(20) 
            CHECK (user_gender IN ('male', 'female', 'other', 'prefer-not-to-say', NULL)),
          user_personal_bio TEXT,
          user_personal_website VARCHAR(500),
          user_job_title VARCHAR(100),
          user_current_company VARCHAR(200),
          user_education TEXT,
          user_skills TEXT[] DEFAULT '{}',
          user_experience_years INTEGER,
          user_github_url VARCHAR(500),
          user_twitter_url VARCHAR(500),
          user_linkedin_url VARCHAR(500),
          user_portfolio_url VARCHAR(500),
          
          -- Company Specific Fields (NULL for users)
          company_name VARCHAR(200),
          company_founded_year INTEGER 
            CHECK (company_founded_year >= 1800 AND company_founded_year <= EXTRACT(YEAR FROM CURRENT_DATE)),
          company_size VARCHAR(20) DEFAULT 'solo' 
            CHECK (company_size IN ('solo', '2-10', '11-50', '51-200', '200-plus')),
          company_headquarters_location VARCHAR(200),
          company_business_type VARCHAR(100),
          company_target_market VARCHAR(200),
          company_description TEXT,
          company_mission_statement TEXT,
          company_contact_person VARCHAR(200),
          company_contact_email VARCHAR(100),
          company_contact_phone VARCHAR(20),
          company_website VARCHAR(500),
          company_legal_name VARCHAR(200),
          
          -- Stats & Analytics
          last_login_at TIMESTAMP,
          login_count INTEGER DEFAULT 0,
          profile_completion_percentage INTEGER DEFAULT 0 
            CHECK (profile_completion_percentage >= 0 AND profile_completion_percentage <= 100),
          account_created_via VARCHAR(20) DEFAULT 'manual' 
            CHECK (account_created_via IN ('manual', 'google', 'github')),
          
          -- Settings & Preferences
          is_active BOOLEAN DEFAULT true,
          is_public BOOLEAN DEFAULT true,
          email_notifications JSONB DEFAULT '{
            "product_updates": true,
            "marketing": false,
            "security_alerts": true,
            "newsletter": false,
            "comment_replies": true,
            "new_followers": true 
          }'::JSONB,
          push_notifications JSONB DEFAULT '{}'::JSONB,
          language_preference VARCHAR(10) DEFAULT 'en',
          timezone VARCHAR(50) DEFAULT 'UTC', 
          
          -- Privacy Settings
          privacy_settings JSONB DEFAULT '{
            "profile_visibility": "public",
            "email_visibility": "private",
            "phone_visibility": "private",
            "activity_visibility": "public",
            "data_sharing": false
          }'::JSONB,
          
          -- Account Status
          account_status VARCHAR(20) DEFAULT 'active' 
            CHECK (account_status IN ('active', 'suspended', 'deactivated', 'pending_verification')),
          suspension_reason TEXT,
          suspension_ends_at TIMESTAMP,
          
          -- Timestamps
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          deleted_at TIMESTAMP,
          
          -- Constraints
          CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
          CONSTRAINT valid_phone CHECK (phone_number IS NULL OR phone_number ~ '^[0-9+\-\s()]{10,20}$'),
          CONSTRAINT user_fields_for_users_only CHECK (
            (account_type = 'user' AND user_full_name IS NOT NULL) OR 
            (account_type = 'company' AND user_full_name IS NULL)
          ),
          CONSTRAINT company_fields_for_companies_only CHECK (
            (account_type = 'company' AND company_name IS NOT NULL) OR 
            (account_type = 'user' AND company_name IS NULL)
          ),
          CONSTRAINT password_or_social_required CHECK (
            password_hash IS NOT NULL OR 
            google_id IS NOT NULL OR 
            github_id IS NOT NULL
            -- linkedin_id IS NOT NULL
          ),
          CONSTRAINT account_upgrade_check CHECK (
            account_type != 'company' OR 
            (account_type = 'company' AND is_email_verified = true)
          )
        );`;

    await database.query(query);

    // Index creation for better performance
    const indexes = [
      // Basic search and lookup
      `CREATE INDEX IF NOT EXISTS idx_accounts_username ON accounts(username);`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_email ON accounts(email);`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_account_type ON accounts(account_type);`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_is_active ON accounts(is_active) WHERE is_active = true;`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_is_public ON accounts(is_public) WHERE is_public = true;`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_is_email_verified ON accounts(is_email_verified) WHERE is_email_verified = true;`,

      // For social auth
      `CREATE INDEX IF NOT EXISTS idx_accounts_google_id ON accounts(google_id) WHERE google_id IS NOT NULL;`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_github_id ON accounts(github_id) WHERE github_id IS NOT NULL;`,

      // For user-specific queries
      `CREATE INDEX IF NOT EXISTS idx_accounts_user_full_name ON accounts(user_full_name) WHERE account_type = 'user';`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_user_skills ON accounts USING GIN(user_skills) WHERE account_type = 'user';`,

      // For company-specific queries
      `CREATE INDEX IF NOT EXISTS idx_accounts_company_name ON accounts(company_name) WHERE account_type = 'company';`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_company_size ON accounts(company_size) WHERE account_type = 'company';`,

      // For location-based queries
      `CREATE INDEX IF NOT EXISTS idx_accounts_location_country ON accounts(location_country);`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_location_city ON accounts(location_city);`,

      // For date-based queries
      `CREATE INDEX IF NOT EXISTS idx_accounts_created_at ON accounts(created_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_last_login ON accounts(last_login_at DESC NULLS LAST);`,

      // For OTP and security
      `CREATE INDEX IF NOT EXISTS idx_accounts_otp_expires ON accounts(otp_expires_at) WHERE otp_expires_at IS NOT NULL;`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_reset_password_expires ON accounts(reset_password_expires_at) WHERE reset_password_expires_at IS NOT NULL;`,

      // Composite indexes for common queries
      `CREATE INDEX IF NOT EXISTS idx_accounts_type_active_public ON accounts(account_type, is_active, is_public) WHERE is_active = true AND is_public = true;`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_email_verified_type ON accounts(is_email_verified, account_type) WHERE is_email_verified = true;`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_completion_active ON accounts(profile_completion_percentage DESC) WHERE is_active = true;`,

      // For full-text search
      `CREATE INDEX IF NOT EXISTS idx_accounts_search_display_name ON accounts USING gin(to_tsvector('english', display_name));`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_search_bio ON accounts USING gin(to_tsvector('english', bio));`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_search_user_bio ON accounts USING gin(to_tsvector('english', user_personal_bio)) WHERE account_type = 'user';`,
      `CREATE INDEX IF NOT EXISTS idx_accounts_search_company_desc ON accounts USING gin(to_tsvector('english', company_description)) WHERE account_type = 'company';`,
    ];

    for (const indexQuery of indexes) {
      try {
        await database.query(indexQuery);
      } catch (err) {
        // Index already exists, ignore
        if (
          !(err instanceof Error) ||
          !err.message.includes("already exists")
        ) {
          throw err;
        }
      }
    }

    // Create function to calculate profile completion percentage
    const profileCompletionFunction = `
      CREATE OR REPLACE FUNCTION calculate_profile_completion(account_id UUID)
      RETURNS INTEGER AS $$
      DECLARE
        account_record RECORD;
        total_fields INTEGER := 0;
        completed_fields INTEGER := 0;
        percentage INTEGER;
      BEGIN
        SELECT * INTO account_record FROM accounts WHERE id = account_id;
        
        IF NOT FOUND THEN
          RETURN 0;
        END IF;
        
        IF account_record.account_type = 'user' THEN
          -- User profile completion logic
          total_fields := 15;
          
          IF account_record.avatar IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.bio IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.user_full_name IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.user_job_title IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.location_city IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.location_country IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.website IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.phone_number IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.user_github_url IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.user_linkedin_url IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF array_length(account_record.user_skills, 1) > 0 THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.user_experience_years IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.user_education IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.user_current_company IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.user_portfolio_url IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          
        ELSE
          -- Company profile completion logic
          total_fields := 12;
          
          IF account_record.avatar IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.bio IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.company_name IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.company_description IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.company_website IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.company_contact_email IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.company_contact_phone IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.location_city IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.location_country IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.company_founded_year IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
          IF account_record.company_size IS NOT NULL THEN completed_fields := completed_fields + 1; END IF;
        END IF;
        
        percentage := (completed_fields * 100) / total_fields;
        RETURN percentage;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(profileCompletionFunction);

    // Create trigger function to update profile completion percentage
    const updateProfileCompletionFunction = `
      CREATE OR REPLACE FUNCTION update_profile_completion()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.profile_completion_percentage := calculate_profile_completion(NEW.id);
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(updateProfileCompletionFunction);

    // Drop existing trigger before creating new one
    await database.query(`
      DROP TRIGGER IF EXISTS trigger_update_profile_completion ON accounts;
    `);

    // Create trigger for profile completion
    const profileCompletionTrigger = `
      CREATE TRIGGER trigger_update_profile_completion
      BEFORE INSERT OR UPDATE ON accounts
      FOR EACH ROW
      EXECUTE FUNCTION update_profile_completion();
    `;

    await database.query(profileCompletionTrigger);

    // Create trigger function to automatically update updated_at
    const updateTimestampFunction = `
      CREATE OR REPLACE FUNCTION update_accounts_updated_at()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = CURRENT_TIMESTAMP;
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(updateTimestampFunction);

    // Drop existing timestamp trigger
    await database.query(`
      DROP TRIGGER IF EXISTS set_accounts_updated_at ON accounts;
    `);

    // Create trigger for automatic updated_at
    const updateTimestampTrigger = `
      CREATE TRIGGER set_accounts_updated_at
      BEFORE UPDATE ON accounts
      FOR EACH ROW
      EXECUTE FUNCTION update_accounts_updated_at();
    `;

    await database.query(updateTimestampTrigger);

    // Create function to upgrade user to company
    const upgradeToCompanyFunction = `
      CREATE OR REPLACE FUNCTION upgrade_user_to_company(
        user_account_id UUID,
        new_company_name VARCHAR(200),
        new_company_description TEXT
      )
      RETURNS JSON AS $$
      DECLARE
        account_record RECORD;
        result JSON;
      BEGIN
        -- Get the account
        SELECT * INTO account_record 
        FROM accounts 
        WHERE id = user_account_id AND account_type = 'user';
        
        IF NOT FOUND THEN
          RETURN json_build_object(
            'success', false,
            'message', 'User account not found'
          );
        END IF;
        
        -- Check if email is verified
        IF account_record.is_email_verified = false THEN
          RETURN json_build_object(
            'success', false,
            'message', 'Email must be verified before upgrading to company account'
          );
        END IF;
        
        -- Update to company
        UPDATE accounts
        SET 
          account_type = 'company',
          company_name = new_company_name,
          company_description = new_company_description,
          display_name = new_company_name,
          updated_at = CURRENT_TIMESTAMP
        WHERE id = user_account_id
        RETURNING * INTO account_record;
        
        result := json_build_object(
          'success', true,
          'message', 'Account upgraded to company successfully',
          'account_id', account_record.id,
          'company_name', account_record.company_name
        );
        
        RETURN result;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(upgradeToCompanyFunction);

    // Add these functions to your database setup

    // Function to get account activity summary
    const createActivityFunction = `
      CREATE OR REPLACE FUNCTION get_account_activity_summary(account_id UUID)
      RETURNS JSON AS $$
      DECLARE
        account_record RECORD;
        summary JSON;
      BEGIN
        SELECT * INTO account_record FROM accounts WHERE id = account_id;
        
        IF NOT FOUND THEN
          RETURN json_build_object('error', 'Account not found');
        END IF;
        
        summary := json_build_object(
          'account_id', account_record.id,
          'username', account_record.username,
          'account_type', account_record.account_type,
          'login_stats', json_build_object(
            'login_count', account_record.login_count,
            'last_login', account_record.last_login_at,
            'profile_completion', account_record.profile_completion_percentage
          ),
          'verification_status', json_build_object(
            'email_verified', account_record.is_email_verified,
            'email_verified_at', account_record.email_verified_at
          ),
          'account_status', json_build_object(
            'status', account_record.account_status,
            'is_active', account_record.is_active,
            'is_public', account_record.is_public
          ),
          'timestamps', json_build_object(
            'created_at', account_record.created_at,
            'updated_at', account_record.updated_at
          )
        );
        
        RETURN summary;
      END;
      $$ LANGUAGE plpgsql;
    `;
    await database.query(createActivityFunction);

    // Function to validate account data
    const createValidationFunction = `
      CREATE OR REPLACE FUNCTION validate_account_data(
        p_account_type VARCHAR,
        p_user_data JSONB DEFAULT NULL,
        p_company_data JSONB DEFAULT NULL
      )
      RETURNS JSON AS $$
      DECLARE
        validation_result JSON;
        errors TEXT[] := '{}';
      BEGIN
        -- Validate account type
        IF p_account_type NOT IN ('user', 'company') THEN
          errors := array_append(errors, 'Invalid account type');
        END IF;
        
        -- User specific validation
        IF p_account_type = 'user' AND p_user_data IS NOT NULL THEN
          IF NOT (p_user_data ? 'user_full_name') THEN
            errors := array_append(errors, 'Full name is required for users');
          END IF;
          
          IF p_user_data ? 'user_experience_years' THEN
            IF (p_user_data->>'user_experience_years')::INTEGER < 0 THEN
              errors := array_append(errors, 'Experience years cannot be negative');
            END IF;
          END IF;
        END IF;
        
        -- Company specific validation
        IF p_account_type = 'company' AND p_company_data IS NOT NULL THEN
          IF NOT (p_company_data ? 'company_name') THEN
            errors := array_append(errors, 'Company name is required for companies');
          END IF;
          
          IF p_company_data ? 'company_founded_year' THEN
            IF (p_company_data->>'company_founded_year')::INTEGER < 1800 
               OR (p_company_data->>'company_founded_year')::INTEGER > EXTRACT(YEAR FROM CURRENT_DATE) THEN
              errors := array_append(errors, 'Invalid founded year');
            END IF;
          END IF;
        END IF;
        
        IF array_length(errors, 1) > 0 THEN
          validation_result := json_build_object(
            'valid', false,
            'errors', errors
          );
        ELSE
          validation_result := json_build_object('valid', true);
        END IF;
        
        RETURN validation_result;
      END;
      $$ LANGUAGE plpgsql;
    `;
    await database.query(createValidationFunction);

    console.log("✅ Accounts table created successfully");
  } catch (err) {
    console.error("❌ Failed To Create Accounts Table.", err);
    process.exit(1);
  }
}
