import * as XLSX from 'xlsx';
import { db } from '../src/db';
import { unitsTable } from '../src/db/schema';
import { eq, and } from 'drizzle-orm';

interface XLSXRow {
  'Project Name': string;
  'Floor': number;
  'Unit No.': string;
  'Unit Series': string;
  'No of Parking Space': number;
  'Suits Area (sqft)': number;
  'Balcony Area (sqft)': number;
  'Total Square Ft.': number;
  'Unit Type Variant': string;
  'Unit View': string;
}

// Constants for project
const PROJECT_NAME = 'Rove Home HQ';
const PROJECT_SLUG = 'hq';

// Helper function to generate slug from text
function generateSlug(text: string): string {
  return text
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-+|-+$/g, '');
}

// Helper function to extract unit number
function extractUnitNumber(unitNo: string): number | null {
  const match = unitNo.match(/\d+/);
  return match ? parseInt(match[0]) : null;
}

// Helper function to determine unit type based on variant
function getUnitType(variant: string): string {
  // Extract bedroom count from variant (e.g., "1BR" -> "1 Bedroom")
  const match = variant.match(/(\d+)BR/i);
  if (match) {
    return `${match[1]} Bedroom`;
  }
  return variant;
}

// Helper function to determine number of bathrooms based on variant
function getNoOfBathrooms(variant: string): number {
  // Common logic: 1BR = 1 bath, 2BR = 2 baths, 3BR = 3 baths, etc.
  const match = variant.match(/(\d+)BR/i);
  if (match) {
    const bedrooms = parseInt(match[1]);
    return bedrooms; // Simple 1:1 mapping, adjust if needed
  }
  return 1; // Default
}

// Helper function to determine wing (East/West) if applicable
function getWing(unitView: string): 'East' | 'West' | null {
  const view = unitView.toLowerCase();
  if (view.includes('east')) return 'East';
  if (view.includes('west')) return 'West';
  return null;
}

// Helper function to determine side (left/right) if applicable
function getSide(unitNo: string): 'left' | 'right' | null {
  // This logic depends on your project's numbering system
  // You may need to adjust this based on your specific requirements
  const num = extractUnitNumber(unitNo);
  if (num === null) return null;
  
  // Example logic: even numbers on right, odd on left
  // Adjust based on your actual unit numbering system
  return num % 2 === 0 ? 'right' : 'left';
}

async function importUnitsFromXLSX(filePath: string) {
  try {
    console.log(`Reading file: ${filePath}`);
    
    // Read the XLSX file
    const workbook = XLSX.readFile(filePath);
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    
    // Convert to JSON
    const data: XLSXRow[] = XLSX.utils.sheet_to_json(worksheet);
    
    console.log(`Found ${data.length} rows to import`);
    
    let imported = 0;
    let updated = 0;
    let skipped = 0;
    let errors = 0;
    
    for (const row of data) {
      try {
        // Validate required fields
        if (!row['Unit No.'] || row['Floor'] === undefined) {
          console.warn(`Skipping row due to missing required fields:`, row);
          skipped++;
          continue;
        }
        
        const unitTypeVariant = row['Unit Type Variant'] || row['Unit Series'] || '';
        const unitTypeVariantSlug = generateSlug(unitTypeVariant);
        
        // Prepare unit data
        const unitData = {
          unitNo: row['Unit No.'].toString().trim(),
          number: extractUnitNumber(row['Unit No.'].toString()),
          project: PROJECT_NAME,
          projectSlug: PROJECT_SLUG,
          floor: Math.floor(row['Floor']),
          unitType: getUnitType(unitTypeVariant),
          noOfBathrooms: getNoOfBathrooms(unitTypeVariant),
          unitView: row['Unit View']?.trim() || null,
          unitTypeVariant: unitTypeVariant || null,
          unitTypeVariantSlug: unitTypeVariantSlug || null,
          suitsArea: row['Suits Area (sqft)'] || 0,
          squareFt: row['Total Square Ft.'] || 0,
          noOfParkingSpace: row['No of Parking Space'] || 0,
          salesPrice: 0, // Not in XLSX, needs to be set separately
          state: 'available' as const, // Default state
          balconyArea: row['Balcony Area (sqft)'] || 0,
          wing: getWing(row['Unit View'] || ''),
          side: getSide(row['Unit No.'].toString()),
          isLoft: false, // Default, can be updated based on unit type
        };
        
        // Check if unit already exists
        const existingUnit = await db
          .select()
          .from(unitsTable)
          .where(
            and(
              eq(unitsTable.project, PROJECT_NAME),
              eq(unitsTable.unitNo, unitData.unitNo)
            )
          )
          .limit(1);
        
        if (existingUnit.length > 0) {
          // Update existing unit
          await db
            .update(unitsTable)
            .set(unitData)
            .where(eq(unitsTable.id, existingUnit[0].id));
          
          console.log(`Updated: ${PROJECT_NAME} - ${unitData.unitNo}`);
          updated++;
        } else {
          // Insert new unit
          await db.insert(unitsTable).values(unitData);
          console.log(`Imported: ${PROJECT_NAME} - ${unitData.unitNo}`);
          imported++;
        }
        
      } catch (error) {
        console.error(`Error processing row:`, row, error);
        errors++;
      }
    }
    
    console.log('\n=== Import Summary ===');
    console.log(`Total rows: ${data.length}`);
    console.log(`Imported: ${imported}`);
    console.log(`Updated: ${updated}`);
    console.log(`Skipped: ${skipped}`);
    console.log(`Errors: ${errors}`);
    
  } catch (error) {
    console.error('Failed to import units:', error);
    throw error;
  }
}

// Main execution
const args = process.argv.slice(2);
const filePath = args[0];

if (!filePath) {
  console.error('Usage: bun run scripts/import-units.ts <path-to-xlsx-file>');
  process.exit(1);
}

importUnitsFromXLSX(filePath)
  .then(() => {
    console.log('Import completed successfully');
    process.exit(0);
  })
  .catch((error) => {
    console.error('Import failed:', error);
    process.exit(1);
  });