
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- auditing
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `auditing`;

CREATE TABLE `auditing`
(
    `data` TEXT NOT NULL,
    `user_id` INTEGER NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `auditing_fi_69bd79` (`user_id`),
    CONSTRAINT `auditing_fk_69bd79`
        FOREIGN KEY (`user_id`)
        REFERENCES `users` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- companies
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `companies`;

CREATE TABLE `companies`
(
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `phone` TEXT NOT NULL,
    `alternateNumber` TEXT,
    `address` TEXT,
    `postcode` TEXT,
    `state` TEXT,
    `country` TEXT,
    `timezone` TEXT,
    `contactfirstname` TEXT NOT NULL,
    `contactlastname` TEXT NOT NULL,
    `billinggroupid` TEXT,
    `language_id` INTEGER,
    `phoneassigned` TINYINT(1),
    `notify` TINYINT(1),
    `status` TINYINT(1),
    `logo` INTEGER,
    `user_fields` TEXT,
    `extra` TEXT,
    `tree_left` INTEGER,
    `tree_right` INTEGER,
    `tree_level` INTEGER,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `email` (`email`),
    INDEX `companies_fi_3ed31d` (`language_id`),
    INDEX `companies_fi_ab78ed` (`logo`),
    CONSTRAINT `companies_fk_3ed31d`
        FOREIGN KEY (`language_id`)
        REFERENCES `language_types` (`id`),
    CONSTRAINT `companies_fk_ab78ed`
        FOREIGN KEY (`logo`)
        REFERENCES `media` (`id`)
        ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- users
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users`
(
    `owner` INTEGER NOT NULL,
    `client` INTEGER NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(255) NOT NULL,
    `pin` INTEGER(32),
    `passwordHash` TEXT NOT NULL,
    `firstName` TEXT NOT NULL,
    `lastName` TEXT NOT NULL,
    `address` TEXT NOT NULL,
    `city` TEXT NOT NULL,
    `state` TEXT NOT NULL,
    `country` TEXT NOT NULL,
    `postcode` TEXT NOT NULL,
    `apiKey` VARCHAR(38) NOT NULL,
    `uuid` VARCHAR(36) NOT NULL,
    `status` TINYINT(1) NOT NULL,
    `active_at` TIME,
    `image` INTEGER,
    `subscriptionid` INTEGER,
    `phoneid` INTEGER NOT NULL,
    `recurring` TINYINT(1),
    `paymentcode` VARCHAR(255),
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `email` (`email`),
    INDEX `users_i_060963` (`owner`),
    INDEX `users_i_4d162c` (`client`),
    INDEX `users_i_36f940` (`subscriptionid`),
    INDEX `users_fi_05b03c` (`image`),
    INDEX `users_fi_51e636` (`phoneid`),
    CONSTRAINT `users_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `users_fk_16a5b8`
        FOREIGN KEY (`client`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `users_fk_05b03c`
        FOREIGN KEY (`image`)
        REFERENCES `media` (`id`)
        ON DELETE SET NULL,
    CONSTRAINT `users_fk_8d072e`
        FOREIGN KEY (`subscriptionid`)
        REFERENCES `subscriptions` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `users_fk_51e636`
        FOREIGN KEY (`phoneid`)
        REFERENCES `phone_numbers` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- user_companies
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `user_companies`;

CREATE TABLE `user_companies`
(
    `owner` INTEGER,
    `userXref` INTEGER NOT NULL,
    `start` TIME NOT NULL,
    `end` TIME NOT NULL,
    `extra` TEXT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`userXref`),
    INDEX `user_companies_fi_7162aa` (`owner`),
    CONSTRAINT `user_companies_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `user_companies_fk_e22380`
        FOREIGN KEY (`userXref`)
        REFERENCES `users` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- user_types
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `user_types`;

CREATE TABLE `user_types`
(
    `name` TEXT NOT NULL,
    `code` TEXT NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- features
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `features`;

CREATE TABLE `features`
(
    `component` VARCHAR(32) NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- features_labels
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `features_labels`;

CREATE TABLE `features_labels`
(
    `feature_id` INTEGER NOT NULL,
    `label_id` INTEGER NOT NULL,
    `power` INTEGER DEFAULT 0 NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `features_labels_fi_e9d98a` (`feature_id`),
    INDEX `features_labels_fi_7a2262` (`label_id`),
    CONSTRAINT `features_labels_fk_e9d98a`
        FOREIGN KEY (`feature_id`)
        REFERENCES `features` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `features_labels_fk_7a2262`
        FOREIGN KEY (`label_id`)
        REFERENCES `labels` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- products
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products`
(
    `name` TEXT NOT NULL,
    `monthlycost` TEXT NOT NULL,
    `period` TEXT NOT NULL,
    `price` TEXT NOT NULL,
    `gst` TEXT NOT NULL,
    `total` TEXT NOT NULL,
    `savings` TEXT NOT NULL,
    `plan` TEXT,
    `status` TINYINT(1) NOT NULL,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- invoices
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `invoices`;

CREATE TABLE `invoices`
(
    `owner` INTEGER NOT NULL,
    `client` INTEGER NOT NULL,
    `productid` INTEGER NOT NULL,
    `userid` INTEGER NOT NULL,
    `amount` INTEGER NOT NULL,
    `pdf` TEXT,
    `status` TEXT,
    `date` DATE,
    `expires` DATE,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `invoices_i_060963` (`owner`),
    INDEX `invoices_i_4d162c` (`client`),
    INDEX `invoices_i_7c0b83` (`productid`),
    INDEX `invoices_fi_2596c7` (`userid`),
    CONSTRAINT `invoices_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `invoices_fk_16a5b8`
        FOREIGN KEY (`client`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `invoices_fk_7d6e0d`
        FOREIGN KEY (`productid`)
        REFERENCES `products` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `invoices_fk_2596c7`
        FOREIGN KEY (`userid`)
        REFERENCES `users` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- payments
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `payments`;

CREATE TABLE `payments`
(
    `owner` INTEGER NOT NULL,
    `productid` INTEGER NOT NULL,
    `client` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    `invoiceid` INTEGER NOT NULL,
    `pdf` TEXT,
    `amount` TEXT,
    `recepit` TEXT,
    `date` DATE,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `payments_i_060963` (`owner`),
    INDEX `payments_i_7c0b83` (`productid`),
    INDEX `payments_i_4d162c` (`client`),
    INDEX `payments_i_b7e02f` (`invoiceid`),
    INDEX `payments_fi_69bd79` (`user_id`),
    CONSTRAINT `payments_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `payments_fk_7d6e0d`
        FOREIGN KEY (`productid`)
        REFERENCES `products` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `payments_fk_16a5b8`
        FOREIGN KEY (`client`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `payments_fk_69bd79`
        FOREIGN KEY (`user_id`)
        REFERENCES `users` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `payments_fk_492d46`
        FOREIGN KEY (`invoiceid`)
        REFERENCES `invoices` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- subscriptions
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `subscriptions`;

CREATE TABLE `subscriptions`
(
    `owner` INTEGER NOT NULL,
    `client` INTEGER NOT NULL,
    `productid` INTEGER NOT NULL,
    `phoneid` INTEGER NOT NULL,
    `invoiceid` INTEGER NOT NULL,
    `paymentid` INTEGER NOT NULL,
    `start_date` DATE NOT NULL,
    `expiry_date` DATE NOT NULL,
    `status` VARCHAR(255) NOT NULL,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `subscriptions_i_060963` (`owner`),
    INDEX `subscriptions_i_4d162c` (`client`),
    INDEX `subscriptions_i_7c0b83` (`productid`),
    INDEX `subscriptions_fi_51e636` (`phoneid`),
    INDEX `subscriptions_fi_492d46` (`invoiceid`),
    INDEX `subscriptions_fi_df916c` (`paymentid`),
    CONSTRAINT `subscriptions_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `subscriptions_fk_16a5b8`
        FOREIGN KEY (`client`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `subscriptions_fk_7d6e0d`
        FOREIGN KEY (`productid`)
        REFERENCES `products` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `subscriptions_fk_51e636`
        FOREIGN KEY (`phoneid`)
        REFERENCES `phone_numbers` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `subscriptions_fk_492d46`
        FOREIGN KEY (`invoiceid`)
        REFERENCES `invoices` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `subscriptions_fk_df916c`
        FOREIGN KEY (`paymentid`)
        REFERENCES `payments` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- language_types
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `language_types`;

CREATE TABLE `language_types`
(
    `name` TEXT NOT NULL,
    `code` TEXT NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- phone_numbers
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `phone_numbers`;

CREATE TABLE `phone_numbers`
(
    `number` TEXT NOT NULL,
    `sipconnection` TEXT NOT NULL,
    `username` TEXT NOT NULL,
    `telnyxpassword` TEXT NOT NULL,
    `status` TEXT NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- outbound
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `outbound`;

CREATE TABLE `outbound`
(
    `owner` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    `duration` TEXT NOT NULL,
    `client` INTEGER NOT NULL,
    `phone_id` INTEGER NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `outbound_i_060963` (`owner`),
    INDEX `outbound_i_4d162c` (`client`),
    INDEX `outbound_fi_69bd79` (`user_id`),
    INDEX `outbound_fi_1db830` (`phone_id`),
    CONSTRAINT `outbound_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `outbound_fk_69bd79`
        FOREIGN KEY (`user_id`)
        REFERENCES `users` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `outbound_fk_16a5b8`
        FOREIGN KEY (`client`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `outbound_fk_1db830`
        FOREIGN KEY (`phone_id`)
        REFERENCES `phone_numbers` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- inbound
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `inbound`;

CREATE TABLE `inbound`
(
    `owner` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    `duration` TEXT NOT NULL,
    `client` INTEGER NOT NULL,
    `phone_id` INTEGER NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `inbound_i_060963` (`owner`),
    INDEX `inbound_i_4d162c` (`client`),
    INDEX `inbound_fi_69bd79` (`user_id`),
    INDEX `inbound_fi_1db830` (`phone_id`),
    CONSTRAINT `inbound_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `inbound_fk_69bd79`
        FOREIGN KEY (`user_id`)
        REFERENCES `users` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `inbound_fk_16a5b8`
        FOREIGN KEY (`client`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `inbound_fk_1db830`
        FOREIGN KEY (`phone_id`)
        REFERENCES `phone_numbers` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- phone_notes
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `phone_notes`;

CREATE TABLE `phone_notes`
(
    `phone_id` INTEGER NOT NULL,
    `owner` INTEGER NOT NULL,
    `userid` INTEGER NOT NULL,
    `message` TEXT NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `phone_notes_i_060963` (`owner`),
    INDEX `phone_notes_fi_1db830` (`phone_id`),
    INDEX `phone_notes_fi_2596c7` (`userid`),
    CONSTRAINT `phone_notes_fk_1db830`
        FOREIGN KEY (`phone_id`)
        REFERENCES `phone_numbers` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `phone_notes_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `phone_notes_fk_2596c7`
        FOREIGN KEY (`userid`)
        REFERENCES `users` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- calls
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `calls`;

CREATE TABLE `calls`
(
    `owner` INTEGER NOT NULL,
    `client` INTEGER NOT NULL,
    `phone_id` INTEGER NOT NULL,
    `name` TEXT NOT NULL,
    `day` DATE NOT NULL,
    `start` DATETIME NOT NULL,
    `end` DATETIME NOT NULL,
    `assignee` INTEGER,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `calls_i_060963` (`owner`),
    INDEX `calls_i_4d162c` (`client`),
    INDEX `calls_i_afcf6a` (`assignee`),
    INDEX `calls_fi_1db830` (`phone_id`),
    CONSTRAINT `calls_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `calls_fk_16a5b8`
        FOREIGN KEY (`client`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `calls_fk_1db830`
        FOREIGN KEY (`phone_id`)
        REFERENCES `phone_numbers` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `calls_fk_b2f249`
        FOREIGN KEY (`assignee`)
        REFERENCES `users` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- messages
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `messages`;

CREATE TABLE `messages`
(
    `title` TEXT NOT NULL,
    `body` TEXT NOT NULL,
    `from` INTEGER NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `messages_fi_b1eacf` (`from`),
    CONSTRAINT `messages_fk_b1eacf`
        FOREIGN KEY (`from`)
        REFERENCES `users` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- users_messages
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `users_messages`;

CREATE TABLE `users_messages`
(
    `userXref` INTEGER NOT NULL,
    `messageXref` INTEGER NOT NULL,
    `read` TINYINT(1) DEFAULT false NOT NULL,
    `extra` TEXT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`userXref`,`messageXref`),
    INDEX `users_messages_fi_c46f67` (`messageXref`),
    CONSTRAINT `users_messages_fk_e22380`
        FOREIGN KEY (`userXref`)
        REFERENCES `users` (`id`),
    CONSTRAINT `users_messages_fk_c46f67`
        FOREIGN KEY (`messageXref`)
        REFERENCES `messages` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- media
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `media`;

CREATE TABLE `media`
(
    `user` INTEGER NOT NULL,
    `owner` INTEGER,
    `client` INTEGER NOT NULL,
    `type` TEXT NOT NULL,
    `section` TEXT NOT NULL,
    `disposition` TEXT NOT NULL,
    `url` TEXT NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `media_fi_9c85ee` (`user`),
    INDEX `media_fi_7162aa` (`owner`),
    INDEX `media_fi_16a5b8` (`client`),
    CONSTRAINT `media_fk_9c85ee`
        FOREIGN KEY (`user`)
        REFERENCES `users` (`id`),
    CONSTRAINT `media_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `media_fk_16a5b8`
        FOREIGN KEY (`client`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- labels
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `labels`;

CREATE TABLE `labels`
(
    `owner` INTEGER NOT NULL,
    `power` INTEGER DEFAULT 0 NOT NULL,
    `name` TEXT NOT NULL,
    `colour` TEXT,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `labels_i_060963` (`owner`),
    CONSTRAINT `labels_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- companies_labels
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `companies_labels`;

CREATE TABLE `companies_labels`
(
    `companyXref` INTEGER NOT NULL,
    `labelXref` INTEGER NOT NULL,
    `power` INTEGER DEFAULT 0 NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `companies_labels_fi_ceacff` (`companyXref`),
    INDEX `companies_labels_fi_a2ca27` (`labelXref`),
    CONSTRAINT `companies_labels_fk_ceacff`
        FOREIGN KEY (`companyXref`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `companies_labels_fk_a2ca27`
        FOREIGN KEY (`labelXref`)
        REFERENCES `labels` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- Invoices_labels
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `Invoices_labels`;

CREATE TABLE `Invoices_labels`
(
    `invoiceXref` INTEGER NOT NULL,
    `labelXref` INTEGER NOT NULL,
    `power` INTEGER DEFAULT 0 NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `Invoices_labels_fi_8e706a` (`invoiceXref`),
    INDEX `Invoices_labels_fi_a2ca27` (`labelXref`),
    CONSTRAINT `Invoices_labels_fk_8e706a`
        FOREIGN KEY (`invoiceXref`)
        REFERENCES `invoices` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `Invoices_labels_fk_a2ca27`
        FOREIGN KEY (`labelXref`)
        REFERENCES `labels` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- payments_labels
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `payments_labels`;

CREATE TABLE `payments_labels`
(
    `paymentXref` INTEGER NOT NULL,
    `labelXref` INTEGER NOT NULL,
    `power` INTEGER DEFAULT 0 NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `payments_labels_fi_c88ff8` (`paymentXref`),
    INDEX `payments_labels_fi_a2ca27` (`labelXref`),
    CONSTRAINT `payments_labels_fk_c88ff8`
        FOREIGN KEY (`paymentXref`)
        REFERENCES `payments` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `payments_labels_fk_a2ca27`
        FOREIGN KEY (`labelXref`)
        REFERENCES `labels` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- subscriptions_labels
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `subscriptions_labels`;

CREATE TABLE `subscriptions_labels`
(
    `subscriptionXref` INTEGER NOT NULL,
    `labelXref` INTEGER NOT NULL,
    `power` INTEGER DEFAULT 0 NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `subscriptions_labels_fi_0c0443` (`subscriptionXref`),
    INDEX `subscriptions_labels_fi_a2ca27` (`labelXref`),
    CONSTRAINT `subscriptions_labels_fk_0c0443`
        FOREIGN KEY (`subscriptionXref`)
        REFERENCES `subscriptions` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `subscriptions_labels_fk_a2ca27`
        FOREIGN KEY (`labelXref`)
        REFERENCES `labels` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- users_labels
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `users_labels`;

CREATE TABLE `users_labels`
(
    `userXref` INTEGER NOT NULL,
    `labelXref` INTEGER NOT NULL,
    `power` INTEGER DEFAULT 0 NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `users_labels_fi_e22380` (`userXref`),
    INDEX `users_labels_fi_a2ca27` (`labelXref`),
    CONSTRAINT `users_labels_fk_e22380`
        FOREIGN KEY (`userXref`)
        REFERENCES `users` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `users_labels_fk_a2ca27`
        FOREIGN KEY (`labelXref`)
        REFERENCES `labels` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- media_labels
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `media_labels`;

CREATE TABLE `media_labels`
(
    `mediaXref` INTEGER NOT NULL,
    `labelXref` INTEGER NOT NULL,
    `power` INTEGER DEFAULT 0 NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `media_labels_fi_8d84cd` (`mediaXref`),
    INDEX `media_labels_fi_a2ca27` (`labelXref`),
    CONSTRAINT `media_labels_fk_8d84cd`
        FOREIGN KEY (`mediaXref`)
        REFERENCES `media` (`id`)
        ON DELETE CASCADE,
    CONSTRAINT `media_labels_fk_a2ca27`
        FOREIGN KEY (`labelXref`)
        REFERENCES `labels` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- roles
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `roles`;

CREATE TABLE `roles`
(
    `owner` INTEGER NOT NULL,
    `name` TEXT NOT NULL,
    `colour` TEXT,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `roles_fi_7162aa` (`owner`),
    CONSTRAINT `roles_fk_7162aa`
        FOREIGN KEY (`owner`)
        REFERENCES `companies` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- roles_labels
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `roles_labels`;

CREATE TABLE `roles_labels`
(
    `role_id` INTEGER NOT NULL,
    `label_id` INTEGER NOT NULL,
    `power` INTEGER DEFAULT 0 NOT NULL,
    `extra` TEXT,
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `roles_labels_fi_06a84f` (`role_id`),
    INDEX `roles_labels_fi_7a2262` (`label_id`),
    CONSTRAINT `roles_labels_fk_06a84f`
        FOREIGN KEY (`role_id`)
        REFERENCES `roles` (`id`),
    CONSTRAINT `roles_labels_fk_7a2262`
        FOREIGN KEY (`label_id`)
        REFERENCES `labels` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
