/******************************************************************************************
 *  Script Name  : setup_audit_sequence.sql
 *  Description  : Creates and synchronizes a table-based sequence (Audit_Sequence)
 *                 for Hibernate to generate IDs for the Audit entity in MySQL.
 *  Author       : <Your Name>
 *  Created Date : <Insert Date>
 ******************************************************************************************/

-- =========================================================================================
-- 1️⃣ Drop old sequence table if it exists
-- -----------------------------------------------------------------------------------------
-- This removes any previously created Audit_Sequence table to ensure a clean setup.
-- =========================================================================================
DROP TABLE IF EXISTS `Audit_Sequence`;


-- =========================================================================================
-- 2️⃣ Create table-based sequence (used by Hibernate)
-- -----------------------------------------------------------------------------------------
-- This table mimics a SEQUENCE since MySQL does not support real sequence objects.
-- It stores the next value to be used for generating primary keys.
-- =========================================================================================
CREATE TABLE `Audit_Sequence` (
  `next_val` BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ERROR 1146 (42S02) at line 43: Table 'sandbox_db.audit' doesn't exist
DROP TABLE IF EXISTS `Audit`;
CREATE TABLE `Audit` (
  `Id` BIGINT NOT NULL,
  `Action` VARCHAR(255) DEFAULT NULL,
  `CreatedOn` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `Audit` (`Id`, `Action`) VALUES
(811360, 'User Login'),
(811361, 'User Logout');


-- =========================================================================================
-- 3️⃣ Initialize the sequence with a starting value
-- -----------------------------------------------------------------------------------------
-- This sets the first value. Hibernate will read and increment this value during inserts.
-- =========================================================================================
LOCK TABLES `audit_sequence` WRITE;
INSERT INTO `Audit_Sequence` (`next_val`) VALUES (1);
UNLOCK TABLES;

-- =========================================================================================
-- 4️⃣ Check current maximum ID from the audit table
-- -----------------------------------------------------------------------------------------
-- Run this to find the current highest ID in the `audit` table.
-- Example result: 811361
-- =========================================================================================
SELECT `next_val` FROM `Audit_Sequence`;
SELECT MAX(id) AS max_id FROM `audit`;


-- =========================================================================================
-- 5️⃣ Synchronize the sequence with the existing data
-- -----------------------------------------------------------------------------------------
-- This ensures the next generated value will not conflict with existing audit IDs.
-- It sets next_val = MAX(id from audit) + 1.
-- =========================================================================================
UPDATE `Audit_Sequence`
SET `next_val` = (SELECT IFNULL(MAX(id), 0) + 1 FROM `audit`);


-- =========================================================================================
-- 6️⃣ Verify the sequence value after synchronization
-- -----------------------------------------------------------------------------------------
-- The next_val should now be greater than any existing audit ID.
-- Example: 811362
-- =========================================================================================
SELECT `next_val` FROM `Audit_Sequence`;


-- =========================================================================================
-- 7️⃣ (Optional) Simulate Hibernate increment behavior
-- -----------------------------------------------------------------------------------------
-- Hibernate increments this value each time it needs a new ID.
-- You can test it manually with the below command.
-- =========================================================================================
UPDATE `Audit_Sequence` SET `next_val` = `next_val` + 1;


-- =========================================================================================
-- 8️⃣ Verify final sequence value
-- -----------------------------------------------------------------------------------------
-- This should reflect the increment.
-- =========================================================================================
SELECT * FROM `Audit_Sequence`;


-- =========================================================================================
-- ✅ Done
-- The `Audit_Sequence` table is now ready for use by the Hibernate entity `Audit`.
-- Hibernate will read and increment this table automatically.
-- =========================================================================================

--
-- delete all data inserted until this point
--
 -- SET FOREIGN_KEY_CHECKS = 0;
 -- DELETE FROM `audit_sequence`;