-- =====================================================
-- Fix Invalid Datetime Values in Database
-- =====================================================
-- 
-- This script fixes invalid datetime values (0000-00-00) that cause Prisma errors.
-- These invalid dates are common in legacy MySQL databases.
-- 
-- IMPORTANT: Run this script BEFORE deploying the application to production!
-- 
-- Usage:
-- mysql -u username -p -h hostname database_name < fix_invalid_datetime_values.sql
-- 
-- Or for this project specifically:
-- mysql -u beszerzes_dev -p390L2zF21LKJfds -h localhost beszerzes_dev < fix_invalid_datetime_values.sql
-- =====================================================

-- Enable strict mode to prevent future invalid dates
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';

-- Show current counts before fixing
SELECT 'BEFORE FIXES - Invalid datetime counts:' as Status;

SELECT 
  'Munkak.Datum_kezd' as TableColumn,
  COUNT(*) as InvalidCount
FROM Munkak 
WHERE Datum_kezd = '0000-00-00' OR MONTH(Datum_kezd) = 0 OR DAY(Datum_kezd) = 0
UNION ALL
SELECT 
  'Munkak.Datum_vege' as TableColumn,
  COUNT(*) as InvalidCount
FROM Munkak 
WHERE Datum_vege = '0000-00-00' OR MONTH(Datum_vege) = 0 OR DAY(Datum_vege) = 0
UNION ALL
SELECT 
  'Munkak.Esedekes' as TableColumn,
  COUNT(*) as InvalidCount
FROM Munkak 
WHERE Esedekes IS NOT NULL AND (Esedekes = '0000-00-00' OR MONTH(Esedekes) = 0 OR DAY(Esedekes) = 0)
UNION ALL
SELECT 
  'Beszerzes.Be_datum' as TableColumn,
  COUNT(*) as InvalidCount
FROM Beszerzes 
WHERE Be_datum = '0000-00-00' OR MONTH(Be_datum) = 0 OR DAY(Be_datum) = 0;

-- =====================================================
-- FIXES START HERE
-- =====================================================

-- Fix invalid start dates in Munkak table
UPDATE `Munkak` 
SET `Datum_kezd` = '1970-01-01' 
WHERE `Datum_kezd` = '0000-00-00' 
   OR MONTH(`Datum_kezd`) = 0 
   OR DAY(`Datum_kezd`) = 0;

-- Fix invalid end dates in Munkak table  
UPDATE `Munkak` 
SET `Datum_vege` = '1970-01-01' 
WHERE `Datum_vege` = '0000-00-00' 
   OR MONTH(`Datum_vege`) = 0 
   OR DAY(`Datum_vege`) = 0;

-- Fix invalid due dates in Munkak table (set to NULL since it's optional)
UPDATE `Munkak` 
SET `Esedekes` = NULL 
WHERE `Esedekes` = '0000-00-00' 
   OR MONTH(`Esedekes`) = 0 
   OR DAY(`Esedekes`) = 0;

-- Fix invalid dates in Beszerzes table (Be_datum)
-- Note: We set these to a valid default date since Be_datum has NOT NULL constraint
UPDATE `Beszerzes`
SET `Be_datum` = '1970-01-01'
WHERE `Be_datum` = '0000-00-00'
   OR MONTH(`Be_datum`) = 0
   OR DAY(`Be_datum`) = 0;

-- Fix invalid dates in other tables if they exist
-- Bank table (commented out since it's marked as @@ignore in Prisma)
-- UPDATE `Bank` 
-- SET `Esedekes` = NULL 
-- WHERE `Esedekes` = '0000-00-00' 
--    OR MONTH(`Esedekes`) = 0 
--    OR DAY(`Esedekes`) = 0;

-- UPDATE `Bank` 
-- SET `Beerkezett` = '1970-01-01' 
-- WHERE `Beerkezett` = '0000-00-00' 
--    OR MONTH(`Beerkezett`) = 0 
--    OR DAY(`Beerkezett`) = 0;

-- Fix invalid dates in Dokument table
UPDATE `Dokument` 
SET `Datum` = '1970-01-01' 
WHERE `Datum` = '0000-00-00' 
   OR MONTH(`Datum`) = 0 
   OR DAY(`Datum`) = 0;

-- Fix invalid dates in Hazi_penztar table
UPDATE `Hazi_penztar` 
SET `Datum` = '1970-01-01' 
WHERE `Datum` = '0000-00-00' 
   OR MONTH(`Datum`) = 0 
   OR DAY(`Datum`) = 0;

-- Fix invalid dates in Munkalap table
UPDATE `Munkalap` 
SET `datum` = '1970-01-01' 
WHERE `datum` = '0000-00-00' 
   OR MONTH(`datum`) = 0 
   OR DAY(`datum`) = 0;

-- Fix invalid dates in worksheets table
UPDATE `worksheets` 
SET `date` = '1970-01-01' 
WHERE `date` = '0000-00-00' 
   OR MONTH(`date`) = 0 
   OR DAY(`date`) = 0;

UPDATE `worksheets` 
SET `creation_date` = '1970-01-01 00:00:00' 
WHERE `creation_date` = '0000-00-00 00:00:00' 
   OR MONTH(`creation_date`) = 0 
   OR DAY(`creation_date`) = 0;

-- Fix validation_date (can be NULL)
UPDATE `worksheets` 
SET `validation_date` = NULL 
WHERE `validation_date` = '0000-00-00 00:00:00' 
   OR MONTH(`validation_date`) = 0 
   OR DAY(`validation_date`) = 0;

-- Fix invalid dates in worksheet_validation_links table
UPDATE `worksheet_validation_links` 
SET `created` = '1970-01-01 00:00:00' 
WHERE `created` = '0000-00-00 00:00:00' 
   OR MONTH(`created`) = 0 
   OR DAY(`created`) = 0;

-- =====================================================
-- VERIFICATION
-- =====================================================

-- Show counts after fixing
SELECT 'AFTER FIXES - Remaining invalid datetime counts:' as Status;

SELECT 
  'Munkak.Datum_kezd' as TableColumn,
  COUNT(*) as RemainingInvalidCount
FROM Munkak 
WHERE Datum_kezd = '0000-00-00' OR MONTH(Datum_kezd) = 0 OR DAY(Datum_kezd) = 0
UNION ALL
SELECT 
  'Munkak.Datum_vege' as TableColumn,
  COUNT(*) as RemainingInvalidCount
FROM Munkak 
WHERE Datum_vege = '0000-00-00' OR MONTH(Datum_vege) = 0 OR DAY(Datum_vege) = 0
UNION ALL
SELECT 
  'Munkak.Esedekes' as TableColumn,
  COUNT(*) as RemainingInvalidCount
FROM Munkak 
WHERE Esedekes IS NOT NULL AND (Esedekes = '0000-00-00' OR MONTH(Esedekes) = 0 OR DAY(Esedekes) = 0)
UNION ALL
SELECT 
  'Beszerzes.Be_datum' as TableColumn,
  COUNT(*) as RemainingInvalidCount
FROM Beszerzes 
WHERE Be_datum = '0000-00-00' OR MONTH(Be_datum) = 0 OR DAY(Be_datum) = 0;

SELECT 'Database datetime fix completed successfully!' as Status;
