-- =============================================================================
-- بيانات تجريبية: زيارات مشرف + فحص صناديق داخلية (أسبوعياً)
-- من يناير 2026 حتى اليوم — لاختبار «تقرير الصناديق الداخلية»
-- =============================================================================
-- ⚠️ نفّذ الأقسام 1→6 دفعة واحدة في phpMyAdmin (المتغيرات @ تضيع بين الجلسات)
-- =============================================================================

-- ─── 0) اختر عقداً ───────────────────────────────────────────────────────────
SELECT `ID`, `CustName`, `special_report`, `indoor_trap_count`, `outdoor_trap_count`
FROM `Contracts`
WHERE `special_report` = 1 AND `indoor_trap_count` > 0
ORDER BY `CustName`
LIMIT 30;

-- ─── 1) الإعداد ─────────────────────────────────────────────────────────────
SET @contract_id = 14;  -- مطعم الجادرية العائم  بغداد
SET @sup_name    = 'مشرف عمل';
SET @entered_by  = 1;
SET @seed_tag    = '[seed-indoor-trap]';
SET @date_start  = '2026-01-06';
SET @date_end    = CURDATE();

SET @cust_name = (
  SELECT `CustName` FROM `Contracts`
  WHERE `ID` = @contract_id AND `special_report` = 1 LIMIT 1
);
SET @indoor_cnt = (
  SELECT COALESCE(`indoor_trap_count`, 0) FROM `Contracts` WHERE `ID` = @contract_id LIMIT 1
);
SET @outdoor_cnt = (
  SELECT COALESCE(`outdoor_trap_count`, 0) FROM `Contracts` WHERE `ID` = @contract_id LIMIT 1
);

SELECT @contract_id AS contract_id, @cust_name AS cust,
       @indoor_cnt AS indoor, @outdoor_cnt AS outdoor, @date_end AS until_date;

-- ─── 2) (اختياري) حذف تجربة سابقة — زيارات الوسم فقط ───────────────────────
DELETE tl FROM `SupVisitTrapLines` tl
INNER JOIN `SupVisits` v ON v.`id` = tl.`VisitID`
INNER JOIN `Contracts` c ON c.`ID` = @contract_id
WHERE @contract_id > 0
  AND BINARY v.`cust_name` = BINARY c.`CustName`
  AND v.`visit_date` >= @date_start
  AND v.`visit_date` <= @date_end
  AND v.`entered_by_user_id` = @entered_by
  AND BINARY v.`sup_name` = BINARY @sup_name;

DELETE v FROM `SupVisits` v
INNER JOIN `Contracts` c ON c.`ID` = @contract_id
WHERE @contract_id > 0
  AND BINARY v.`cust_name` = BINARY c.`CustName`
  AND v.`visit_date` >= @date_start
  AND v.`visit_date` <= @date_end
  AND v.`entered_by_user_id` = @entered_by
  AND BINARY v.`sup_name` = BINARY @sup_name;

-- ─── 3) زيارات أسبوعية + trap_inspected ─────────────────────────────────────
INSERT INTO `SupVisits` (
  `entered_by_user_id`, `cust_name`, `cust_phone`,
  `visit_date`, `visit_time`, `sup_name`, `note`,
  `trap_inspected`, `trap_indoor_count`, `trap_outdoor_count`,
  `created_at`, `updated_at`
)
WITH RECURSIVE `weeks` AS (
  SELECT CAST(@date_start AS DATE) AS `visit_date`
  UNION ALL
  SELECT DATE_ADD(`visit_date`, INTERVAL 7 DAY)
  FROM `weeks`
  WHERE `visit_date` < @date_end
)
SELECT
  @entered_by,
  c.`CustName`,
  '',
  w.`visit_date`,
  '10:00:00',
  @sup_name,
  CONCAT(@seed_tag, ' زيارة أسبوعية ', DATE_FORMAT(w.`visit_date`, '%Y-%m-%d')),
  1,
  GREATEST(1, @indoor_cnt),
  GREATEST(0, @outdoor_cnt),
  NOW(),
  NOW()
FROM `weeks` w
CROSS JOIN `Contracts` c
WHERE c.`ID` = @contract_id
  AND @contract_id > 0
  AND @indoor_cnt > 0
  AND NOT EXISTS (
    SELECT 1 FROM `SupVisits` ex
    WHERE BINARY ex.`cust_name` = BINARY c.`CustName`
      AND ex.`visit_date` = w.`visit_date`
      AND ex.`trap_inspected` = 1
  );

-- ─── 3ب) معاينة: كم زيارة ستُملأ لها صناديق؟ ───────────────────────────────
SELECT COUNT(*) AS visits_for_trap_lines
FROM `SupVisits` v
INNER JOIN `Contracts` c ON c.`ID` = @contract_id
WHERE @contract_id > 0
  AND BINARY v.`cust_name` = BINARY c.`CustName`
  AND v.`trap_inspected` = 1
  AND v.`visit_date` >= @date_start
  AND v.`visit_date` <= @date_end;

-- ─── 4) صناديق: أبو بريص — كل زيارة ثالثة ──────────────────────────────────
INSERT INTO `SupVisitTrapLines` (
  `VisitID`, `CustName`, `VisitDate`, `SupName`,
  `BoxType`, `BoxNo`, `FindingCode`, `FindingLabel`, `BaitPercent`
)
SELECT
  v.`id`,
  v.`cust_name`,
  v.`visit_date`,
  v.`sup_name`,
  'indoor',
  CAST(1 + MOD(v.`rn` - 1, GREATEST(1, @indoor_cnt)) AS CHAR),
  'gecko',
  'وجد أبو بريص',
  NULL
FROM (
  SELECT v2.`id`, v2.`cust_name`, v2.`visit_date`, v2.`sup_name`,
         ROW_NUMBER() OVER (ORDER BY v2.`visit_date`, v2.`id`) AS `rn`
  FROM `SupVisits` v2
  INNER JOIN `Contracts` c ON c.`ID` = @contract_id
  WHERE @contract_id > 0
    AND BINARY v2.`cust_name` = BINARY c.`CustName`
    AND v2.`trap_inspected` = 1
    AND v2.`visit_date` >= @date_start
    AND v2.`visit_date` <= @date_end
) v
WHERE MOD(v.`rn`, 3) = 1
  AND NOT EXISTS (
    SELECT 1 FROM `SupVisitTrapLines` x
    WHERE x.`VisitID` = v.`id`
      AND x.`BoxType` = 'indoor'
      AND BINARY x.`BoxNo` = BINARY CAST(1 + MOD(v.`rn` - 1, GREATEST(1, @indoor_cnt)) AS CHAR)
  );

-- ─── 5) صناديق: فار — كل زيارة رابعة ───────────────────────────────────────
INSERT INTO `SupVisitTrapLines` (
  `VisitID`, `CustName`, `VisitDate`, `SupName`,
  `BoxType`, `BoxNo`, `FindingCode`, `FindingLabel`, `BaitPercent`
)
SELECT
  v.`id`,
  v.`cust_name`,
  v.`visit_date`,
  v.`sup_name`,
  'indoor',
  CAST(1 + MOD(v.`rn`, GREATEST(1, @indoor_cnt)) AS CHAR),
  'rat',
  'وجد فار / قوارض',
  NULL
FROM (
  SELECT v2.`id`, v2.`cust_name`, v2.`visit_date`, v2.`sup_name`,
         ROW_NUMBER() OVER (ORDER BY v2.`visit_date`, v2.`id`) AS `rn`
  FROM `SupVisits` v2
  INNER JOIN `Contracts` c ON c.`ID` = @contract_id
  WHERE @contract_id > 0
    AND BINARY v2.`cust_name` = BINARY c.`CustName`
    AND v2.`trap_inspected` = 1
    AND v2.`visit_date` >= @date_start
    AND v2.`visit_date` <= @date_end
) v
WHERE MOD(v.`rn`, 4) = 0
  AND NOT EXISTS (
    SELECT 1 FROM `SupVisitTrapLines` x
    WHERE x.`VisitID` = v.`id`
      AND x.`BoxType` = 'indoor'
      AND BINARY x.`BoxNo` = BINARY CAST(1 + MOD(v.`rn`, GREATEST(1, @indoor_cnt)) AS CHAR)
  );

-- ─── 6) تحقق ─────────────────────────────────────────────────────────────────
SELECT COUNT(*) AS visit_count
FROM `SupVisits` v
INNER JOIN `Contracts` c ON c.`ID` = @contract_id
WHERE @contract_id > 0
  AND BINARY v.`cust_name` = BINARY c.`CustName`
  AND v.`trap_inspected` = 1
  AND v.`visit_date` >= @date_start
  AND v.`visit_date` <= @date_end;

SELECT COUNT(*) AS trap_line_count
FROM `SupVisitTrapLines` tl
INNER JOIN `SupVisits` v ON v.`id` = tl.`VisitID`
INNER JOIN `Contracts` c ON c.`ID` = @contract_id
WHERE @contract_id > 0
  AND BINARY v.`cust_name` = BINARY c.`CustName`
  AND v.`visit_date` >= @date_start
  AND v.`visit_date` <= @date_end;

SELECT v.`visit_date`,
       CEIL(DAY(v.`visit_date`) / 7) AS week_of_month,
       COUNT(tl.`ID`) AS exception_lines
FROM `SupVisits` v
INNER JOIN `Contracts` c ON c.`ID` = @contract_id
LEFT JOIN `SupVisitTrapLines` tl ON tl.`VisitID` = v.`id` AND tl.`BoxType` = 'indoor'
WHERE @contract_id > 0
  AND BINARY v.`cust_name` = BINARY c.`CustName`
  AND v.`trap_inspected` = 1
  AND v.`visit_date` >= @date_start
  AND v.`visit_date` <= @date_end
GROUP BY v.`id`, v.`visit_date`
ORDER BY v.`visit_date`;