<?php
require_once 'includes/config.php';

echo "Creating Madurai Sample Data...\n\n";

try {
    // Clear existing data
    echo "Clearing existing data...\n";
    $conn->query("DELETE FROM tblcustomerpayments");
    $conn->query("DELETE FROM tblcustomerreminders");
    $conn->query("DELETE FROM tblcustomers");
    $conn->query("DELETE FROM tblareas");
    echo "Existing data cleared.\n\n";

    // Insert Madurai Areas
    echo "Creating Areas...\n";
    $areas = [
        ['Anna Nagar', 'Anna Nagar - Madurai'],
        ['KK Nagar', 'KK Nagar - Madurai'],
        ['Sellur', 'Sellur - Madurai'],
        ['Thirunagar', 'Thirunagar - Madurai'],
        ['Pasumalai', 'Pasumalai - Madurai'],
        ['Vilangudi', 'Vilangudi - Madurai'],
        ['Simmakkal', 'Simmakkal - Madurai'],
        ['Goripalayam', 'Goripalayam - Madurai'],
        ['Meenakshi Amman College', 'Meenakshi Amman College Area'],
        ['Tallakulam', 'Tallakulam - Madurai'],
        ['Madurai East', 'Madurai East'],
        ['Madurai West', 'Madurai West'],
        ['Periyar', 'Periyar Bus Stand Area'],
        ['Mattuthavani', 'Mattuthavani - Madurai'],
        ['Arapalayam', 'Arapalayam - Madurai']
    ];

    foreach($areas as $area) {
        $stmt = $conn->prepare("INSERT INTO tblareas (AreaName, Description, IsActive) VALUES (?, ?, 1)");
        $stmt->bind_param("ss", $area[0], $area[1]);
        $stmt->execute();
    }
    echo count($areas) . " areas created.\n\n";

    // Insert 20 Retail Customers
    echo "Creating Retail Customers...\n";
    $retailCustomers = [
        ['Murugan K', '9001234567', '8001234567', 'Anna Nagar', '12/45, Gandhi Street, Anna Nagar, Madurai-625020', 'https://maps.google.com/?q=9.9252,78.1198'],
        ['Lakshmi S', '9001234568', '8001234568', 'KK Nagar', '23/67, Bharathi Street, KK Nagar, Madurai-625021', 'https://maps.google.com/?q=9.9195,78.1077'],
        ['Raman P', '9001234569', '8001234569', 'Sellur', '34/89, Periyar Road, Sellur, Madurai-625002', 'https://maps.google.com/?q=9.9312,78.1587'],
        ['Kamala T', '9001234570', '8001234570', 'Thirunagar', '45/12, Thiruvalluvar Street, Thirunagar, Madurai-625006', 'https://maps.google.com/?q=9.9442,78.1385'],
        ['Selvam R', '9001234571', '8001234571', 'Pasumalai', '56/34, Kamaraj Road, Pasumalai, Madurai-625004', 'https://maps.google.com/?q=9.8842,78.0932'],
        ['Priya V', '9001234572', '8001234572', 'Vilangudi', '67/56, Netaji Road, Vilangudi, Madurai-625009', 'https://maps.google.com/?q=9.8912,78.1432'],
        ['Kumar M', '9001234573', '8001234573', 'Simmakkal', '78/78, Market Street, Simmakkal, Madurai-625001', 'https://maps.google.com/?q=9.9195,78.1198'],
        ['Meera N', '9001234574', '8001234574', 'Goripalayam', '89/90, Temple Street, Goripalayam, Madurai-625002', 'https://maps.google.com/?q=9.9098,78.1289'],
        ['Rajesh B', '9001234575', '8001234575', 'Meenakshi Amman College', '90/12, College Road, MAC Area, Madurai-625011', 'https://maps.google.com/?q=9.8765,78.0954'],
        ['Sita L', '9001234576', '8001234576', 'Tallakulam', '01/23, River View Street, Tallakulam, Madurai-625002', 'https://maps.google.com/?q=9.9187,78.1543'],
        ['Arjun K', '9001234577', '8001234577', 'Madurai East', '12/34, East Gate Road, Madurai East-625001', 'https://maps.google.com/?q=9.9311,78.1321'],
        ['Divya R', '9001234578', '8001234578', 'Madurai West', '23/45, West Masi Street, Madurai West-625001', 'https://maps.google.com/?q=9.9198,78.1076'],
        ['Ganesh S', '9001234579', '8001234579', 'Periyar', '34/56, Bus Stand Road, Periyar, Madurai-625007', 'https://maps.google.com/?q=9.9252,78.1432'],
        ['Kavitha M', '9001234580', '8001234580', 'Mattuthavani', '45/67, New Colony, Mattuthavani, Madurai-625107', 'https://maps.google.com/?q=9.8976,78.0876'],
        ['Suresh P', '9001234581', '8001234581', 'Arapalayam', '56/78, Main Road, Arapalayam, Madurai-625016', 'https://maps.google.com/?q=9.9065,78.0754'],
        ['Valli A', '9001234582', '8001234582', 'Anna Nagar', '67/89, Second Street, Anna Nagar, Madurai-625020', 'https://maps.google.com/?q=9.9265,78.1211'],
        ['Bharathi C', '9001234583', '8001234583', 'KK Nagar', '78/90, Third Avenue, KK Nagar, Madurai-625021', 'https://maps.google.com/?q=9.9187,78.1089'],
        ['Muthu V', '9001234584', '8001234584', 'Sellur', '89/01, Fourth Cross, Sellur, Madurai-625002', 'https://maps.google.com/?q=9.9324,78.1599'],
        ['Sangeetha D', '9001234585', '8001234585', 'Thirunagar', '90/12, Fifth Street, Thirunagar, Madurai-625006', 'https://maps.google.com/?q=9.9454,78.1397'],
        ['Venkatesh G', '9001234586', '8001234586', 'Pasumalai', '01/23, Sixth Road, Pasumalai, Madurai-625004', 'https://maps.google.com/?q=9.8854,78.0944']
    ];

    foreach($retailCustomers as $customer) {
        $stmt = $conn->prepare("INSERT INTO tblcustomers (CustomerName, Mobile, AlternativePhone, Area, Address, GmapLocation, CustomerType, CreatedBy, CreatedDate) VALUES (?, ?, ?, ?, ?, ?, 'Retail', 1, NOW())");
        $stmt->bind_param("ssssss", $customer[0], $customer[1], $customer[2], $customer[3], $customer[4], $customer[5]);
        $stmt->execute();
    }
    echo count($retailCustomers) . " retail customers created.\n\n";

    // Insert 20 Shop Customers
    echo "Creating Shop Customers...\n";
    $shopCustomers = [
        ['Sri Murugan Rice Store', '9002345678', '8002345678', 'Anna Nagar', 'Shop No. 15, Gandhi Street, Anna Nagar, Madurai-625020', 'https://maps.google.com/?q=9.9252,78.1198'],
        ['Lakshmi Provisions', '9002345679', '8002345679', 'KK Nagar', 'Shop No. 27, Bharathi Street, KK Nagar, Madurai-625021', 'https://maps.google.com/?q=9.9195,78.1077'],
        ['Raman Rice Mill', '9002345680', '8002345680', 'Sellur', 'Shop No. 38, Periyar Road, Sellur, Madurai-625002', 'https://maps.google.com/?q=9.9312,78.1587'],
        ['Kamala Grocery Store', '9002345681', '8002345681', 'Thirunagar', 'Shop No. 49, Thiruvalluvar Street, Thirunagar, Madurai-625006', 'https://maps.google.com/?q=9.9442,78.1385'],
        ['Selvam Wholesale Rice', '9002345682', '8002345682', 'Pasumalai', 'Shop No. 60, Kamaraj Road, Pasumalai, Madurai-625004', 'https://maps.google.com/?q=9.8842,78.0932'],
        ['Priya Departmental Store', '9002345683', '8002345683', 'Vilangudi', 'Shop No. 71, Netaji Road, Vilangudi, Madurai-625009', 'https://maps.google.com/?q=9.8912,78.1432'],
        ['Kumar Food Mart', '9002345684', '8002345684', 'Simmakkal', 'Shop No. 82, Market Street, Simmakkal, Madurai-625001', 'https://maps.google.com/?q=9.9195,78.1198'],
        ['Meera General Stores', '9002345685', '8002345685', 'Goripalayam', 'Shop No. 93, Temple Street, Goripalayam, Madurai-625002', 'https://maps.google.com/?q=9.9098,78.1289'],
        ['Rajesh Rice Trading', '9002345686', '8002345686', 'Meenakshi Amman College', 'Shop No. 104, College Road, MAC Area, Madurai-625011', 'https://maps.google.com/?q=9.8765,78.0954'],
        ['Sita Super Market', '9002345687', '8002345687', 'Tallakulam', 'Shop No. 115, River View Street, Tallakulam, Madurai-625002', 'https://maps.google.com/?q=9.9187,78.1543'],
        ['Arjun Rice Depot', '9002345688', '8002345688', 'Madurai East', 'Shop No. 126, East Gate Road, Madurai East-625001', 'https://maps.google.com/?q=9.9311,78.1321'],
        ['Divya Provision Store', '9002345689', '8002345689', 'Madurai West', 'Shop No. 137, West Masi Street, Madurai West-625001', 'https://maps.google.com/?q=9.9198,78.1076'],
        ['Ganesh Wholesale Market', '9002345690', '8002345690', 'Periyar', 'Shop No. 148, Bus Stand Road, Periyar, Madurai-625007', 'https://maps.google.com/?q=9.9252,78.1432'],
        ['Kavitha Rice Center', '9002345691', '8002345691', 'Mattuthavani', 'Shop No. 159, New Colony, Mattuthavani, Madurai-625107', 'https://maps.google.com/?q=9.8976,78.0876'],
        ['Suresh Trading Company', '9002345692', '8002345692', 'Arapalayam', 'Shop No. 170, Main Road, Arapalayam, Madurai-625016', 'https://maps.google.com/?q=9.9065,78.0754'],
        ['Valli Food Palace', '9002345693', '8002345693', 'Anna Nagar', 'Shop No. 181, Second Street, Anna Nagar, Madurai-625020', 'https://maps.google.com/?q=9.9265,78.1211'],
        ['Bharathi Rice Mill', '9002345694', '8002345694', 'KK Nagar', 'Shop No. 192, Third Avenue, KK Nagar, Madurai-625021', 'https://maps.google.com/?q=9.9187,78.1089'],
        ['Muthu Grocery Hub', '9002345695', '8002345695', 'Sellur', 'Shop No. 203, Fourth Cross, Sellur, Madurai-625002', 'https://maps.google.com/?q=9.9324,78.1599'],
        ['Sangeetha Mini Market', '9002345696', '8002345696', 'Thirunagar', 'Shop No. 214, Fifth Street, Thirunagar, Madurai-625006', 'https://maps.google.com/?q=9.9454,78.1397'],
        ['Venkatesh Mega Store', '9002345697', '8002345697', 'Pasumalai', 'Shop No. 225, Sixth Road, Pasumalai, Madurai-625004', 'https://maps.google.com/?q=9.8854,78.0944']
    ];

    foreach($shopCustomers as $customer) {
        $stmt = $conn->prepare("INSERT INTO tblcustomers (CustomerName, Mobile, AlternativePhone, Area, Address, GmapLocation, CustomerType, CreatedBy, CreatedDate) VALUES (?, ?, ?, ?, ?, ?, 'Shop', 1, NOW())");
        $stmt->bind_param("ssssss", $customer[0], $customer[1], $customer[2], $customer[3], $customer[4], $customer[5]);
        $stmt->execute();
    }
    echo count($shopCustomers) . " shop customers created.\n\n";

    // Add nearby customer relationships
    echo "Setting up nearby customer relationships...\n";
    $conn->query("UPDATE tblcustomers SET NearbyCustomerId = 1 WHERE ID IN (16, 21)");  // Valli A and Sri Murugan Rice Store near Murugan K
    $conn->query("UPDATE tblcustomers SET NearbyCustomerId = 3 WHERE ID IN (18, 23)");  // Muthu V and Raman Rice Mill near Raman P
    $conn->query("UPDATE tblcustomers SET NearbyCustomerId = 5 WHERE ID IN (20, 25)");  // Venkatesh G and Selvam Wholesale Rice near Selvam R
    echo "Nearby relationships established.\n\n";

    // Display final summary
    echo "=== SAMPLE DATA CREATION COMPLETE ===\n";
    
    $result = $conn->query("SELECT COUNT(*) as count FROM tblareas WHERE IsActive=1");
    $count = $result->fetch_assoc()['count'];
    echo "Areas Created: $count\n";
    
    $result = $conn->query("SELECT COUNT(*) as count FROM tblcustomers WHERE CustomerType='Retail' AND IsActive=1");
    $count = $result->fetch_assoc()['count'];
    echo "Retail Customers: $count\n";
    
    $result = $conn->query("SELECT COUNT(*) as count FROM tblcustomers WHERE CustomerType='Shop' AND IsActive=1");
    $count = $result->fetch_assoc()['count'];
    echo "Shop Customers: $count\n";
    
    $result = $conn->query("SELECT COUNT(*) as count FROM tblcustomers WHERE IsActive=1");
    $count = $result->fetch_assoc()['count'];
    echo "Total Customers: $count\n\n";
    
    echo "Sample customers with nearby relationships:\n";
    $result = $conn->query("SELECT c1.CustomerName, c1.CustomerType, c2.CustomerName as NearbyCustomer FROM tblcustomers c1 LEFT JOIN tblcustomers c2 ON c1.NearbyCustomerId = c2.ID WHERE c1.NearbyCustomerId IS NOT NULL");
    while($row = $result->fetch_assoc()) {
        echo "- {$row['CustomerName']} ({$row['CustomerType']}) is near {$row['NearbyCustomer']}\n";
    }
    
    echo "\nAll features are ready for testing:\n";
    echo "✓ Nearby customer selection with auto-fill\n";
    echo "✓ Type-to-search functionality on all dropdowns\n";
    echo "✓ Tamil customer names and Madurai locations\n";
    echo "✓ 20 retail + 20 shop customers across 15 areas\n";

} catch(Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

$conn->close();
?>