-- =============================================================================
-- عقد 14 — إصلاح الزيارات + إدراج SupVisitTrapLines
-- نفّذ الملف كاملاً دفعة واحدة في phpMyAdmin
-- =============================================================================

SET @contract_id = 14;
SET @date_start  = '2026-01-06';
SET @date_end    = CURDATE();

SET @cust_name = (SELECT `CustName` FROM `Contracts` WHERE `ID` = @contract_id LIMIT 1);
SET @indoor_cnt = (SELECT COALESCE(`indoor_trap_count`, 1) 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);

-- ─── تشخيص 1: العقد ─────────────────────────────────────────────────────────
SELECT @contract_id AS contract_id, @cust_name AS contract_name,
       @indoor_cnt AS indoor_cnt, @outdoor_cnt AS outdoor_cnt;

-- ─── تشخيص 2: كل الزيارات المحتملة لهذا العقد ───────────────────────────────
SELECT v.`id`, v.`cust_name`, v.`visit_date`, v.`trap_inspected`,
       v.`trap_indoor_count`, v.`sup_name`,
       CASE WHEN BINARY TRIM(v.`cust_name`) = BINARY TRIM(@cust_name) THEN 'exact'
            WHEN v.`cust_name` LIKE '%جادرية%' THEN 'like'
            ELSE 'no' END AS match_type
FROM `SupVisits` v
WHERE v.`visit_date` >= @date_start
  AND v.`visit_date` <= @date_end
  AND (
    BINARY TRIM(v.`cust_name`) = BINARY TRIM(@cust_name)
    OR v.`cust_name` LIKE '%جادرية%'
  )
ORDER BY v.`visit_date`, v.`id`;

-- ─── خطوة أ: تفعيل فحص الصناديق على الزيارات المطابقة ───────────────────────
UPDATE `SupVisits` v
SET v.`trap_inspected` = 1,
    v.`trap_indoor_count` = GREATEST(1, @indoor_cnt),
    v.`trap_outdoor_count` = GREATEST(0, @outdoor_cnt),
    v.`updated_at` = NOW()
WHERE v.`visit_date` >= @date_start
  AND v.`visit_date` <= @date_end
  AND (
    BINARY TRIM(v.`cust_name`) = BINARY TRIM(@cust_name)
    OR v.`cust_name` LIKE '%جادرية%'
  );

-- ─── تشخيص 3: بعد التحديث ──────────────────────────────────────────────────
SELECT COUNT(*) AS visits_ready
FROM `SupVisits` v
WHERE v.`visit_date` >= @date_start
  AND v.`visit_date` <= @date_end
  AND v.`trap_inspected` = 1
  AND (
    BINARY TRIM(v.`cust_name`) = BINARY TRIM(@cust_name)
    OR v.`cust_name` LIKE '%جادرية%'
  );

-- ─── خطوة ب: أبو بريص (gecko) — كل زيارة ثالثة ─────────────────────────────
INSERT INTO `SupVisitTrapLines` (
  `VisitID`, `CustName`, `VisitDate`, `SupName`,
  `BoxType`, `BoxNo`, `FindingCode`, `FindingLabel`, `BaitPercent`
)
SELECT
  s.`id`,
  s.`cust_name`,
  s.`visit_date`,
  s.`sup_name`,
  'indoor',
  s.`box_no`,
  'gecko',
  'وجد أبو بريص',
  NULL
FROM (
  SELECT
    v.`id`,
    @cust_name AS `cust_name`,
    v.`visit_date`,
    COALESCE(NULLIF(TRIM(v.`sup_name`), ''), 'مشرف عمل') AS `sup_name`,
    CAST(1 + MOD(r.`rn` - 1, GREATEST(1, @indoor_cnt)) AS CHAR) AS `box_no`,
    r.`rn`
  FROM `SupVisits` v
  INNER JOIN (
    SELECT v2.`id`,
           ROW_NUMBER() OVER (ORDER BY v2.`visit_date`, v2.`id`) AS `rn`
    FROM `SupVisits` v2
    WHERE v2.`visit_date` >= @date_start
      AND v2.`visit_date` <= @date_end
      AND v2.`trap_inspected` = 1
      AND (
        BINARY TRIM(v2.`cust_name`) = BINARY TRIM(@cust_name)
        OR v2.`cust_name` LIKE '%جادرية%'
      )
  ) r ON r.`id` = v.`id`
  WHERE MOD(r.`rn`, 3) = 1
) s
WHERE NOT EXISTS (
  SELECT 1 FROM `SupVisitTrapLines` x
  WHERE x.`VisitID` = s.`id`
    AND x.`BoxType` = 'indoor'
    AND x.`FindingCode` = 'gecko'
);

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

-- ─── نتيجة ───────────────────────────────────────────────────────────────────
SELECT COUNT(*) AS total_trap_lines
FROM `SupVisitTrapLines` tl
INNER JOIN `SupVisits` v ON v.`id` = tl.`VisitID`
WHERE v.`visit_date` >= @date_start
  AND v.`visit_date` <= @date_end
  AND (
    BINARY TRIM(v.`cust_name`) = BINARY TRIM(@cust_name)
    OR v.`cust_name` LIKE '%جادرية%'
  );

SELECT tl.`ID`, tl.`VisitID`, tl.`VisitDate`, tl.`BoxNo`, tl.`FindingCode`, tl.`FindingLabel`
FROM `SupVisitTrapLines` tl
INNER JOIN `SupVisits` v ON v.`id` = tl.`VisitID`
WHERE v.`visit_date` >= @date_start
  AND v.`visit_date` <= @date_end
  AND (
    BINARY TRIM(v.`cust_name`) = BINARY TRIM(@cust_name)
    OR v.`cust_name` LIKE '%جادرية%'
  )
ORDER BY tl.`VisitDate`, tl.`BoxNo`
LIMIT 50;