
# 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;

-- ---------------------------------------------------------------------
-- bsd_code
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `bsd_code`;

CREATE TABLE `bsd_code`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `waste_type` VARCHAR(255),
    `nomenclature` VARCHAR(32),
    `code` VARCHAR(32),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- carrier
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `carrier`;

CREATE TABLE `carrier`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `manufacturer_id` INTEGER NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `status` INTEGER,
    `telephone` VARCHAR(255),
    `created_by` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `carrier_I_1` (`name`),
    INDEX `carrier_FI_1` (`manufacturer_id`),
    INDEX `carrier_FI_2` (`created_by`),
    CONSTRAINT `carrier_FK_1`
        FOREIGN KEY (`manufacturer_id`)
        REFERENCES `user` (`id`),
    CONSTRAINT `carrier_FK_2`
        FOREIGN KEY (`created_by`)
        REFERENCES `user` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- civility
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `civility`;

CREATE TABLE `civility`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- civility_i18n
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `civility_i18n`;

CREATE TABLE `civility_i18n`
(
    `id` INTEGER NOT NULL,
    `culture` VARCHAR(7) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`,`culture`),
    CONSTRAINT `civility_i18n_FK_1`
        FOREIGN KEY (`id`)
        REFERENCES `civility` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- config
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `config`;

CREATE TABLE `config`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(32),
    `siret` VARCHAR(255),
    `address` VARCHAR(255),
    `postal_code` VARCHAR(255),
    `city` VARCHAR(255),
    `email` VARCHAR(255),
    `telephone` VARCHAR(255),
    `fax` VARCHAR(255),
    `contact_name` VARCHAR(255),
    `recipisse_number` VARCHAR(255),
    `department` VARCHAR(255),
    `validite` VARCHAR(255),
    `signature` VARCHAR(255),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- driver
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `driver`;

CREATE TABLE `driver`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `carrier_id` INTEGER,
    `name` VARCHAR(255) NOT NULL,
    `telephone` VARCHAR(255),
    `created_by` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `driver_FI_1` (`carrier_id`),
    INDEX `driver_FI_2` (`created_by`),
    CONSTRAINT `driver_FK_1`
        FOREIGN KEY (`carrier_id`)
        REFERENCES `carrier` (`id`),
    CONSTRAINT `driver_FK_2`
        FOREIGN KEY (`created_by`)
        REFERENCES `user` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- holder
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `holder`;

CREATE TABLE `holder`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `manufacturer_id` INTEGER NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `address` TEXT,
    `postal_code` VARCHAR(255),
    `city` VARCHAR(255),
    `telephone` VARCHAR(255),
    `email` VARCHAR(255),
    `siret` VARCHAR(255),
    `created_by` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `holder_FI_1` (`manufacturer_id`),
    INDEX `holder_FI_2` (`created_by`),
    CONSTRAINT `holder_FK_1`
        FOREIGN KEY (`manufacturer_id`)
        REFERENCES `user` (`id`),
    CONSTRAINT `holder_FK_2`
        FOREIGN KEY (`created_by`)
        REFERENCES `user` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- holder_contact
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `holder_contact`;

CREATE TABLE `holder_contact`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `holder_id` INTEGER,
    `name` VARCHAR(255),
    `firstname` VARCHAR(255),
    `address` TEXT,
    `postal_code` VARCHAR(255),
    `city` VARCHAR(255),
    `telephone` VARCHAR(255),
    `fax` VARCHAR(255),
    `email` VARCHAR(255),
    PRIMARY KEY (`id`),
    INDEX `holder_contact_FI_1` (`holder_id`),
    CONSTRAINT `holder_contact_FK_1`
        FOREIGN KEY (`holder_id`)
        REFERENCES `holder` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- job
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `job`;

CREATE TABLE `job`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `internal_id` VARCHAR(32),
    `center_id` INTEGER,
    `manufacturer_id` INTEGER NOT NULL,
    `holder_id` INTEGER,
    `holder_contact_id` INTEGER,
    `carrier_id` INTEGER,
    `status` INTEGER,
    `taking_at` DATETIME,
    `invoice` VARCHAR(255),
    `created_by` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `job_FI_1` (`center_id`),
    INDEX `job_FI_2` (`manufacturer_id`),
    INDEX `job_FI_3` (`holder_id`),
    INDEX `job_FI_4` (`holder_contact_id`),
    INDEX `job_FI_5` (`carrier_id`),
    INDEX `job_FI_6` (`created_by`),
    CONSTRAINT `job_FK_1`
        FOREIGN KEY (`center_id`)
        REFERENCES `user` (`id`),
    CONSTRAINT `job_FK_2`
        FOREIGN KEY (`manufacturer_id`)
        REFERENCES `user` (`id`),
    CONSTRAINT `job_FK_3`
        FOREIGN KEY (`holder_id`)
        REFERENCES `holder` (`id`),
    CONSTRAINT `job_FK_4`
        FOREIGN KEY (`holder_contact_id`)
        REFERENCES `holder_contact` (`id`),
    CONSTRAINT `job_FK_5`
        FOREIGN KEY (`carrier_id`)
        REFERENCES `carrier` (`id`),
    CONSTRAINT `job_FK_6`
        FOREIGN KEY (`created_by`)
        REFERENCES `user` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- job_bsd
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `job_bsd`;

CREATE TABLE `job_bsd`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `job_id` INTEGER,
    `waste_code` VARCHAR(255),
    `weight` INTEGER,
    `bsd_final` VARCHAR(255),
    PRIMARY KEY (`id`),
    INDEX `job_bsd_FI_1` (`job_id`),
    CONSTRAINT `job_bsd_FK_1`
        FOREIGN KEY (`job_id`)
        REFERENCES `job` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- user
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `culture` VARCHAR(2),
    `slug` VARCHAR(255),
    `name` VARCHAR(255),
    `is_admin` TINYINT(1),
    `user_role` INTEGER,
    `status` INTEGER,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `civility_id` INTEGER,
    `siret` VARCHAR(255),
    `address` TEXT,
    `postal_code` VARCHAR(255),
    `city` VARCHAR(255),
    `telephone` VARCHAR(255),
    `fax` VARCHAR(255),
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `user_FI_1` (`civility_id`),
    CONSTRAINT `user_FK_1`
        FOREIGN KEY (`civility_id`)
        REFERENCES `civility` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- user_contact
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `user_contact`;

CREATE TABLE `user_contact`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER,
    `name` VARCHAR(255),
    `email` VARCHAR(255) NOT NULL,
    `civility_id` INTEGER,
    `address` TEXT,
    `postal_code` VARCHAR(255),
    `city` VARCHAR(255),
    `telephone` VARCHAR(255),
    `fax` VARCHAR(255),
    PRIMARY KEY (`id`),
    INDEX `user_contact_FI_1` (`user_id`),
    INDEX `user_contact_FI_2` (`civility_id`),
    CONSTRAINT `user_contact_FK_1`
        FOREIGN KEY (`user_id`)
        REFERENCES `user` (`id`),
    CONSTRAINT `user_contact_FK_2`
        FOREIGN KEY (`civility_id`)
        REFERENCES `civility` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- waste
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `waste`;

CREATE TABLE `waste`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `job_id` INTEGER,
    `make` VARCHAR(255),
    `category` VARCHAR(255),
    `model` VARCHAR(255),
    `quantity` INTEGER,
    `rubrique` VARCHAR(255),
    `serial_number` VARCHAR(255),
    `internal_reference` VARCHAR(255),
    `status` INTEGER,
    `weight_estimated` INTEGER,
    `weight_confirmed` INTEGER,
    `created_by` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `waste_FI_1` (`job_id`),
    INDEX `waste_FI_2` (`created_by`),
    CONSTRAINT `waste_FK_1`
        FOREIGN KEY (`job_id`)
        REFERENCES `job` (`id`),
    CONSTRAINT `waste_FK_2`
        FOREIGN KEY (`created_by`)
        REFERENCES `user` (`id`)
) ENGINE=InnoDB;

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