package com.reallinkbilling.utils;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.struts2.dispatcher.SessionMap;

import com.reallinkbilling.db.GetDBConnection;

public class CSVToDBImporter {
    
    private String csvFilePath;
    private String tableName;
    private String[] columnHeaders;
    private List<String[]> csvData;
    private SessionMap<String, Object> sessionMap;
    
    public CSVToDBImporter(String csvFilePath, String tableName, SessionMap<String, Object> sessionMap) {
        this.csvFilePath = csvFilePath;
        this.tableName = tableName;
        this.sessionMap = sessionMap;
        this.csvData = new ArrayList<>();
    }
    
    /**
     * Main method to import CSV data to database
     */
    public ImportResult importCSVToDatabase() {
        ImportResult result = new ImportResult();
        
        try {
            // Step 1: Read CSV file
            if (!readCSVFile()) {
                result.setSuccess(false);
                result.setMessage("Failed to read CSV file");
                return result;
            }
            
            // Step 2: Insert data into database
            int insertedRecords = insertDataToDatabase();
            
            result.setSuccess(true);
            result.setMessage("Successfully imported " + insertedRecords + " records");
            result.setRecordsProcessed(csvData.size());
            result.setRecordsInserted(insertedRecords);
            
        } catch (Exception e) {
            result.setSuccess(false);
            result.setMessage("Error during import: " + e.getMessage());
            e.printStackTrace();
        }
        
        return result;
    }
    
    /**
     * Read CSV file and parse data
     */
    private boolean readCSVFile() {
        try (BufferedReader br = new BufferedReader(new FileReader(csvFilePath))) {
            String line;
            boolean isFirstLine = true;
            
            while ((line = br.readLine()) != null) {
                String[] values = parseCSVLine(line);
                
                if (isFirstLine) {
                    // First line contains column headers
                    columnHeaders = values;
                    isFirstLine = false;
                    System.out.println("Column Headers: ");
                    for (int i = 0; i < columnHeaders.length; i++) {
                        System.out.println((i + 1) + ". " + columnHeaders[i].trim());
                    }
                } else {
                    // Data rows
                    csvData.add(values);
                }
            }
            
            System.out.println("Total records found: " + csvData.size());
            return true;
            
        } catch (IOException e) {
            System.err.println("Error reading CSV file: " + e.getMessage());
            return false;
        }
    }
    
    /**
     * Parse CSV line handling commas within quotes
     */
    private String[] parseCSVLine(String line) {
        List<String> values = new ArrayList<>();
        StringBuilder currentValue = new StringBuilder();
        boolean inQuotes = false;
        
        for (int i = 0; i < line.length(); i++) {
            char ch = line.charAt(i);
            
            if (ch == '"') {
                inQuotes = !inQuotes;
            } else if (ch == ',' && !inQuotes) {
                values.add(currentValue.toString().trim());
                currentValue = new StringBuilder();
            } else {
                currentValue.append(ch);
            }
        }
        
        // Add the last value
        values.add(currentValue.toString().trim());
        
        return values.toArray(new String[0]);
    }
    
    /**
     * Insert parsed data into database
     */
    private int insertDataToDatabase() throws SQLException, IOException {
        String db = (String) sessionMap.get("db");
        String shopId = (String) sessionMap.get("shopId");
        String shopCode = (String) sessionMap.get("shopCode");
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int insertedCount = 0;
        
        try {
            connection = GetDBConnection.getDBConnection(db);
            connection.setAutoCommit(false);
            
            // Build dynamic INSERT SQL based on column headers
            String insertSQL = buildInsertSQL();
            System.out.println("Generated SQL: " + insertSQL);
            
            preparedStatement = connection.prepareStatement(insertSQL);
            
            for (String[] row : csvData) {
                try {
                    // Set parameters based on data type detection
                    setParametersForRow(preparedStatement, row, shopId, shopCode);
                    
                    int result = preparedStatement.executeUpdate();
                    if (result > 0) {
                        insertedCount++;
                    }
                    
                    // Print progress every 100 records
                    if (insertedCount % 100 == 0) {
                        System.out.println("Processed " + insertedCount + " records...");
                    }
                    
                } catch (SQLException e) {
                    System.err.println("Error inserting row: " + String.join(",", row));
                    System.err.println("SQL Error: " + e.getMessage());
                    // Continue with next row
                }
            }
            
            connection.commit();
            System.out.println("Successfully inserted " + insertedCount + " records");
            
        } catch (Exception e) {
            if (connection != null) {
                connection.rollback();
            }
            throw e;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        
        return insertedCount;
    }
    
    /**
     * Build dynamic INSERT SQL based on column headers
     */
    private String buildInsertSQL() {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(tableName).append(" (");
        
        // Add shop_id and shop_code first
        sql.append("shop_id, shop_code, ");
        
        // Add CSV column headers
        for (int i = 0; i < columnHeaders.length; i++) {
            String columnName = sanitizeColumnName(columnHeaders[i]);
            sql.append(columnName);
            if (i < columnHeaders.length - 1) {
                sql.append(", ");
            }
        }
        
        sql.append(") VALUES (?, ?, ");
        
        // Add placeholders for CSV data
        for (int i = 0; i < columnHeaders.length; i++) {
            sql.append("?");
            if (i < columnHeaders.length - 1) {
                sql.append(", ");
            }
        }
        
        sql.append(")");
        return sql.toString();
    }
    
    /**
     * Sanitize column names for SQL
     */
    private String sanitizeColumnName(String columnName) {
        return columnName.trim()
                        .toLowerCase()
                        .replaceAll(" ", "_")
                        .replaceAll("[^a-zA-Z0-9_]", "");
    }
    
    /**
     * Set parameters for prepared statement
     */
    private void setParametersForRow(PreparedStatement stmt, String[] row, String shopId, String shopCode) 
            throws SQLException {
        
        // Set shop_id and shop_code first
        stmt.setString(1, shopId);
        stmt.setString(2, shopCode);
        
        // Set CSV data starting from parameter 3
        for (int i = 0; i < row.length && i < columnHeaders.length; i++) {
            String value = row[i].trim();
            
            if (value.isEmpty() || value.equalsIgnoreCase("null")) {
                stmt.setNull(i + 3, java.sql.Types.VARCHAR);
            } else {
                // Try to detect data type and set accordingly
                if (isNumeric(value)) {
                    if (value.contains(".")) {
                        stmt.setDouble(i + 3, Double.parseDouble(value));
                    } else {
                        stmt.setInt(i + 3, Integer.parseInt(value));
                    }
                } else {
                    stmt.setString(i + 3, value);
                }
            }
        }
        
        // Fill remaining parameters with null if row has fewer columns
        for (int i = row.length; i < columnHeaders.length; i++) {
            stmt.setNull(i + 3, java.sql.Types.VARCHAR);
        }
    }
    
    /**
     * Check if string is numeric
     */
    private boolean isNumeric(String str) {
        try {
            Double.parseDouble(str);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }
    
    /**
     * Generate CREATE TABLE SQL based on CSV headers
     */
    public String generateCreateTableSQL() {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE IF NOT EXISTS ").append(tableName).append(" (\n");
        sql.append("    id INT AUTO_INCREMENT PRIMARY KEY,\n");
        sql.append("    shop_id VARCHAR(50) NOT NULL,\n");
        sql.append("    shop_code VARCHAR(50) NOT NULL,\n");
        
        for (int i = 0; i < columnHeaders.length; i++) {
            String columnName = sanitizeColumnName(columnHeaders[i]);
            sql.append("    ").append(columnName).append(" TEXT");
            if (i < columnHeaders.length - 1) {
                sql.append(",");
            }
            sql.append("\n");
        }
        
        sql.append(");");
        return sql.toString();
    }
    
    /**
     * Result class for import operation
     */
    public static class ImportResult {
        private boolean success;
        private String message;
        private int recordsProcessed;
        private int recordsInserted;
        
        // Getters and setters
        public boolean isSuccess() { return success; }
        public void setSuccess(boolean success) { this.success = success; }
        
        public String getMessage() { return message; }
        public void setMessage(String message) { this.message = message; }
        
        public int getRecordsProcessed() { return recordsProcessed; }
        public void setRecordsProcessed(int recordsProcessed) { this.recordsProcessed = recordsProcessed; }
        
        public int getRecordsInserted() { return recordsInserted; }
        public void setRecordsInserted(int recordsInserted) { this.recordsInserted = recordsInserted; }
    }
} 