/*
 * Copyright ©  2022 Thomas J Collinson
 * All rights reserved
 */

ALTER TABLE `accounts`
    MODIFY `pword` VARCHAR(500) NOT NULL;

INSERT INTO `advanced_settings_default` (`acc_type`, `job_priority`, `max_active_jobs`, `cancel_job`, `allow_cash`, `hold_jobs`, `admin_user`)
VALUES ('user_create', 3, 10, 0, 0, 1, 0);
INSERT INTO `advanced_settings_default` (`acc_type`, `job_priority`, `max_active_jobs`, `cancel_job`, `allow_cash`, `hold_jobs`, `admin_user`)
VALUES ('admin_create', 3, 10, 1, 0, 0, 1);

ALTER TABLE `dispatch_systems`
    MODIFY `type` ENUM ('icabbi', 'autocab', 'external') DEFAULT 'icabbi' NOT NULL;

ALTER TABLE `dispatch_systems`
    ADD `payment_service_provider` SMALLINT UNSIGNED DEFAULT 2 NOT NULL;

DROP TABLE IF EXISTS `judopay_keys`;

CREATE TABLE `judopay_keys`
(
    `id`     INT UNSIGNED AUTO_INCREMENT,
    `judoid` INT UNSIGNED         NOT NULL,
    `token`  VARCHAR(20)          NOT NULL,
    `secret` VARCHAR(64)          NOT NULL,
    `live`   TINYINT(1) DEFAULT 0 NOT NULL,
    CONSTRAINT `judopay_keys_pk`
        PRIMARY KEY (`id`)
);

CREATE TABLE `dispatch_system_judo_pay_rel`
(
    `judo_idfk`            INT UNSIGNED NOT NULL,
    `dispatch_system_idfk` INT UNSIGNED NOT NULL,
    CONSTRAINT `dispatch_system_judo_pay_rel_pk`
        PRIMARY KEY (`dispatch_system_idfk`),
    CONSTRAINT `dispatch_system_judo_pay_rel_dispatch_systems_id_fk`
        FOREIGN KEY (`dispatch_system_idfk`) REFERENCES `dispatch_systems` (`id`)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `dispatch_system_judo_pay_rel_judopay_keys_id_fk`
        FOREIGN KEY (`judo_idfk`) REFERENCES `judopay_keys` (`id`)
            ON UPDATE CASCADE ON DELETE CASCADE
);

ALTER TABLE `sage_booking_cache`
    MODIFY `transaction_id` VARCHAR(100) NOT NULL;

CREATE TABLE IF NOT EXISTS `ghost_secret_cache`
(
    `url`     VARCHAR(150)     NOT NULL,
    `secret`  VARCHAR(500)     NOT NULL,
    `expires` BIGINT DEFAULT 0 NOT NULL,
    CONSTRAINT `ghost_secret_cache_pk`
        PRIMARY KEY (`url`)
);
