USE db_field_reporting;

SET @rsm_user_id := (SELECT id FROM users WHERE username = 'rsm1' LIMIT 1);
SET @admin_user_id := (SELECT id FROM users WHERE username = 'admin' LIMIT 1);

DELETE FROM user_agent_assignments
WHERE agent_name IN ('Alpha Traders', 'Beta Distribution', 'Gamma Enterprise');

DELETE FROM agentsmrd
WHERE sap_code IN ('DEMO001', 'DEMO002', 'DEMO003');

INSERT INTO agentsmrd (
    zone_order, sap_code, chanelname, sm_id, rm_id, zone, region, sub_region, arm_name, asm_name,
    rsm_name, executive_name, agent_name, agent_replc, agent_replcnames, agent_type, range_type,
    rep_name, territory_name, stores_name, stores_type, user_names, user_dates, dbaddres, dbcontact, dis_rates
) VALUES
(1, 'DEMO001', 'GT', 'SM001', 'RM001', 'North', 'Dhaka North', 'Mirpur', 'ARM Demo 1', 'ASM Demo 1', 'Regional Sales Manager', 'Exec Demo 1', 'Alpha Traders', 'REP001', 'Rahim', 'Exclusive', 'Core', 'RP01', 'Mirpur 10', 'Alpha Main Store', 'Retail', 'system', NOW(), 'House 12, Road 5, Mirpur, Dhaka', '01711000001', 4.50),
(1, 'DEMO002', 'GT', 'SM002', 'RM001', 'North', 'Dhaka North', 'Uttara', 'ARM Demo 1', 'ASM Demo 2', 'Regional Sales Manager', 'Exec Demo 2', 'Beta Distribution', 'REP002', 'Karim', 'Exclusive', 'Core', 'RP02', 'Uttara Sector 7', 'Beta Cash Point', 'Wholesale', 'system', NOW(), 'Sector 7, Uttara, Dhaka', '01711000002', 4.25),
(1, 'DEMO003', 'GT', 'SM003', 'RM001', 'North', 'Gazipur', 'Tongi', 'ARM Demo 2', 'ASM Demo 3', 'Regional Sales Manager', 'Exec Demo 3', 'Gamma Enterprise', 'REP003', 'Salam', 'Exclusive', 'Core', 'RP03', 'Tongi Bazar', 'Gamma Depot', 'Retail', 'system', NOW(), 'Station Road, Tongi, Gazipur', '01711000003', 4.75);

INSERT INTO user_agent_assignments (user_id, agent_name)
SELECT @rsm_user_id, demo.agent_name
FROM (
    SELECT 'Alpha Traders' AS agent_name
    UNION ALL SELECT 'Beta Distribution'
    UNION ALL SELECT 'Gamma Enterprise'
) demo
WHERE @rsm_user_id IS NOT NULL;

DELETE FROM visit_reports
WHERE report_uuid IN (
    '11111111-1111-1111-1111-111111111111',
    '22222222-2222-2222-2222-222222222222'
);

INSERT INTO visit_reports (
    report_uuid, user_id, date_of_visit, agent_name, accompanied_by, visited_type,
    started_at, start_lat, start_lng, ended_at, end_lat, end_lng, status, created_at, updated_at
) VALUES (
    '11111111-1111-1111-1111-111111111111', COALESCE(@rsm_user_id, @admin_user_id), '2026-04-18 10:30:00', 'Alpha Traders', 'ASM Demo 1', 'Review',
    '2026-04-18 10:35:00', 23.8041000, 90.3665000, '2026-04-18 13:10:00', 23.8060000, 90.3702000, 'completed', '2026-04-18 10:35:00', '2026-04-18 13:10:00'
);
SET @report_completed := LAST_INSERT_ID();

INSERT INTO visit_form_progress (report_id, form_no, is_saved, saved_at) VALUES
(@report_completed, 1, 1, '2026-04-18 10:40:00'),
(@report_completed, 2, 1, '2026-04-18 10:55:00'),
(@report_completed, 3, 1, '2026-04-18 11:10:00'),
(@report_completed, 4, 1, '2026-04-18 11:30:00'),
(@report_completed, 5, 1, '2026-04-18 11:45:00'),
(@report_completed, 6, 1, '2026-04-18 12:00:00'),
(@report_completed, 7, 1, '2026-04-18 12:20:00'),
(@report_completed, 8, 1, '2026-04-18 13:10:00');

INSERT INTO report_db_profile (
    report_id, owner_contact, coverage_area, route_strength, vehicle_availability,
    log_book_updated, territory_route_map, territory_route_map_file, route_plan,
    no_credit_bills, credit_bill_total, no_cheques, cheque_total, cash_bank_total,
    progress_sheet_updated, progress_sheet_comment, sku_sales_update, sku_sales_comment,
    store_length, store_width, store_condition, market_return_condition,
    store_table_count, store_chair_count, store_comments, updated_at
) VALUES (
    @report_completed, 'Md. Ashraf / 01711000001', 'Mirpur 10 / Dhaka North', '12 active routes', '2 vans + 1 pickup',
    'Yes', 'No', NULL, 'Yes',
    5, 85000.00, 3, 54000.00, 125000.00,
    'Yes', 'Progress sheet updated till yesterday', 'High', 'Top 20 SKUs reviewed and displayed',
    28.50, 16.00, 'Clean store with clear product zoning', 'Minor biscuit return due to crush damage',
    4, 8, 'Owner requested more chocolate visibility near counter', '2026-04-18 10:55:00'
);

INSERT INTO report_sales_snapshot (report_id, category_name, mtd_target, mtd_achievement, variance, status_remarks) VALUES
(@report_completed, 'Biscuits', 120000.00, 126500.00, 6500.00, 'Achieved / On track'),
(@report_completed, 'Gem', 40000.00, 34500.00, -5500.00, 'Moderate gap'),
(@report_completed, 'Cracker', 55000.00, 51000.00, -4000.00, 'Moderate gap'),
(@report_completed, 'Chocolate', 68000.00, 72100.00, 4100.00, 'Achieved / On track'),
(@report_completed, 'Snacks', 47000.00, 29000.00, -18000.00, 'Needs attention'),
(@report_completed, 'Wafer', 30000.00, 30500.00, 500.00, 'Achieved / On track');

INSERT INTO report_stock_status (report_id, product_name, stock_level, system_stock, variance, variance_comment) VALUES
(@report_completed, 'Glucose Biscuit 45g', 120, 110, 10, 'Physical stock slightly above system count'),
(@report_completed, 'Marie Biscuit 100g', 85, 90, -5, 'Pending issue to one retailer'),
(@report_completed, 'Chocolate Wafer 25g', 150, 145, 5, 'Recent top-up received'),
(@report_completed, 'Salt Cracker 65g', 60, 70, -10, 'Damage adjustment not posted'),
(@report_completed, 'Gem Candy Pack', 200, 210, -10, 'Need recount at sub-stock point'),
(@report_completed, 'Mini Cake Choco', 44, 50, -6, 'One damaged carton identified'),
(@report_completed, 'Potato Snacks 20g', 180, 175, 5, 'Floor stock not entered earlier'),
(@report_completed, 'Peanut Bar 35g', 90, 90, 0, 'Balanced'),
(@report_completed, 'Cream Roll Vanilla', 32, 38, -6, 'Slow movement area'),
(@report_completed, 'Coconut Cookies 75g', 76, 70, 6, 'Backroom stock found during visit');

INSERT INTO report_issues (report_id, issue_type, description, severity) VALUES
(@report_completed, 'Stock', 'Snacks category is understocked for 3 fast-moving SKUs.', 'High'),
(@report_completed, 'Distribution', 'Two fringe outlets were missed in the last route cycle.', 'Medium');

INSERT INTO report_actions_db (report_id, action_name, comments) VALUES
(@report_completed, 'Increase sales focus', 'Push Gem and Snacks through counter display and retailer incentive.'),
(@report_completed, 'Expedite stock replenishment', 'Raise emergency replenishment for snacks within 48 hours.'),
(@report_completed, 'Improve outlet coverage', 'Cover missed fringe outlets every Tuesday and Friday.');

INSERT INTO report_actions_staff (report_id, staff_role, comments) VALUES
(@report_completed, 'ASM', 'Review weekly call compliance and route adherence.'),
(@report_completed, 'ASE', 'Verify all damaged stock adjustments before next closing.'),
(@report_completed, 'CSR', 'Monitor cheque realization and update overdue list daily.');

INSERT INTO report_follow_ups (report_id, action_name, responsible_person, deadline_period) VALUES
(@report_completed, 'Replenish snacks stock', 'ASM Demo 1', '1 month'),
(@report_completed, 'Recover missed outlet coverage', 'Exec Demo 1', '3 months');

INSERT INTO report_general_remarks (report_id, remark, prepared_by_signature, distributor_signature, employee_signature, updated_at) VALUES
(@report_completed, 'Overall operations are stable. Immediate improvement needed in snacks availability and fringe outlet service.', NULL, NULL, NULL, '2026-04-18 13:10:00');

INSERT INTO visit_reports (
    report_uuid, user_id, date_of_visit, agent_name, accompanied_by, visited_type,
    started_at, start_lat, start_lng, ended_at, end_lat, end_lng, status, created_at, updated_at
) VALUES (
    '22222222-2222-2222-2222-222222222222', COALESCE(@rsm_user_id, @admin_user_id), '2026-04-20 09:15:00', 'Beta Distribution', 'ASM Demo 2', 'Audit',
    '2026-04-20 09:20:00', 23.8751000, 90.3982000, NULL, NULL, NULL, 'draft', '2026-04-20 09:20:00', '2026-04-20 11:00:00'
);
SET @report_draft := LAST_INSERT_ID();

INSERT INTO visit_form_progress (report_id, form_no, is_saved, saved_at) VALUES
(@report_draft, 1, 1, '2026-04-20 09:25:00'),
(@report_draft, 2, 1, '2026-04-20 09:45:00'),
(@report_draft, 3, 1, '2026-04-20 10:05:00'),
(@report_draft, 4, 1, '2026-04-20 10:30:00');

INSERT INTO report_db_profile (
    report_id, owner_contact, coverage_area, route_strength, vehicle_availability,
    log_book_updated, territory_route_map, territory_route_map_file, route_plan,
    no_credit_bills, credit_bill_total, no_cheques, cheque_total, cash_bank_total,
    progress_sheet_updated, progress_sheet_comment, sku_sales_update, sku_sales_comment,
    store_length, store_width, store_condition, market_return_condition,
    store_table_count, store_chair_count, store_comments, updated_at
) VALUES (
    @report_draft, 'Mr. Belal / 01711000002', 'Uttara Sector 7 / Dhaka North', '9 active routes', '1 covered van',
    'Yes', 'No', NULL, 'Yes',
    2, 22000.00, 1, 18000.00, 36000.00,
    'No', 'Pending update after route reconciliation', 'Moderate', 'Chocolate and wafer movement average',
    22.00, 14.00, 'Store is organized but backroom is congested', 'No major return issue',
    3, 6, 'Audit still open for a few system mismatches', '2026-04-20 09:45:00'
);

INSERT INTO report_sales_snapshot (report_id, category_name, mtd_target, mtd_achievement, variance, status_remarks) VALUES
(@report_draft, 'Biscuits', 90000.00, 72000.00, -18000.00, 'Moderate gap'),
(@report_draft, 'Gem', 35000.00, 26000.00, -9000.00, 'Needs attention'),
(@report_draft, 'Cracker', 42000.00, 39000.00, -3000.00, 'Moderate gap'),
(@report_draft, 'Chocolate', 50000.00, 48000.00, -2000.00, 'Moderate gap'),
(@report_draft, 'Snacks', 38000.00, 31000.00, -7000.00, 'Moderate gap'),
(@report_draft, 'Wafer', 26000.00, 25500.00, -500.00, 'Moderate gap');

INSERT INTO report_stock_status (report_id, product_name, stock_level, system_stock, variance, variance_comment) VALUES
(@report_draft, 'Glucose Biscuit 45g', 70, 75, -5, 'System needs recount'),
(@report_draft, 'Marie Biscuit 100g', 60, 55, 5, 'Recent stock received'),
(@report_draft, 'Chocolate Wafer 25g', 80, 95, -15, 'Short supply'),
(@report_draft, 'Salt Cracker 65g', 40, 42, -2, 'Acceptable gap'),
(@report_draft, 'Gem Candy Pack', 110, 120, -10, 'Slow inward posting'),
(@report_draft, 'Mini Cake Choco', 25, 28, -3, 'Damage check pending'),
(@report_draft, 'Potato Snacks 20g', 90, 100, -10, 'Fast movement'),
(@report_draft, 'Peanut Bar 35g', 54, 54, 0, 'Balanced'),
(@report_draft, 'Cream Roll Vanilla', 20, 24, -4, 'Need replenishment'),
(@report_draft, 'Coconut Cookies 75g', 34, 30, 4, 'Good availability');
