-- Complete Database Setup for Madurai Rice Mill
-- Clear existing data and create fresh sample data

USE inba_rice;

-- Clear all existing data
DELETE FROM tblcustomerpayments;
DELETE FROM tblcustomerreminders;
DELETE FROM tblcustomers;
DELETE FROM tblareas;

-- Insert Madurai Areas
INSERT INTO tblareas (AreaName, Description, IsActive) VALUES
('Anna Nagar', 'Anna Nagar - Madurai', 1),
('KK Nagar', 'KK Nagar - Madurai', 1),
('Sellur', 'Sellur - Madurai', 1),
('Thirunagar', 'Thirunagar - Madurai', 1),
('Pasumalai', 'Pasumalai - Madurai', 1),
('Vilangudi', 'Vilangudi - Madurai', 1),
('Simmakkal', 'Simmakkal - Madurai', 1),
('Goripalayam', 'Goripalayam - Madurai', 1),
('Meenakshi Amman College', 'Meenakshi Amman College Area', 1),
('Tallakulam', 'Tallakulam - Madurai', 1),
('Madurai East', 'Madurai East', 1),
('Madurai West', 'Madurai West', 1),
('Periyar', 'Periyar Bus Stand Area', 1),
('Mattuthavani', 'Mattuthavani - Madurai', 1),
('Arapalayam', 'Arapalayam - Madurai', 1);

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

-- Insert 20 Shop Customers
INSERT INTO tblcustomers (CustomerName, Mobile, AlternativePhone, Area, Address, GmapLocation, CustomerType, CreatedBy, CreatedDate) VALUES
('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', 'Shop', 1, NOW()),
('Lakshmi Provisions', '9002345679', '8002345679', 'KK Nagar', 'Shop No. 27, Bharathi Street, KK Nagar, Madurai-625021', 'https://maps.google.com/?q=9.9195,78.1077', 'Shop', 1, NOW()),
('Raman Rice Mill', '9002345680', '8002345680', 'Sellur', 'Shop No. 38, Periyar Road, Sellur, Madurai-625002', 'https://maps.google.com/?q=9.9312,78.1587', 'Shop', 1, NOW()),
('Kamala Grocery Store', '9002345681', '8002345681', 'Thirunagar', 'Shop No. 49, Thiruvalluvar Street, Thirunagar, Madurai-625006', 'https://maps.google.com/?q=9.9442,78.1385', 'Shop', 1, NOW()),
('Selvam Wholesale Rice', '9002345682', '8002345682', 'Pasumalai', 'Shop No. 60, Kamaraj Road, Pasumalai, Madurai-625004', 'https://maps.google.com/?q=9.8842,78.0932', 'Shop', 1, NOW()),
('Priya Departmental Store', '9002345683', '8002345683', 'Vilangudi', 'Shop No. 71, Netaji Road, Vilangudi, Madurai-625009', 'https://maps.google.com/?q=9.8912,78.1432', 'Shop', 1, NOW()),
('Kumar Food Mart', '9002345684', '8002345684', 'Simmakkal', 'Shop No. 82, Market Street, Simmakkal, Madurai-625001', 'https://maps.google.com/?q=9.9195,78.1198', 'Shop', 1, NOW()),
('Meera General Stores', '9002345685', '8002345685', 'Goripalayam', 'Shop No. 93, Temple Street, Goripalayam, Madurai-625002', 'https://maps.google.com/?q=9.9098,78.1289', 'Shop', 1, NOW()),
('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', 'Shop', 1, NOW()),
('Sita Super Market', '9002345687', '8002345687', 'Tallakulam', 'Shop No. 115, River View Street, Tallakulam, Madurai-625002', 'https://maps.google.com/?q=9.9187,78.1543', 'Shop', 1, NOW()),
('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', 'Shop', 1, NOW()),
('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', 'Shop', 1, NOW()),
('Ganesh Wholesale Market', '9002345690', '8002345690', 'Periyar', 'Shop No. 148, Bus Stand Road, Periyar, Madurai-625007', 'https://maps.google.com/?q=9.9252,78.1432', 'Shop', 1, NOW()),
('Kavitha Rice Center', '9002345691', '8002345691', 'Mattuthavani', 'Shop No. 159, New Colony, Mattuthavani, Madurai-625107', 'https://maps.google.com/?q=9.8976,78.0876', 'Shop', 1, NOW()),
('Suresh Trading Company', '9002345692', '8002345692', 'Arapalayam', 'Shop No. 170, Main Road, Arapalayam, Madurai-625016', 'https://maps.google.com/?q=9.9065,78.0754', 'Shop', 1, NOW()),
('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', 'Shop', 1, NOW()),
('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', 'Shop', 1, NOW()),
('Muthu Grocery Hub', '9002345695', '8002345695', 'Sellur', 'Shop No. 203, Fourth Cross, Sellur, Madurai-625002', 'https://maps.google.com/?q=9.9324,78.1599', 'Shop', 1, NOW()),
('Sangeetha Mini Market', '9002345696', '8002345696', 'Thirunagar', 'Shop No. 214, Fifth Street, Thirunagar, Madurai-625006', 'https://maps.google.com/?q=9.9454,78.1397', 'Shop', 1, NOW()),
('Venkatesh Mega Store', '9002345697', '8002345697', 'Pasumalai', 'Shop No. 225, Sixth Road, Pasumalai, Madurai-625004', 'https://maps.google.com/?q=9.8854,78.0944', 'Shop', 1, NOW());

-- Add nearby customer relationships to demonstrate the feature
UPDATE tblcustomers SET NearbyCustomerId = 1 WHERE ID IN (16, 21);  -- Valli A and Sri Murugan Rice Store near Murugan K
UPDATE tblcustomers SET NearbyCustomerId = 3 WHERE ID IN (18, 23);  -- Muthu V and Raman Rice Mill near Raman P
UPDATE tblcustomers SET NearbyCustomerId = 5 WHERE ID IN (20, 25);  -- Venkatesh G and Selvam Wholesale Rice near Selvam R

-- Display summary
SELECT 'DATA CREATION COMPLETE' as Status;
SELECT 'Areas Created' as Summary, COUNT(*) as Count FROM tblareas WHERE IsActive=1;
SELECT 'Retail Customers' as Summary, COUNT(*) as Count FROM tblcustomers WHERE CustomerType='Retail' AND IsActive=1;
SELECT 'Shop Customers' as Summary, COUNT(*) as Count FROM tblcustomers WHERE CustomerType='Shop' AND IsActive=1;
SELECT 'Total Customers' as Summary, COUNT(*) as Count FROM tblcustomers WHERE IsActive=1;