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

CREATE TABLE `sub_accounts`
(
    `id`           INT UNSIGNED AUTO_INCREMENT,
    `name`         VARCHAR(50)          NOT NULL,
    `reference`    VARCHAR(50)          NOT NULL,
    `colour`       VARCHAR(7)           NOT NULL,
    `disabled`     TINYINT(1) DEFAULT 0 NOT NULL,
    `invoice_idfk` INT UNSIGNED         NOT NULL,
    CONSTRAINT `sub_accounts_pk`
        PRIMARY KEY (`id`),
    CONSTRAINT `sub_accounts_invoice_accounts_id_fk`
        FOREIGN KEY (`invoice_idfk`) REFERENCES `invoice_accounts` (`id`)
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE `sub_account_labels`
(
    `invoice_idfk` INT UNSIGNED NOT NULL,
    `singular`     VARCHAR(30)  NOT NULL,
    `plural`       VARCHAR(30)  NOT NULL,
    CONSTRAINT `sub_account_labels_pk`
        PRIMARY KEY (`invoice_idfk`),
    CONSTRAINT `sub_account_labels_invoice_accounts_id_fk`
        FOREIGN KEY (`invoice_idfk`) REFERENCES `invoice_accounts` (`id`)
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE `sub_account_admins`
(
    `account_idfk`     INT UNSIGNED       NOT NULL,
    `sub_account_idfk` INT UNSIGNED       NOT NULL,
    `flags`            SMALLINT DEFAULT 0 NOT NULL,
    CONSTRAINT `sub_account_admins_pk`
        PRIMARY KEY (`sub_account_idfk`, `account_idfk`),
    CONSTRAINT `sub_account_admins_accounts_id_fk`
        FOREIGN KEY (`account_idfk`) REFERENCES `accounts` (`id`)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `sub_account_admins_sub_accounts_id_fk`
        FOREIGN KEY (`sub_account_idfk`) REFERENCES `sub_accounts` (`id`)
            ON UPDATE CASCADE ON DELETE CASCADE
);

#Just for NHS system
INSERT INTO `sub_account_labels` (`invoice_idfk`, `singular`, `plural`)
VALUES (9181, 'Department', 'Departments');
INSERT INTO `sub_accounts`
VALUES (1, 'Oncology', 'Oncology', '#f7a3d7', 0, 9181),
       (2, 'Renal', 'Renal', '#ffcc75', 0, 9181),
       (3, 'Cardiology', 'Cardiology', '#99ffde', 0, 9181),
       (4, 'ENT', 'ENT', '#5fafbf', 0, 9181),
       (5, 'Neurology', 'Neurology', '#08d6ad', 0, 9181);
