-- =============================================================================
-- العقد 14 — مطعم الجادرية العائم  بغداد
-- =============================================================================
-- السبب السابق للفشل: الإدراج كان يستخدم أعمدة trap_* غير موجودة بعد الحذف.
-- الاختبار نجح بدونها — هذا الملف يطابق نفس طريقة الاختبار.
-- =============================================================================
-- الخطوة 0 (مرة واحدة فقط إن لم تُنفَّذ): شغّل manual_trap_columns.sql
-- =============================================================================
-- ⚠️ phpMyAdmin: حدّد كل الملف → Go
-- =============================================================================

SELECT `ID`, `CustName`, `indoor_trap_count` FROM `Contracts` WHERE `ID` = 14;

-- حذف بيانات التجربة فقط (note يبدأ بـ [seed])
DELETE tl FROM `SupVisitTrapLines` tl
INNER JOIN `SupVisits` v ON v.`id` = tl.`VisitID`
WHERE v.`cust_name` = 'مطعم الجادرية العائم  بغداد'
  AND v.`note` LIKE '[seed]%';

DELETE FROM `SupVisits`
WHERE `cust_name` = 'مطعم الجادرية العائم  بغداد'
  AND `note` LIKE '[seed]%';

-- إدراج الزيارات — نفس أعمدة الاختبار الذي نجح معك
INSERT INTO `SupVisits` (
  `entered_by_user_id`, `cust_name`, `cust_phone`,
  `visit_date`, `visit_time`, `sup_name`, `note`,
  `created_at`, `updated_at`
)
SELECT
  1,
  'مطعم الجادرية العائم  بغداد',
  '',
  DATE_ADD('2026-01-06', INTERVAL (s.`n` * 7) DAY),
  '10:00:00',
  'مشرف عمل',
  CONCAT('[seed] ', DATE_FORMAT(DATE_ADD('2026-01-06', INTERVAL (s.`n` * 7) DAY), '%Y-%m-%d')),
  NOW(),
  NOW()
FROM (
  SELECT 0 AS `n` UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
  SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
  SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
  SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
  SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL
  SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
) s
WHERE DATE_ADD('2026-01-06', INTERVAL (s.`n` * 7) DAY) <= CURDATE();

-- تفعيل فحص الصناديق (يتطلب أعمدة trap_* من manual_trap_columns.sql)
UPDATE `SupVisits` v
CROSS JOIN `Contracts` c
SET
  v.`trap_inspected` = 1,
  v.`trap_indoor_count` = GREATEST(1, IFNULL(c.`indoor_trap_count`, 1)),
  v.`trap_outdoor_count` = IFNULL(c.`outdoor_trap_count`, 0),
  v.`updated_at` = NOW()
WHERE c.`ID` = 14
  AND v.`cust_name` = 'مطعم الجادرية العائم  بغداد'
  AND v.`note` LIKE '[seed]%';

-- صناديق: أبو بريص
INSERT INTO `SupVisitTrapLines` (
  `VisitID`, `CustName`, `VisitDate`, `SupName`,
  `BoxType`, `BoxNo`, `FindingCode`, `FindingLabel`, `BaitPercent`
)
SELECT
  v.`id`,
  'مطعم الجادرية العائم  بغداد',
  v.`visit_date`,
  v.`sup_name`,
  'indoor', '3', 'gecko', 'وجد أبو بريص', NULL
FROM `SupVisits` v
INNER JOIN (
  SELECT 0 AS `n` UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
  SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
  SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
  SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
  SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL
  SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
) s ON v.`visit_date` = DATE_ADD('2026-01-06', INTERVAL (s.`n` * 7) DAY)
WHERE v.`cust_name` = 'مطعم الجادرية العائم  بغداد'
  AND v.`note` LIKE '[seed]%'
  AND MOD(s.`n`, 3) = 0;

-- صناديق: فار
INSERT INTO `SupVisitTrapLines` (
  `VisitID`, `CustName`, `VisitDate`, `SupName`,
  `BoxType`, `BoxNo`, `FindingCode`, `FindingLabel`, `BaitPercent`
)
SELECT
  v.`id`,
  'مطعم الجادرية العائم  بغداد',
  v.`visit_date`,
  v.`sup_name`,
  'indoor', '7', 'rat', 'وجد فار / قوارض', NULL
FROM `SupVisits` v
INNER JOIN (
  SELECT 0 AS `n` UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
  SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
  SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
  SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
  SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL
  SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
) s ON v.`visit_date` = DATE_ADD('2026-01-06', INTERVAL (s.`n` * 7) DAY)
WHERE v.`cust_name` = 'مطعم الجادرية العائم  بغداد'
  AND v.`note` LIKE '[seed]%'
  AND MOD(s.`n`, 4) = 3;

-- نتيجة
SELECT COUNT(*) AS `زيارات` FROM `SupVisits`
WHERE `cust_name` = 'مطعم الجادرية العائم  بغداد' AND `note` LIKE '[seed]%';

SELECT COUNT(*) AS `صناديق` FROM `SupVisitTrapLines`
WHERE `CustName` = 'مطعم الجادرية العائم  بغداد';

SELECT v.`visit_date`, v.`trap_inspected`, tl.`BoxNo`, tl.`FindingCode`
FROM `SupVisits` v
LEFT JOIN `SupVisitTrapLines` tl ON tl.`VisitID` = v.`id`
WHERE v.`note` LIKE '[seed]%'
ORDER BY v.`visit_date`, tl.`BoxNo`;