-- This SQL script fixes vessels with 0 certificates by importing data from the aikhrrte_certificates.sql file

-- First, let's check which vessels have 0 certificates
SELECT v.id, v.name, COUNT(vc.id) as certificate_count
FROM vessels v
LEFT JOIN vessel_certificates vc ON v.id = vc.vessel_id
GROUP BY v.id, v.name
ORDER BY certificate_count ASC;

-- Now, let's import certificates for vessels with 0 certificates
-- These INSERT statements are extracted from the aikhrrte_certificates.sql file

-- Certificates for vessel ID 1 (AK BRIGHT)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(1, 30, '2503114HM', 'DBS', '2025-03-12', '2025-06-03', '[\"\\/certificates\\/uploads\\/67d2a49f1133f5.12387494.pdf\"]', NOW(), NOW()),
(1, 31, '2501276ll', 'dbs', '2025-02-06', '2026-12-09', '[\"\\/certificates\\/uploads\\/67e4fa47cd7785.00945531.pdf\"]', NOW(), NOW()),
(1, 32, '2501276ITC', 'DBS', '2025-02-06', '2030-02-06', '[\"\\/certificates\\/uploads\\/67e4fc0dcb5346.97705926.pdf\"]', NOW(), NOW()),
(1, 33, '2503114SC', 'DBS', '2025-03-12', '2025-06-03', '[\"\\/certificates\\/uploads\\/67d7c9195aa1b3.72226650.pdf\"]', NOW(), NOW()),
(1, 34, '2501276SE', 'DBS', '2025-02-06', '2026-12-09', '[\"\\/certificates\\/uploads\\/67e4fadf7c73a5.60625762.pdf\"]', NOW(), NOW()),
(1, 35, '2501276SR', 'DBS', '2025-02-06', '2026-12-09', '[\"\\/certificates\\/uploads\\/67e4fb1bb312e1.29397598.pdf\"]', NOW(), NOW());

-- Certificates for vessel ID 3 (AK DISCOVERY)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(3, 30, '2504184HM', 'PRS', '2025-05-09', '2025-10-05', '[\"https:\\/\\/aik.hrr.temporary.site\\/certificates\\/uploads\\/681c59a67d2e44.36343578.pdf\",\"https:\\/\\/aik.hrr.temporary.site\\/certificates\\/uploads\\/681dea1876e0c2.30508850.pdf\"]', NOW(), NOW()),
(3, 31, '2504184LL', 'DBS', '2025-05-06', '2025-10-05', '[\"https:\\/\\/aik.hrr.temporary.site\\/certificates\\/uploads\\/681c5b90328725.99345375.pdf\"]', NOW(), NOW()),
(3, 32, '2504184ITC', 'DBS', '2025-05-06', '2025-10-05', '[\"https:\\/\\/aik.hrr.temporary.site\\/certificates\\/uploads\\/681c5b668246d3.27576108.pdf\"]', NOW(), NOW());

-- Certificates for vessel ID 6 (PARESA)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(6, 30, '2504184HM', 'PRS', '2025-05-09', '2025-10-05', '[\"https:\\/\\/aik.hrr.temporary.site\\/certificates\\/uploads\\/681c59a67d2e44.36343578.pdf\"]', NOW(), NOW()),
(6, 31, '2504184LL', 'DBS', '2025-05-06', '2025-10-05', '[\"https:\\/\\/aik.hrr.temporary.site\\/certificates\\/uploads\\/681c5b90328725.99345375.pdf\"]', NOW(), NOW());

-- Certificates for vessel ID 7 (AK AMBITION)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(7, 21, 'ADOMS-COR-250196', 'FLAG', '2025-05-09', '2025-05-19', '[\"https:\\/\\/aik.hrr.temporary.site\\/certificates\\/uploads\\/681df70fb51ec2.00177273.pdf\"]', NOW(), NOW()),
(7, 26, 'ADMOS-DMLC-240041', 'FLAG', '2024-11-15', '2029-05-12', '/certificates/uploads/67909102b99af_AK_AMBITION_DMLC.pdf', NOW(), NOW());

-- Certificates for vessel ID 8 (AK BEAUTY)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(8, 21, 'BMSR-COR-240392', 'FLAG', '2024-09-01', '2029-03-01', '[\"https:\\/\\/aik.hrr.temporary.site\\/certificates\\/uploads\\/681db50e2a4c75.62247743.pdf\",\"https:\\/\\/aik.hrr.temporary.site\\/certificates\\/uploads\\/681dd3fa016f57.01069909.pdf\"]', NOW(), NOW()),
(8, 26, '', 'FLAG', '2024-09-01', '2025-03-01', '/certificates/uploads/6790c381c0a9d_AK_BEAUTY_DMLC_PART_I.pdf', NOW(), NOW());

-- Certificates for vessel ID 9 (AK GARY)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(9, 21, 'BMSR-COR-240392', 'FLAG', '2024-09-01', '2029-03-01', '[\"https:\\/\\/aik.hrr.temporary.site\\/certificates\\/uploads\\/681db50e2a4c75.62247743.pdf\"]', NOW(), NOW()),
(9, 26, '', 'FLAG', '2024-09-01', '2025-03-01', '/certificates/uploads/6790c381c0a9d_AK_BEAUTY_DMLC_PART_I.pdf', NOW(), NOW());

-- Certificates for vessel ID 10 (AK GLORIOUS)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(10, 21, 'BMSR-COR-240392', 'FLAG', '2024-09-01', '2029-03-01', '[\"https:\\/\\/aik.hrr.temporary.site\\/certificates\\/uploads\\/681db50e2a4c75.62247743.pdf\"]', NOW(), NOW()),
(10, 26, '', 'FLAG', '2024-09-01', '2025-03-01', '/certificates/uploads/6790c381c0a9d_AK_BEAUTY_DMLC_PART_I.pdf', NOW(), NOW());

-- Certificates for vessel ID 11 (AK HALIMA)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(11, 21, 'BMSR-COR-230272', 'FLAG', '2023-05-09', '2027-11-16', '/certificates/uploads/679c776d34b0b8.00287614.pdf', NOW(), NOW()),
(11, 26, 'BMSR-DMLC-230031', 'FLAG', '2023-04-13', '2028-04-13', '/certificates/uploads/67920644036011.04983607.pdf', NOW(), NOW());

-- Certificates for vessel ID 12 (AK HAMZA)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(12, 28, 'BMSR-BUNKER-250346', 'FLAG', '2025-02-20', '2026-02-20', '/certificates/uploads/67c02ee86ed9f6.10890410.pdf', NOW(), NOW()),
(12, 21, 'BMSR-COR-240706', 'FLAG', '2024-11-23', '2025-02-23', '/certificates/uploads/6790b2396995b_AK_HAMZA_COR.pdf', NOW(), NOW());

-- Certificates for vessel ID 13 (AK KHADEJA)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(13, 21, 'BMSR-COR-230488', 'FLAG', '2023-10-13', '2028-10-13', '[\"\\/certificates\\/uploads\\/67dbc1721c7203.76739858.pdf\"]', NOW(), NOW()),
(13, 26, 'BMSR-DMLC-230031', 'FLAG', '2023-04-13', '2028-04-13', '/certificates/uploads/67920644036011.04983607.pdf', NOW(), NOW());

-- Certificates for vessel ID 14 (AK KHAIRA)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(14, 21, 'BMSR-COR-230488', 'FLAG', '2023-10-13', '2028-10-13', '[\"\\/certificates\\/uploads\\/67dbc1721c7203.76739858.pdf\"]', NOW(), NOW()),
(14, 26, 'BMSR-DMLC-230031', 'FLAG', '2023-04-13', '2028-04-13', '/certificates/uploads/67920644036011.04983607.pdf', NOW(), NOW());

-- Certificates for vessel ID 15 (AK LIZA)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(15, 21, 'BMSR-COR-230488', 'FLAG', '2023-10-13', '2028-10-13', '[\"\\/certificates\\/uploads\\/67dbc1721c7203.76739858.pdf\"]', NOW(), NOW()),
(15, 26, 'BMSR-DMLC-230031', 'FLAG', '2023-04-13', '2028-04-13', '/certificates/uploads/67920644036011.04983607.pdf', NOW(), NOW());

-- Certificates for vessel ID 16 (SUAVE)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(16, 21, 'BMSR-COR-230488', 'FLAG', '2023-10-13', '2028-10-13', '[\"\\/certificates\\/uploads\\/67dbc1721c7203.76739858.pdf\"]', NOW(), NOW()),
(16, 26, 'BMSR-DMLC-230031', 'FLAG', '2023-04-13', '2028-04-13', '/certificates/uploads/67920644036011.04983607.pdf', NOW(), NOW());

-- Certificates for vessel ID 17 (AK CARL)
INSERT INTO vessel_certificates (vessel_id, certificate_id, id_no, vessel_department, issued_date, expiry_date, attachment_path, created_at, updated_at)
VALUES
(17, 21, 'BMSR-COR-230488', 'FLAG', '2023-10-13', '2028-10-13', '[\"\\/certificates\\/uploads\\/67dbc1721c7203.76739858.pdf\"]', NOW(), NOW()),
(17, 26, 'BMSR-DMLC-230031', 'FLAG', '2023-04-13', '2028-04-13', '/certificates/uploads/67920644036011.04983607.pdf', NOW(), NOW());

-- Fix duplicate certificates issue for AK BEAUTY
-- Remove the expired SHIP SAFETY RADIO CERTIFICATE (ID 144) and keep the valid one (ID 142)
DELETE FROM vessel_certificates
WHERE id = 144
AND vessel_id = 8
AND certificate_id = 35
AND expiry_date = '2025-05-20';

-- Check for other duplicate certificates across all vessels
SELECT vessel_id, certificate_id, COUNT(*) as duplicate_count
FROM vessel_certificates
GROUP BY vessel_id, certificate_id
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

-- Check the results
SELECT v.id, v.name, COUNT(vc.id) as certificate_count
FROM vessels v
LEFT JOIN vessel_certificates vc ON v.id = vc.vessel_id
GROUP BY v.id, v.name
ORDER BY certificate_count ASC;
