SET sql_mode = "NO_ENGINE_SUBSTITUTION";

--
-- PacketFence SQL schema upgrade from 11.0 to 11.1
--


--
-- Setting the major/minor version of the DB
--

SET @MAJOR_VERSION = 12;
SET @MINOR_VERSION = 2;


SET @PREV_MAJOR_VERSION = 12;
SET @PREV_MINOR_VERSION = 1;

--
-- The VERSION_INT to ensure proper ordering of the version in queries
--

SET @VERSION_INT = @MAJOR_VERSION << 16 | @MINOR_VERSION << 8;

SET @PREV_VERSION_INT = @PREV_MAJOR_VERSION << 16 | @PREV_MINOR_VERSION << 8;

DROP PROCEDURE IF EXISTS ValidateVersion;
--
-- Updating to current version
--
DELIMITER //
CREATE PROCEDURE ValidateVersion()
BEGIN
    DECLARE PREVIOUS_VERSION int(11);
    DECLARE PREVIOUS_VERSION_STRING varchar(11);
    DECLARE _message varchar(255);
    SELECT id, version INTO PREVIOUS_VERSION, PREVIOUS_VERSION_STRING FROM pf_version ORDER BY id DESC LIMIT 1;

      IF PREVIOUS_VERSION != @PREV_VERSION_INT THEN
        SELECT CONCAT('PREVIOUS VERSION ', PREVIOUS_VERSION_STRING, ' DOES NOT MATCH ', CONCAT_WS('.', @PREV_MAJOR_VERSION, @PREV_MINOR_VERSION)) INTO _message;
        SIGNAL SQLSTATE VALUE '99999'
              SET MESSAGE_TEXT = _message;
      END IF;
END
//

DELIMITER ;

\! echo "Checking PacketFence schema version...";
call ValidateVersion;

DROP PROCEDURE IF EXISTS ValidateVersion;

--
-- UPGRADE STATEMENTS GO HERE
--

--
-- Table structure for table `node_current_session`
--

CREATE TABLE IF NOT EXISTS node_current_session (
  `mac` varchar(17) NOT NULL,
  `updated` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_session_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `is_online` BOOLEAN DEFAULT 1,
  PRIMARY KEY (mac)
) ENGINE=InnoDB DEFAULT CHARACTER SET = 'utf8mb4';

\! echo "Updating node table"
ALTER TABLE `node`
    ADD COLUMN IF NOT EXISTS `bypass_acls` MEDIUMTEXT DEFAULT NULL;

\! echo "Updating pki_certs table"
ALTER TABLE `pki_certs`
    ADD COLUMN IF NOT EXISTS `not_before` datetime DEFAULT NULL AFTER `valid_until`;
UPDATE pki_certs SET not_before = created_at WHERE 1;

\! echo "Updating pki_revoked_certs table"
ALTER TABLE `pki_revoked_certs`
    ADD COLUMN IF NOT EXISTS `not_before` datetime DEFAULT NULL AFTER `valid_until`;
UPDATE pki_revoked_certs SET not_before = created_at WHERE 1;

\! echo "Incrementing PacketFence schema version...";
INSERT IGNORE INTO pf_version (id, version, created_at) VALUES (@VERSION_INT, CONCAT_WS('.', @MAJOR_VERSION, @MINOR_VERSION), NOW());


\! echo "Upgrade completed successfully.";
