-- ========================================
-- TABLE CREATION QUERIES
-- ========================================

-- Call Logs Table
CREATE TABLE call_logs (
    call_id CHAR(36) PRIMARY KEY,
    invoice_id CHAR(36) NOT NULL,
    taxpayer_id CHAR(36) NOT NULL,
    caller_id CHAR(36) NOT NULL,
    call_type ENUM('outbound', 'inbound', 'follow_up') NOT NULL,
    call_status ENUM('connected', 'no_answer', 'busy', 'failed') NOT NULL,
    call_outcome ENUM('payment_promised', 'disputed', 'payment_plan', 'escalated'),
    duration_seconds INT,
    notes TEXT,
    scheduled_at TIMESTAMP,
    called_at TIMESTAMP NOT NULL,
    recording_url VARCHAR(500),
    INDEX idx_call_status (call_status),
    INDEX idx_call_type (call_type),
    INDEX idx_called_at (called_at),
    INDEX idx_caller_id (caller_id)
);

-- Follow-ups Table
CREATE TABLE follow_ups (
    followup_id CHAR(36) PRIMARY KEY,
    invoice_id CHAR(36) NOT NULL,
    assigned_to CHAR(36) NOT NULL,
    followup_type ENUM('call', 'email', 'visit', 'reminder') NOT NULL,
    priority ENUM('low', 'medium', 'high', 'urgent') NOT NULL,
    scheduled_date TIMESTAMP NOT NULL,
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
    notes TEXT,
    completed_at TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_scheduled_date (scheduled_date),
    INDEX idx_assigned_to (assigned_to)
);

-- Escalations Table
CREATE TABLE escalations (
    escalation_id CHAR(36) PRIMARY KEY,
    invoice_id CHAR(36) NOT NULL,
    raised_by CHAR(36) NOT NULL,
    assigned_to CHAR(36),
    escalation_type ENUM('dispute', 'complaint', 'legal', 'technical') NOT NULL,
    priority ENUM('medium', 'high', 'urgent', 'critical') NOT NULL,
    status ENUM('open', 'in_progress', 'resolved', 'closed') NOT NULL DEFAULT 'open',
    description TEXT NOT NULL,
    resolution TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_created_at (created_at),
    INDEX idx_assigned_to (assigned_to)
);

-- ========================================
-- CALL LOGS QUERIES
-- ========================================

-- 1. Create new call log
INSERT INTO call_logs (
    call_id,
    invoice_id,
    taxpayer_id,
    caller_id,
    call_type,
    call_status,
    call_outcome,
    duration_seconds,
    notes,
    scheduled_at,
    called_at,
    recording_url
) VALUES (
    UUID(),
    ?,  -- invoice_id
    ?,  -- taxpayer_id
    ?,  -- caller_id (IPO user_id)
    'outbound',
    ?,  -- call_status
    ?,  -- call_outcome (optional)
    ?,  -- duration_seconds
    ?,  -- notes
    ?,  -- scheduled_at (optional)
    CURRENT_TIMESTAMP,
    ?   -- recording_url (optional)
);

-- 2. Get all calls for a specific invoice
SELECT 
    cl.call_id,
    cl.call_type,
    cl.call_status,
    cl.call_outcome,
    cl.duration_seconds,
    cl.notes,
    cl.called_at,
    cl.recording_url,
    u.full_name AS caller_name,
    t.taxpayer_name,
    t.phone_number
FROM call_logs cl
INNER JOIN users u ON cl.caller_id = u.user_id
INNER JOIN taxpayers t ON cl.taxpayer_id = t.taxpayer_id
WHERE cl.invoice_id = ?
ORDER BY cl.called_at DESC;

-- 3. Get recent calls by IPO
SELECT 
    cl.call_id,
    cl.call_type,
    cl.call_status,
    cl.call_outcome,
    cl.duration_seconds,
    cl.called_at,
    i.invoice_number,
    t.taxpayer_name,
    t.phone_number
FROM call_logs cl
INNER JOIN invoices i ON cl.invoice_id = i.invoice_id
INNER JOIN taxpayers t ON cl.taxpayer_id = t.taxpayer_id
WHERE cl.caller_id = ?
ORDER BY cl.called_at DESC
LIMIT 50;

-- 4. Get call statistics by status
SELECT 
    call_status,
    COUNT(*) AS total_calls,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS percentage,
    AVG(duration_seconds) AS avg_duration_seconds,
    SUM(duration_seconds) AS total_duration_seconds
FROM call_logs
WHERE called_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY call_status
ORDER BY total_calls DESC;

-- 5. Get call outcome distribution
SELECT 
    call_outcome,
    COUNT(*) AS outcome_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS percentage
FROM call_logs
WHERE call_outcome IS NOT NULL
  AND called_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY call_outcome
ORDER BY outcome_count DESC;

-- 6. Update call log with outcome
UPDATE call_logs
SET 
    call_outcome = ?,
    notes = ?,
    duration_seconds = ?
WHERE call_id = ?;

-- ========================================
-- FOLLOW-UPS QUERIES
-- ========================================

-- 7. Create new follow-up
INSERT INTO follow_ups (
    followup_id,
    invoice_id,
    assigned_to,
    followup_type,
    priority,
    scheduled_date,
    status,
    notes
) VALUES (
    UUID(),
    ?,  -- invoice_id
    ?,  -- assigned_to (IPO user_id)
    'call',
    ?,  -- priority
    ?,  -- scheduled_date
    'pending',
    ?   -- notes
);

-- 8. Get today's scheduled follow-ups
SELECT 
    f.followup_id,
    f.followup_type,
    f.priority,
    f.scheduled_date,
    f.status,
    f.notes,
    i.invoice_number,
    i.amount,
    i.due_date,
    DATEDIFF(CURDATE(), i.due_date) AS days_past_due,
    t.taxpayer_name,
    t.phone_number,
    t.email,
    u.full_name AS assigned_ipo
FROM follow_ups f
INNER JOIN invoices i ON f.invoice_id = i.invoice_id
INNER JOIN taxpayers t ON i.taxpayer_id = t.taxpayer_id
INNER JOIN users u ON f.assigned_to = u.user_id
WHERE DATE(f.scheduled_date) = CURDATE()
  AND f.status IN ('pending', 'in_progress')
ORDER BY 
    FIELD(f.priority, 'urgent', 'high', 'medium', 'low'),
    f.scheduled_date ASC;

-- 9. Get overdue follow-ups
SELECT 
    f.followup_id,
    f.followup_type,
    f.priority,
    f.scheduled_date,
    DATEDIFF(CURDATE(), DATE(f.scheduled_date)) AS days_overdue,
    i.invoice_number,
    t.taxpayer_name,
    u.full_name AS assigned_ipo
FROM follow_ups f
INNER JOIN invoices i ON f.invoice_id = i.invoice_id
INNER JOIN taxpayers t ON i.taxpayer_id = t.taxpayer_id
INNER JOIN users u ON f.assigned_to = u.user_id
WHERE f.status = 'pending'
  AND f.scheduled_date < CURRENT_TIMESTAMP
ORDER BY f.scheduled_date ASC;

-- 10. Get follow-ups for specific IPO
SELECT 
    f.followup_id,
    f.followup_type,
    f.priority,
    f.scheduled_date,
    f.status,
    i.invoice_number,
    i.amount,
    t.taxpayer_name,
    t.phone_number
FROM follow_ups f
INNER JOIN invoices i ON f.invoice_id = i.invoice_id
INNER JOIN taxpayers t ON i.taxpayer_id = t.taxpayer_id
WHERE f.assigned_to = ?
  AND f.status IN ('pending', 'in_progress')
ORDER BY 
    FIELD(f.priority, 'urgent', 'high', 'medium', 'low'),
    f.scheduled_date ASC;

-- 11. Complete a follow-up
UPDATE follow_ups
SET 
    status = 'completed',
    completed_at = CURRENT_TIMESTAMP,
    notes = ?
WHERE followup_id = ?;

-- 12. Reschedule a follow-up
UPDATE follow_ups
SET 
    scheduled_date = ?,
    notes = CONCAT(COALESCE(notes, ''), '\n[Rescheduled] ', ?)
WHERE followup_id = ?;

-- 13. Cancel a follow-up
UPDATE follow_ups
SET 
    status = 'cancelled',
    notes = CONCAT(COALESCE(notes, ''), '\n[Cancelled] ', ?)
WHERE followup_id = ?;

-- ========================================
-- ESCALATIONS QUERIES
-- ========================================

-- 14. Create new escalation
INSERT INTO escalations (
    escalation_id,
    invoice_id,
    raised_by,
    assigned_to,
    escalation_type,
    priority,
    status,
    description
) VALUES (
    UUID(),
    ?,  -- invoice_id
    ?,  -- raised_by (IPO user_id)
    ?,  -- assigned_to (supervisor user_id, optional)
    ?,  -- escalation_type
    ?,  -- priority
    'open',
    ?   -- description
);

-- 15. Get all open escalations with priority sorting
SELECT 
    e.escalation_id,
    e.escalation_type,
    e.priority,
    e.status,
    e.description,
    e.created_at,
    u1.full_name AS raised_by_name,
    u2.full_name AS assigned_to_name,
    i.invoice_number,
    i.amount,
    t.taxpayer_name
FROM escalations e
INNER JOIN users u1 ON e.raised_by = u1.user_id
LEFT JOIN users u2 ON e.assigned_to = u2.user_id
INNER JOIN invoices i ON e.invoice_id = i.invoice_id
INNER JOIN taxpayers t ON i.taxpayer_id = t.taxpayer_id
WHERE e.status IN ('open', 'in_progress')
ORDER BY 
    FIELD(e.priority, 'critical', 'urgent', 'high', 'medium'),
    e.created_at DESC;

-- 16. Get escalations assigned to supervisor
SELECT 
    e.escalation_id,
    e.escalation_type,
    e.priority,
    e.status,
    e.description,
    e.created_at,
    u.full_name AS raised_by_name,
    i.invoice_number,
    t.taxpayer_name
FROM escalations e
INNER JOIN users u ON e.raised_by = u.user_id
INNER JOIN invoices i ON e.invoice_id = i.invoice_id
INNER JOIN taxpayers t ON i.taxpayer_id = t.taxpayer_id
WHERE e.assigned_to = ?
  AND e.status IN ('open', 'in_progress')
ORDER BY 
    FIELD(e.priority, 'critical', 'urgent', 'high', 'medium'),
    e.created_at DESC;

-- 17. Update escalation status
UPDATE escalations
SET 
    status = ?,
    resolution = ?,
    resolved_at = CASE WHEN ? IN ('resolved', 'closed') THEN CURRENT_TIMESTAMP ELSE resolved_at END
WHERE escalation_id = ?;

-- 18. Assign escalation to supervisor
UPDATE escalations
SET 
    assigned_to = ?,
    status = 'in_progress'
WHERE escalation_id = ?;

-- 19. Get escalation statistics
SELECT 
    escalation_type,
    priority,
    COUNT(*) AS total_count,
    COUNT(CASE WHEN status = 'open' THEN 1 END) AS open_count,
    COUNT(CASE WHEN status = 'in_progress' THEN 1 END) AS in_progress_count,
    COUNT(CASE WHEN status = 'resolved' THEN 1 END) AS resolved_count,
    COUNT(CASE WHEN status = 'closed' THEN 1 END) AS closed_count,
    AVG(TIMESTAMPDIFF(HOUR, created_at, COALESCE(resolved_at, CURRENT_TIMESTAMP))) AS avg_resolution_hours
FROM escalations
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY escalation_type, priority
ORDER BY 
    escalation_type,
    FIELD(priority, 'critical', 'urgent', 'high', 'medium');

-- ========================================
-- COMBINED ANALYTICS QUERIES
-- ========================================

-- 20. IPO Performance Dashboard
SELECT 
    u.user_id,
    u.full_name AS ipo_name,
    COUNT(DISTINCT cl.call_id) AS total_calls,
    COUNT(DISTINCT CASE WHEN cl.call_status = 'connected' THEN cl.call_id END) AS successful_calls,
    ROUND(COUNT(CASE WHEN cl.call_status = 'connected' THEN 1 END) * 100.0 / NULLIF(COUNT(cl.call_id), 0), 2) AS success_rate,
    AVG(CASE WHEN cl.call_status = 'connected' THEN cl.duration_seconds END) AS avg_call_duration,
    COUNT(DISTINCT f.followup_id) AS total_followups,
    COUNT(DISTINCT CASE WHEN f.status = 'completed' THEN f.followup_id END) AS completed_followups,
    COUNT(DISTINCT e.escalation_id) AS escalations_raised
FROM users u
LEFT JOIN call_logs cl ON u.user_id = cl.caller_id 
    AND cl.called_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
LEFT JOIN follow_ups f ON u.user_id = f.assigned_to 
    AND f.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
LEFT JOIN escalations e ON u.user_id = e.raised_by 
    AND e.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
WHERE u.role = 'ipo'
GROUP BY u.user_id, u.full_name
ORDER BY successful_calls DESC;

-- 21. Invoice Collection Activity Report
SELECT 
    i.invoice_id,
    i.invoice_number,
    i.amount,
    i.due_date,
    DATEDIFF(CURDATE(), i.due_date) AS days_past_due,
    i.status AS invoice_status,
    t.taxpayer_name,
    t.phone_number,
    COUNT(DISTINCT cl.call_id) AS total_calls,
    COUNT(DISTINCT CASE WHEN cl.call_status = 'connected' THEN cl.call_id END) AS successful_calls,
    MAX(cl.called_at) AS last_call_date,
    COUNT(DISTINCT f.followup_id) AS total_followups,
    COUNT(DISTINCT CASE WHEN f.status = 'pending' THEN f.followup_id END) AS pending_followups,
    MIN(CASE WHEN f.status = 'pending' THEN f.scheduled_date END) AS next_followup_date,
    COUNT(DISTINCT e.escalation_id) AS escalation_count,
    MAX(e.status) AS latest_escalation_status
FROM invoices i
INNER JOIN taxpayers t ON i.taxpayer_id = t.taxpayer_id
LEFT JOIN call_logs cl ON i.invoice_id = cl.invoice_id
LEFT JOIN follow_ups f ON i.invoice_id = f.invoice_id
LEFT JOIN escalations e ON i.invoice_id = e.invoice_id
WHERE i.status = 'unpaid'
  AND i.due_date < CURDATE()
GROUP BY i.invoice_id, i.invoice_number, i.amount, i.due_date, i.status, t.taxpayer_name, t.phone_number
ORDER BY days_past_due DESC;

-- 22. Daily Call Activity Summary
SELECT 
    DATE(cl.called_at) AS call_date,
    COUNT(*) AS total_calls,
    COUNT(CASE WHEN cl.call_status = 'connected' THEN 1 END) AS connected_calls,
    COUNT(CASE WHEN cl.call_status = 'no_answer' THEN 1 END) AS no_answer_calls,
    COUNT(CASE WHEN cl.call_status = 'busy' THEN 1 END) AS busy_calls,
    COUNT(CASE WHEN cl.call_status = 'failed' THEN 1 END) AS failed_calls,
    COUNT(CASE WHEN cl.call_outcome = 'payment_promised' THEN 1 END) AS payment_promises,
    COUNT(CASE WHEN cl.call_outcome = 'escalated' THEN 1 END) AS escalations,
    SUM(cl.duration_seconds) AS total_duration_seconds,
    AVG(CASE WHEN cl.call_status = 'connected' THEN cl.duration_seconds END) AS avg_connected_duration
FROM call_logs cl
WHERE cl.called_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(cl.called_at)
ORDER BY call_date DESC;

-- 23. Taxpayer Contact History
SELECT 
    t.taxpayer_id,
    t.taxpayer_name,
    t.phone_number,
    t.email,
    COUNT(DISTINCT i.invoice_id) AS total_invoices,
    COUNT(DISTINCT CASE WHEN i.status = 'unpaid' THEN i.invoice_id END) AS unpaid_invoices,
    SUM(CASE WHEN i.status = 'unpaid' THEN i.amount ELSE 0 END) AS total_outstanding,
    COUNT(DISTINCT cl.call_id) AS total_calls,
    MAX(cl.called_at) AS last_contact_date,
    COUNT(DISTINCT f.followup_id) AS scheduled_followups,
    COUNT(DISTINCT e.escalation_id) AS active_escalations
FROM taxpayers t
LEFT JOIN invoices i ON t.taxpayer_id = i.taxpayer_id
LEFT JOIN call_logs cl ON t.taxpayer_id = cl.taxpayer_id
LEFT JOIN follow_ups f ON i.invoice_id = f.invoice_id AND f.status IN ('pending', 'in_progress')
LEFT JOIN escalations e ON i.invoice_id = e.invoice_id AND e.status IN ('open', 'in_progress')
WHERE t.taxpayer_id = ?
GROUP BY t.taxpayer_id, t.taxpayer_name, t.phone_number, t.email;

-- 24. Supervisor Workload Dashboard
SELECT 
    u.user_id,
    u.full_name AS supervisor_name,
    COUNT(CASE WHEN e.status = 'open' THEN 1 END) AS open_escalations,
    COUNT(CASE WHEN e.status = 'in_progress' THEN 1 END) AS in_progress_escalations,
    COUNT(CASE WHEN e.priority = 'critical' THEN 1 END) AS critical_count,
    COUNT(CASE WHEN e.priority = 'urgent' THEN 1 END) AS urgent_count,
    COUNT(CASE WHEN e.priority = 'high' THEN 1 END) AS high_count,
    COUNT(CASE WHEN e.status IN ('resolved', 'closed') 
        AND e.resolved_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN 1 END) AS resolved_last_30_days,
    AVG(CASE WHEN e.status IN ('resolved', 'closed') 
        THEN TIMESTAMPDIFF(HOUR, e.created_at, e.resolved_at) END) AS avg_resolution_hours
FROM users u
LEFT JOIN escalations e ON u.user_id = e.assigned_to
WHERE u.role = 'supervisor'
GROUP BY u.user_id, u.full_name
ORDER BY open_escalations DESC, in_progress_escalations DESC;

-- 25. Follow-up Completion Rate
SELECT 
    u.user_id,
    u.full_name AS ipo_name,
    COUNT(f.followup_id) AS total_followups,
    COUNT(CASE WHEN f.status = 'completed' THEN 1 END) AS completed,
    COUNT(CASE WHEN f.status = 'cancelled' THEN 1 END) AS cancelled,
    COUNT(CASE WHEN f.status = 'pending' AND f.scheduled_date < CURRENT_TIMESTAMP THEN 1 END) AS overdue,
    ROUND(COUNT(CASE WHEN f.status = 'completed' THEN 1 END) * 100.0 / NULLIF(COUNT(f.followup_id), 0), 2) AS completion_rate
FROM users u
INNER JOIN follow_ups f ON u.user_id = f.assigned_to
WHERE f.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY u.user_id, u.full_name
ORDER BY completion_rate DESC;