-- Fix Duplicate Certificates Issue
-- This script identifies and removes duplicate vessel certificates,
-- keeping the most recent/valid certificate for each vessel-certificate combination

-- First, let's identify all duplicate certificates
SELECT 
    vc.vessel_id,
    v.name as vessel_name,
    vc.certificate_id,
    c.name as certificate_name,
    COUNT(*) as duplicate_count,
    GROUP_CONCAT(vc.id ORDER BY vc.expiry_date DESC) as certificate_ids,
    GROUP_CONCAT(vc.expiry_date ORDER BY vc.expiry_date DESC) as expiry_dates
FROM vessel_certificates vc
JOIN vessels v ON vc.vessel_id = v.id
JOIN certificates c ON vc.certificate_id = c.id
GROUP BY vc.vessel_id, vc.certificate_id
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC, v.name, c.name;

-- Specific fix for AK BEAUTY SHIP SAFETY RADIO CERTIFICATE
-- Remove the expired certificate (ID 144, expires 2025-05-20)
-- Keep the valid certificate (ID 142, expires 2027-05-20)
DELETE FROM vessel_certificates 
WHERE id = 144 
  AND vessel_id = 8 
  AND certificate_id = 35 
  AND expiry_date = '2025-05-20'
  AND id_no = '2407337RS';

-- Create a temporary table to identify which certificates to keep
CREATE TEMPORARY TABLE certificates_to_keep AS
SELECT 
    vessel_id,
    certificate_id,
    MAX(CASE 
        WHEN expiry_date IS NULL THEN id
        ELSE NULL 
    END) as keep_null_expiry,
    MAX(CASE 
        WHEN expiry_date IS NOT NULL THEN 
            CASE 
                WHEN expiry_date >= CURDATE() THEN id
                ELSE NULL
            END
        ELSE NULL 
    END) as keep_valid_cert,
    MAX(CASE 
        WHEN expiry_date IS NOT NULL AND expiry_date < CURDATE() THEN id
        ELSE NULL 
    END) as keep_latest_expired
FROM vessel_certificates
GROUP BY vessel_id, certificate_id
HAVING COUNT(*) > 1;

-- For each duplicate group, determine which certificate to keep
-- Priority: 1) Valid certificates (not expired), 2) Latest expired, 3) Null expiry
CREATE TEMPORARY TABLE certificates_to_delete AS
SELECT vc.id
FROM vessel_certificates vc
JOIN certificates_to_keep ctk ON vc.vessel_id = ctk.vessel_id AND vc.certificate_id = ctk.certificate_id
WHERE vc.id != COALESCE(
    ctk.keep_valid_cert,
    ctk.keep_latest_expired,
    ctk.keep_null_expiry
);

-- Show what will be deleted before actually deleting
SELECT 
    vc.id,
    v.name as vessel_name,
    c.name as certificate_name,
    vc.id_no,
    vc.expiry_date,
    CASE 
        WHEN vc.expiry_date IS NULL THEN 'No Expiry'
        WHEN vc.expiry_date < CURDATE() THEN 'Expired'
        ELSE 'Valid'
    END as status
FROM vessel_certificates vc
JOIN vessels v ON vc.vessel_id = v.id
JOIN certificates c ON vc.certificate_id = c.id
JOIN certificates_to_delete ctd ON vc.id = ctd.id
ORDER BY v.name, c.name;

-- Uncomment the following line to actually delete the duplicate certificates
-- DELETE vc FROM vessel_certificates vc JOIN certificates_to_delete ctd ON vc.id = ctd.id;

-- Clean up temporary tables
DROP TEMPORARY TABLE certificates_to_keep;
DROP TEMPORARY TABLE certificates_to_delete;

-- Verify the fix by checking for remaining duplicates
SELECT 
    vc.vessel_id,
    v.name as vessel_name,
    vc.certificate_id,
    c.name as certificate_name,
    COUNT(*) as certificate_count
FROM vessel_certificates vc
JOIN vessels v ON vc.vessel_id = v.id
JOIN certificates c ON vc.certificate_id = c.id
GROUP BY vc.vessel_id, vc.certificate_id
HAVING COUNT(*) > 1
ORDER BY certificate_count DESC, v.name, c.name;

-- Show the final state of AK BEAUTY's SHIP SAFETY RADIO CERTIFICATE
SELECT 
    vc.id,
    v.name as vessel_name,
    c.name as certificate_name,
    vc.id_no,
    vc.issued_date,
    vc.expiry_date,
    CASE 
        WHEN vc.expiry_date IS NULL THEN 'No Expiry'
        WHEN vc.expiry_date < CURDATE() THEN 'Expired'
        ELSE 'Valid'
    END as status
FROM vessel_certificates vc
JOIN vessels v ON vc.vessel_id = v.id
JOIN certificates c ON vc.certificate_id = c.id
WHERE v.name = 'AK BEAUTY' 
  AND c.name = 'SHIP SAFETY RADIO CERTIFICATE'
ORDER BY vc.expiry_date DESC;
