-- Default Data Seeder for Textile Manager
-- This file contains default/sample data for development and testing

-- ============================================================================
-- DEFAULT TAILORING ITEMS
-- ============================================================================
INSERT INTO tailoring_items (name, category, sewing_price, artisan_wage, description, estimated_time_hours, is_active, shop_id) VALUES
('Pant', 'Men', 800.00, 250.00, 'Regular fit pants for men', 8, TRUE, 1),
('Shirt', 'Men', 600.00, 200.00, 'Formal shirt for men', 6, TRUE, 1),
('Suit', 'Men', 5000.00, 1500.00, 'Complete 3-piece suit', 24, TRUE, 1),
('Blazer', 'Men', 3000.00, 900.00, 'Formal blazer', 16, TRUE, 1),
('Kurta', 'Men', 800.00, 250.00, 'Traditional kurta', 6, TRUE, 1),

('Salwar Kameez', 'Women', 1200.00, 400.00, 'Traditional salwar kameez set', 10, TRUE, 1),
('Blouse', 'Women', 500.00, 150.00, 'Saree blouse', 4, TRUE, 1),
('Lehenga', 'Women', 4000.00, 1200.00, 'Designer lehenga', 20, TRUE, 1),
('Kurti', 'Women', 700.00, 220.00, 'Casual kurti', 5, TRUE, 1),
('Dress', 'Women', 1500.00, 450.00, 'Western dress', 8, TRUE, 1),

('Kids Frock', 'Kids', 700.00, 220.00, 'Party frock for girls', 4, TRUE, 1),
('Kids Shirt', 'Kids', 400.00, 120.00, 'Casual shirt for boys', 3, TRUE, 1),
('Kids Pant', 'Kids', 500.00, 150.00, 'Regular pants for kids', 3, TRUE, 1),
('School Uniform', 'Kids', 800.00, 240.00, 'Complete school uniform set', 6, TRUE, 1),

('Alterations', 'Other', 200.00, 60.00, 'Basic alterations and repairs', 2, TRUE, 1),
('Hemming', 'Other', 150.00, 45.00, 'Hemming services', 1, TRUE, 1),
('Zipper Repair', 'Other', 100.00, 30.00, 'Zipper replacement/repair', 1, TRUE, 1),
('Button Work', 'Other', 50.00, 15.00, 'Button attachment/replacement', 1, TRUE, 1)
ON DUPLICATE KEY UPDATE id=id;

-- ============================================================================
-- DEFAULT INVENTORY ITEMS
-- ============================================================================
INSERT INTO inventory (name, type, color, price_per_meter, quantity, low_stock_threshold, supplier, shop_id) VALUES
-- Cotton fabrics
('Premium Cotton', 'Cotton', 'White', 120.00, 50.00, 10.00, 'Local Supplier', 1),
('Cotton Twill', 'Cotton', 'Navy Blue', 150.00, 30.00, 8.00, 'Local Supplier', 1),
('Cotton Poplin', 'Cotton', 'Light Blue', 130.00, 25.00, 8.00, 'Local Supplier', 1),
('Cotton Chambray', 'Cotton', 'Denim Blue', 140.00, 20.00, 5.00, 'Local Supplier', 1),
('Cotton Khadi', 'Cotton', 'Cream', 180.00, 15.00, 5.00, 'Khadi Store', 1),

-- Silk fabrics
('Pure Silk', 'Silk', 'Golden', 800.00, 10.00, 3.00, 'Silk House', 1),
('Silk Georgette', 'Silk', 'Pink', 600.00, 8.00, 2.00, 'Silk House', 1),
('Silk Chiffon', 'Silk', 'Red', 700.00, 6.00, 2.00, 'Silk House', 1),
('Raw Silk', 'Silk', 'Maroon', 500.00, 12.00, 3.00, 'Silk House', 1),

-- Wool fabrics
('Wool Suiting', 'Wool', 'Charcoal', 1200.00, 8.00, 2.00, 'Wool Mart', 1),
('Wool Tweed', 'Wool', 'Brown', 1000.00, 5.00, 2.00, 'Wool Mart', 1),
('Merino Wool', 'Wool', 'Black', 1500.00, 4.00, 1.00, 'Premium Fabrics', 1),

-- Linen fabrics
('Pure Linen', 'Linen', 'Beige', 400.00, 15.00, 5.00, 'Linen Co.', 1),
('Linen Cotton', 'Linen', 'White', 350.00, 20.00, 5.00, 'Linen Co.', 1),
('Linen Blend', 'Linen', 'Grey', 300.00, 18.00, 5.00, 'Linen Co.', 1)
ON DUPLICATE KEY UPDATE id=id;

-- ============================================================================
-- SAMPLE CUSTOMERS (for development/testing)
-- ============================================================================
INSERT INTO customers (name, phone, email, address, shop_id, total_orders, total_spent, notes) VALUES
('Rajesh Kumar', '+91-9876543210', 'rajesh@email.com', '123 MG Road, Bangalore', 1, 0, 0.00, 'Regular customer, prefers cotton fabrics'),
('Priya Sharma', '+91-9876543211', 'priya@email.com', '456 Brigade Road, Bangalore', 1, 0, 0.00, 'Likes traditional wear'),
('Amit Patel', '+91-9876543212', 'amit@email.com', '789 Commercial Street, Bangalore', 1, 0, 0.00, 'Corporate client, bulk orders'),
('Sunita Devi', '+91-9876543213', 'sunita@email.com', '321 Jayanagar, Bangalore', 1, 0, 0.00, 'Wedding orders specialist'),
('Vikram Singh', '+91-9876543214', 'vikram@email.com', '654 Koramangala, Bangalore', 1, 0, 0.00, 'Prefers premium fabrics')
ON DUPLICATE KEY UPDATE id=id;

-- ============================================================================
-- SAMPLE EMPLOYEES
-- ============================================================================
INSERT INTO employees (name, phone, email, role, monthly_salary, hire_date, is_active, address, shop_id) VALUES
('Master Tailor Ram', '+91-9876543220', 'ram@shop.com', 'Tailor', 25000.00, '2023-01-15', TRUE, 'Tailor Colony, Bangalore', 1),
('Cutting Master Shyam', '+91-9876543221', 'shyam@shop.com', 'Cutter', 22000.00, '2023-02-01', TRUE, 'Fabric Market, Bangalore', 1),
('Assistant Gita', '+91-9876543222', 'gita@shop.com', 'Assistant', 15000.00, '2023-03-10', TRUE, 'Near Shop, Bangalore', 1),
('Manager Suresh', '+91-9876543223', 'suresh@shop.com', 'Manager', 35000.00, '2022-12-01', TRUE, 'Manager Quarters, Bangalore', 1),
('Junior Tailor Ravi', '+91-9876543224', 'ravi@shop.com', 'Tailor', 18000.00, '2023-06-15', TRUE, 'Workers Area, Bangalore', 1)
ON DUPLICATE KEY UPDATE id=id;

-- ============================================================================
-- EXPENSE CATEGORIES (Default)
-- ============================================================================
INSERT INTO expense_transactions (category, amount, description, transaction_date, shop_id, is_editable) VALUES
('Rent', 0.00, 'Monthly shop rent', CURDATE(), 1, FALSE),
('Utilities', 0.00, 'Electricity, water, internet', CURDATE(), 1, FALSE),
('Salary Advance', 0.00, 'Employee salary advances', CURDATE(), 1, FALSE),
('Supplies', 0.00, 'Thread, buttons, zippers etc.', CURDATE(), 1, FALSE),
('Marketing', 0.00, 'Advertising and promotion', CURDATE(), 1, FALSE),
('Maintenance', 0.00, 'Equipment and shop maintenance', CURDATE(), 1, FALSE),
('Transportation', 0.00, 'Delivery and pickup costs', CURDATE(), 1, FALSE),
('Other', 0.00, 'Miscellaneous expenses', CURDATE(), 1, FALSE)
ON DUPLICATE KEY UPDATE id=id;

-- ============================================================================
-- INCOME CATEGORIES (Default)
-- ============================================================================
INSERT INTO income_transactions (category, amount, description, transaction_date, shop_id, is_editable) VALUES
('Tailoring Services', 0.00, 'Revenue from tailoring work', CURDATE(), 1, FALSE),
('Alterations', 0.00, 'Revenue from alteration services', CURDATE(), 1, FALSE),
('Rush Orders', 0.00, 'Premium charges for urgent orders', CURDATE(), 1, FALSE),
('Fabric Sales', 0.00, 'Revenue from fabric sales', CURDATE(), 1, FALSE),
('Consultation', 0.00, 'Design consultation fees', CURDATE(), 1, FALSE),
('Other', 0.00, 'Other income sources', CURDATE(), 1, FALSE)
ON DUPLICATE KEY UPDATE id=id;

-- ============================================================================
-- DEFAULT SETTINGS
-- ============================================================================
INSERT INTO appearance_settings (theme, font, shop_id) VALUES
('light', 'inter', 1)
ON DUPLICATE KEY UPDATE theme=theme;

INSERT INTO notification_settings (
    sms_enabled, sms_provider, sms_message_template,
    whatsapp_enabled, whatsapp_message_template, shop_id
) VALUES (
    FALSE, 'twilio', 
    'Dear {customerName}, your order #{orderId} is ready for delivery. Total due: {dueAmount}. Thank you, {shopName}.',
    FALSE,
    'Dear {customerName}, your order *#{orderId}* is ready for delivery. Total due: *{dueAmount}*. Thank you, *{shopName}*.',
    1
) ON DUPLICATE KEY UPDATE sms_enabled=sms_enabled;

-- ============================================================================
-- SAMPLE ORDERS (for development/testing)
-- ============================================================================
INSERT INTO orders (
    memo_id, customer_id, customer_name, customer_phone, type, status,
    order_date, delivery_date, total_amount, paid_amount, total_artisan_wage,
    artisan_id, artisan_name, priority, notes, shop_id
) VALUES
(
    'ORD001', 1, 'Rajesh Kumar', '+91-9876543210', 'pickup', 'New',
    CURDATE(), DATE_ADD(CURDATE(), INTERVAL 7 DAY), 1400.00, 500.00, 450.00,
    1, 'Master Tailor Ram', 'medium', 'Customer wants premium finish', 1
),
(
    'ORD002', 2, 'Priya Sharma', '+91-9876543211', 'delivery', 'In Progress',
    DATE_SUB(CURDATE(), INTERVAL 2 DAY), DATE_ADD(CURDATE(), INTERVAL 5 DAY), 1200.00, 1200.00, 400.00,
    1, 'Master Tailor Ram', 'high', 'Wedding order - urgent', 1
),
(
    'ORD003', 3, 'Amit Patel', '+91-9876543212', 'pickup', 'Ready for Delivery',
    DATE_SUB(CURDATE(), INTERVAL 5 DAY), CURDATE(), 5000.00, 3000.00, 1500.00,
    1, 'Master Tailor Ram', 'medium', 'Corporate bulk order', 1
)
ON DUPLICATE KEY UPDATE id=id;

-- ============================================================================
-- SAMPLE ORDER ITEMS
-- ============================================================================
INSERT INTO order_items (order_id, name, quantity, price, artisan_wage, measurements, notes) VALUES
-- Order 1 items
(1, 'Shirt', 1, 600.00, 200.00, 'Chest: 40", Length: 30"', 'Slim fit'),
(1, 'Pant', 1, 800.00, 250.00, 'Waist: 32", Length: 42"', 'Regular fit'),

-- Order 2 items
(2, 'Salwar Kameez', 1, 1200.00, 400.00, 'Bust: 36", Length: 44"', 'Traditional style'),

-- Order 3 items
(3, 'Suit', 1, 5000.00, 1500.00, 'Chest: 42", Waist: 34", Length: 32"', '3-piece suit with vest')
ON DUPLICATE KEY UPDATE id=id;

-- ============================================================================
-- SAMPLE TRANSACTIONS
-- ============================================================================
-- Income transactions
INSERT INTO income_transactions (category, amount, description, transaction_date, order_id, payment_method, shop_id) VALUES
('Tailoring Services', 500.00, 'Advance payment for Order ORD001', CURDATE(), 1, 'cash', 1),
('Tailoring Services', 1200.00, 'Full payment for Order ORD002', DATE_SUB(CURDATE(), INTERVAL 1 DAY), 2, 'card', 1),
('Tailoring Services', 3000.00, 'Advance payment for Order ORD003', DATE_SUB(CURDATE(), INTERVAL 3 DAY), 3, 'upi', 1),
('Alterations', 200.00, 'Hemming service', DATE_SUB(CURDATE(), INTERVAL 2 DAY), NULL, 'cash', 1)
ON DUPLICATE KEY UPDATE id=id;

-- Expense transactions
INSERT INTO expense_transactions (category, amount, description, person, transaction_date, payment_method, shop_id) VALUES
('Rent', 15000.00, 'Monthly shop rent', 'Landlord', DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'bank_transfer', 1),
('Utilities', 2500.00, 'Electricity bill', 'BESCOM', DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'upi', 1),
('Supplies', 1200.00, 'Thread and buttons purchase', 'Supplier', DATE_SUB(CURDATE(), INTERVAL 3 DAY), 'cash', 1),
('Salary Advance', 5000.00, 'Advance to Master Tailor', 'Master Tailor Ram', DATE_SUB(CURDATE(), INTERVAL 4 DAY), 'cash', 1)
ON DUPLICATE KEY UPDATE id=id;

-- ============================================================================
-- SAMPLE CUSTOMER MEASUREMENTS
-- ============================================================================
INSERT INTO customer_measurements (customer_id, item_name, measurements, notes, shop_id) VALUES
(1, 'Shirt', 'Chest: 40", Shoulder: 17", Length: 30", Sleeve: 24"', 'Prefers slim fit', 1),
(1, 'Pant', 'Waist: 32", Hip: 38", Length: 42", Thigh: 22"', 'Regular fit preferred', 1),
(2, 'Blouse', 'Bust: 36", Waist: 30", Length: 15", Sleeve: 12"', 'Traditional style', 1),
(2, 'Salwar Kameez', 'Bust: 36", Waist: 30", Hip: 38", Length: 44"', 'Loose fit', 1),
(3, 'Suit', 'Chest: 42", Waist: 34", Hip: 40", Length: 32", Sleeve: 25"', 'Business formal', 1)
ON DUPLICATE KEY UPDATE id=id;

-- ============================================================================
-- UPDATE CUSTOMER STATISTICS
-- ============================================================================
-- Update customer statistics based on orders
UPDATE customers c SET 
    total_orders = (SELECT COUNT(*) FROM orders WHERE customer_id = c.id),
    total_spent = (SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE customer_id = c.id),
    last_order_date = (SELECT MAX(order_date) FROM orders WHERE customer_id = c.id)
WHERE c.shop_id = 1;