-- ============================================
-- KPI System Test Data Generator
-- Date: 2025-01-04
-- Description: Creates comprehensive test data for KPI validation
-- ============================================

-- Clean up existing test data (optional - comment out if you want to keep existing data)
-- DELETE FROM transactions WHERE deal_id IN (SELECT id FROM deals WHERE didar_deal_id LIKE 'test_%');
-- DELETE FROM deals WHERE didar_deal_id LIKE 'test_%';
-- DELETE FROM persons WHERE didar_contact_id LIKE 'test_%';
-- DELETE FROM users WHERE didar_user_id LIKE 'test_%';

-- 1. Test Users
INSERT INTO users (didar_user_id, first_name, last_name, role, is_active, created_at, updated_at)
VALUES
('test_admin', 'Admin', 'User', 'admin', 1, NOW(), NOW()),
('test_agent_1', 'Agent', 'One', 'agent', 1, NOW(), NOW()),
('test_agent_2', 'Agent', 'Two', 'agent', 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE updated_at = NOW();

-- 2. Test Persons with different registration dates
INSERT INTO persons (didar_contact_id, owner_didar_id, first_name, last_name, mobile_phone, register_time, created_at, updated_at)
VALUES
('test_person_001', 'test_agent_1', 'Test', 'Person', '9120000001', '2024-12-25 23:59:59', NOW(), NOW()),
('test_person_002', 'test_agent_1', 'Test', 'Person', '9120000002', '2025-01-05 10:30:00', NOW(), NOW()),
('test_person_003', 'test_agent_1', 'Test', 'Person', '9120000003', '2025-01-15 14:45:00', NOW(), NOW()),
('test_person_004', 'test_agent_2', 'Test', 'Person', '9120000004', '2025-01-10 09:00:00', NOW(), NOW())
ON DUPLICATE KEY UPDATE updated_at = NOW();

-- 3. Test Deals (auto-created with default values)
INSERT INTO deals (didar_deal_id, owner_didar_id, contact_didar_id, title, status, register_time, payable_amount, payment_amount, created_at, updated_at)
VALUES
('test_deal_001', 'test_agent_1', 'test_person_001', 'معامله اول 9120000001', 'Pending', '2025-01-05 10:35:00', 0, 0, NOW(), NOW()),
('test_deal_002', 'test_agent_1', 'test_person_001', 'معامله دوم 9120000001', 'Pending', '2025-01-10 11:20:00', 100000000, 0, NOW(), NOW()),
('test_deal_003', 'test_agent_1', 'test_person_002', 'معامله اول 9120000002', 'Won', '2025-01-06 12:00:00', 50000000, 50000000, NOW(), NOW()),
('test_deal_004', 'test_agent_1', 'test_person_003', 'معامله اول 9120000003', 'Lost', '2025-01-16 15:00:00', 75000000, 0, NOW(), NOW()),
('test_deal_005', 'test_agent_2', 'test_person_004', 'معامله اول 9120000004', 'Pending', '2025-01-11 10:00:00', 80000000, 0, NOW(), NOW())
ON DUPLICATE KEY UPDATE updated_at = NOW();

-- 4. Test Transactions (across different months)
INSERT INTO transactions (deal_id, amount, payment_date, payment_time, is_first_payment, status, created_at)
SELECT 
    d.id,
    CASE 
        WHEN d.didar_deal_id = 'test_deal_001' THEN 30000000
        WHEN d.didar_deal_id = 'test_deal_002' THEN 50000000
        WHEN d.didar_deal_id = 'test_deal_003' THEN 50000000
        WHEN d.didar_deal_id = 'test_deal_004' THEN 0
        WHEN d.didar_deal_id = 'test_deal_005' THEN 0
    END as amount,
    CASE 
        WHEN d.didar_deal_id = 'test_deal_001' THEN '2025-01-15'
        WHEN d.didar_deal_id = 'test_deal_002' THEN '2025-02-10'
        WHEN d.didar_deal_id = 'test_deal_003' THEN '2025-01-20'
        WHEN d.didar_deal_id = 'test_deal_004' THEN NULL
        WHEN d.didar_deal_id = 'test_deal_005' THEN NULL
    END as payment_date,
    CASE 
        WHEN d.didar_deal_id = 'test_deal_001' THEN '2025-01-15 14:30:00'
        WHEN d.didar_deal_id = 'test_deal_002' THEN '2025-02-10 16:45:00'
        WHEN d.didar_deal_id = 'test_deal_003' THEN '2025-01-20 10:15:00'
        WHEN d.didar_deal_id = 'test_deal_004' THEN NULL
        WHEN d.didar_deal_id = 'test_deal_005' THEN NULL
    END as payment_time,
    1 as is_first_payment,
    'confirmed' as status,
    NOW() as created_at
FROM deals d
WHERE d.didar_deal_id IN ('test_deal_001', 'test_deal_002', 'test_deal_003')
  AND NOT EXISTS (
      SELECT 1 FROM transactions t WHERE t.deal_id = d.id
  );

-- Add second payment for test_deal_001
INSERT INTO transactions (deal_id, amount, payment_date, payment_time, is_first_payment, status, created_at)
SELECT 
    d.id,
    70000000 as amount,
    '2025-01-20' as payment_date,
    '2025-01-20 10:15:00' as payment_time,
    0 as is_first_payment,
    'confirmed' as status,
    NOW() as created_at
FROM deals d
WHERE d.didar_deal_id = 'test_deal_001'
  AND NOT EXISTS (
      SELECT 1 FROM transactions t WHERE t.deal_id = d.id AND t.is_first_payment = 0
  );

-- 5. Test KPI Targets
INSERT INTO kpi_targets (user_id, year, month, target_amount, target_deal_count, is_active, created_at, updated_at)
SELECT 
    u.id,
    2025 as year,
    1 as month,
    CASE 
        WHEN u.didar_user_id = 'test_agent_1' THEN 50000000
        WHEN u.didar_user_id = 'test_agent_2' THEN 100000000
        ELSE 0
    END as target_amount,
    CASE 
        WHEN u.didar_user_id = 'test_agent_1' THEN 3
        WHEN u.didar_user_id = 'test_agent_2' THEN 5
        ELSE 0
    END as target_deal_count,
    1 as is_active,
    NOW() as created_at,
    NOW() as updated_at
FROM users u
WHERE u.didar_user_id IN ('test_agent_1', 'test_agent_2')
  AND NOT EXISTS (
      SELECT 1 FROM kpi_targets kt WHERE kt.user_id = u.id AND kt.year = 2025 AND kt.month = 1
  );

-- 6. Test Deal Status Changes (update won_time and lost_time)
UPDATE deals 
SET won_time = '2025-01-20 12:00:00' 
WHERE didar_deal_id = 'test_deal_003' AND won_time IS NULL;

UPDATE deals 
SET lost_time = '2025-01-25 15:30:00', 
    failure_reason_code = 'no_financial_ability',
    failure_reason_text = 'عدم توان مالی'
WHERE didar_deal_id = 'test_deal_004' AND lost_time IS NULL;

-- Verification queries (run these to check test data)
-- SELECT 'Persons' as table_name, COUNT(*) as count FROM persons WHERE didar_contact_id LIKE 'test_%';
-- SELECT 'Deals' as table_name, COUNT(*) as count FROM deals WHERE didar_deal_id LIKE 'test_%';
-- SELECT 'Transactions' as table_name, COUNT(*) as count FROM transactions WHERE deal_id IN (SELECT id FROM deals WHERE didar_deal_id LIKE 'test_%');
-- SELECT 'KPI Targets' as table_name, COUNT(*) as count FROM kpi_targets WHERE user_id IN (SELECT id FROM users WHERE didar_user_id LIKE 'test_%');