-- KPI Reference Queries (deal-based filters)
-- Replace :date_from, :date_to, :owner_didar_id, :product_id as needed.
-- Date filter is applied on deal creation (register_time / created_at).

-- Common deal filter snippet:
-- DATE(COALESCE(d.register_time, d.created_at)) BETWEEN :date_from AND :date_to
-- AND (:owner_didar_id IS NULL OR d.owner_didar_id = :owner_didar_id)
-- AND (:product_id IS NULL OR EXISTS (
--     SELECT 1 FROM deal_products dp WHERE dp.deal_id = d.id AND dp.product_id = :product_id
-- ))

-- 1) Sales (all payments for deals created in range)
SELECT
    COUNT(DISTINCT d.id) AS deal_count,
    SUM(d.payable_amount) AS total_contract_amount,
    SUM(COALESCE(tp.total_paid, 0)) AS total_collected_amount
FROM deals d
LEFT JOIN (
    SELECT deal_id, SUM(amount) AS total_paid
    FROM transactions
    WHERE status = 'confirmed'
    GROUP BY deal_id
) tp ON tp.deal_id = d.id
WHERE DATE(COALESCE(d.register_time, d.created_at)) BETWEEN :date_from AND :date_to
  AND (:owner_didar_id IS NULL OR d.owner_didar_id = :owner_didar_id)
  AND (:product_id IS NULL OR EXISTS (
      SELECT 1 FROM deal_products dp WHERE dp.deal_id = d.id AND dp.product_id = :product_id
  ));

-- 2) Settled deals (all payments >= payable_amount)
SELECT
    COUNT(DISTINCT d.id) AS deal_count,
    SUM(d.payable_amount) AS total_amount,
    SUM(COALESCE(tp.total_paid, 0)) AS total_collected_amount
FROM deals d
LEFT JOIN (
    SELECT deal_id, SUM(amount) AS total_paid
    FROM transactions
    WHERE status = 'confirmed'
    GROUP BY deal_id
) tp ON tp.deal_id = d.id
WHERE d.payable_amount IS NOT NULL
  AND d.payable_amount > 0
  AND COALESCE(tp.total_paid, 0) >= d.payable_amount
  AND DATE(COALESCE(d.register_time, d.created_at)) BETWEEN :date_from AND :date_to
  AND (:owner_didar_id IS NULL OR d.owner_didar_id = :owner_didar_id)
  AND (:product_id IS NULL OR EXISTS (
      SELECT 1 FROM deal_products dp WHERE dp.deal_id = d.id AND dp.product_id = :product_id
  ));

-- 3) Outstanding balance (all payments < payable_amount)
SELECT
    COUNT(DISTINCT d.id) AS deal_count,
    SUM(d.payable_amount) AS total_contract_amount,
    SUM(COALESCE(tp.total_paid, 0)) AS total_collected,
    SUM(d.payable_amount - COALESCE(tp.total_paid, 0)) AS outstanding_amount
FROM deals d
LEFT JOIN (
    SELECT deal_id, SUM(amount) AS total_paid
    FROM transactions
    WHERE status = 'confirmed'
    GROUP BY deal_id
) tp ON tp.deal_id = d.id
WHERE d.payable_amount IS NOT NULL
  AND d.payable_amount > 0
  AND COALESCE(tp.total_paid, 0) < d.payable_amount
  AND DATE(COALESCE(d.register_time, d.created_at)) BETWEEN :date_from AND :date_to
  AND (:owner_didar_id IS NULL OR d.owner_didar_id = :owner_didar_id)
  AND (:product_id IS NULL OR EXISTS (
      SELECT 1 FROM deal_products dp WHERE dp.deal_id = d.id AND dp.product_id = :product_id
  ));

-- 4) Deals without payments
SELECT
    COUNT(DISTINCT d.id) AS deal_count,
    SUM(d.payable_amount) AS total_potential_amount
FROM deals d
LEFT JOIN (
    SELECT deal_id, SUM(amount) AS total_paid
    FROM transactions
    WHERE status = 'confirmed'
    GROUP BY deal_id
) tp ON tp.deal_id = d.id
WHERE d.payable_amount IS NOT NULL
  AND d.payable_amount > 0
  AND (tp.total_paid IS NULL OR tp.total_paid = 0)
  AND d.status NOT IN ('Won', 'Lost')
  AND DATE(COALESCE(d.register_time, d.created_at)) BETWEEN :date_from AND :date_to
  AND (:owner_didar_id IS NULL OR d.owner_didar_id = :owner_didar_id)
  AND (:product_id IS NULL OR EXISTS (
      SELECT 1 FROM deal_products dp WHERE dp.deal_id = d.id AND dp.product_id = :product_id
  ));

-- 5) Deal conversion rate (deal-based)
SELECT
    COUNT(DISTINCT d.id) AS total_deals,
    COUNT(DISTINCT CASE WHEN COALESCE(tp.total_paid, 0) > 0 THEN d.id END) AS converted_deals
FROM deals d
LEFT JOIN (
    SELECT deal_id, SUM(amount) AS total_paid
    FROM transactions
    WHERE status = 'confirmed'
    GROUP BY deal_id
) tp ON tp.deal_id = d.id
WHERE DATE(COALESCE(d.register_time, d.created_at)) BETWEEN :date_from AND :date_to
  AND (:owner_didar_id IS NULL OR d.owner_didar_id = :owner_didar_id)
  AND (:product_id IS NULL OR EXISTS (
      SELECT 1 FROM deal_products dp WHERE dp.deal_id = d.id AND dp.product_id = :product_id
  ));

-- 6) Lead conversion rate (new leads)
SELECT
    COUNT(DISTINCT p.didar_contact_id) AS total_new_leads,
    COUNT(DISTINCT CASE WHEN t.deal_id IS NOT NULL THEN p.didar_contact_id END) AS converted_leads
FROM persons p
LEFT JOIN deals d ON d.contact_didar_id = p.didar_contact_id
LEFT JOIN (
    SELECT DISTINCT deal_id
    FROM transactions
    WHERE status = 'confirmed'
) t ON t.deal_id = d.id
WHERE DATE(p.register_time) BETWEEN :date_from AND :date_to
  AND (:owner_didar_id IS NULL OR p.owner_didar_id = :owner_didar_id);

-- 7) Lost sales
SELECT
    COUNT(DISTINCT d.id) AS total_lost,
    SUM(d.payable_amount) AS total_amount
FROM deals d
WHERE d.status = 'Lost'
  AND d.lost_time IS NOT NULL
  AND DATE(d.lost_time) BETWEEN :date_from AND :date_to
  AND (:owner_didar_id IS NULL OR d.owner_didar_id = :owner_didar_id)
  AND (:product_id IS NULL OR EXISTS (
      SELECT 1 FROM deal_products dp WHERE dp.deal_id = d.id AND dp.product_id = :product_id
  ));

-- 8) Open pipeline
SELECT
    COUNT(DISTINCT d.id) AS deal_count,
    SUM(d.payable_amount) AS total_potential_amount
FROM deals d
WHERE d.status NOT IN ('Won', 'Lost')
  AND DATE(COALESCE(d.register_time, d.created_at)) BETWEEN :date_from AND :date_to
  AND (:owner_didar_id IS NULL OR d.owner_didar_id = :owner_didar_id)
  AND (:product_id IS NULL OR EXISTS (
      SELECT 1 FROM deal_products dp WHERE dp.deal_id = d.id AND dp.product_id = :product_id
  ));

-- 9) Time to conversion (deal register_time -> first payment)
SELECT
    COUNT(*) AS deal_count,
    AVG(DATEDIFF(first_payment_date, deal_date)) AS avg_conversion_days
FROM (
    SELECT
        d.id,
        DATE(COALESCE(d.register_time, d.created_at)) AS deal_date,
        MIN(COALESCE(t.payment_date, DATE(t.payment_time))) AS first_payment_date
    FROM deals d
    JOIN transactions t ON t.deal_id = d.id AND t.status = 'confirmed'
    WHERE DATE(COALESCE(d.register_time, d.created_at)) BETWEEN :date_from AND :date_to
      AND (:owner_didar_id IS NULL OR d.owner_didar_id = :owner_didar_id)
      AND (:product_id IS NULL OR EXISTS (
          SELECT 1 FROM deal_products dp WHERE dp.deal_id = d.id AND dp.product_id = :product_id
      ))
    GROUP BY d.id
) s
WHERE s.first_payment_date IS NOT NULL;

-- 10) Daily sales (payments for deals created in range)
SELECT
    DATE(COALESCE(t.payment_date, t.payment_time)) AS date,
    COUNT(DISTINCT d.id) AS deal_count,
    SUM(d.payable_amount) AS total_amount,
    SUM(t.amount) AS collected_amount
FROM deals d
JOIN transactions t ON t.deal_id = d.id
WHERE t.status = 'confirmed'
  AND DATE(COALESCE(d.register_time, d.created_at)) BETWEEN :date_from AND :date_to
  AND (:owner_didar_id IS NULL OR d.owner_didar_id = :owner_didar_id)
  AND (:product_id IS NULL OR EXISTS (
      SELECT 1 FROM deal_products dp WHERE dp.deal_id = d.id AND dp.product_id = :product_id
  ))
GROUP BY DATE(COALESCE(t.payment_date, t.payment_time))
ORDER BY DATE(COALESCE(t.payment_date, t.payment_time));