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

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

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user`
(
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `name` TEXT NOT NULL,
    `login` VARCHAR(50) NOT NULL,
    `password` VARCHAR(50) NOT NULL,
    `email` TEXT NOT NULL,
    `phone` TEXT NOT NULL,
    `title` TEXT NOT NULL,
    `address` TEXT NOT NULL,
    `photo` TEXT NOT NULL,
    `active` TINYINT(1) NOT NULL,
    `superuser` TINYINT(1) NOT NULL,
    `last_login` DATETIME NOT NULL,
    `owner_id` BIGINT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `user_I_1` (`login`),
    INDEX `user_FI_1` (`owner_id`),
    CONSTRAINT `user_FK_1`
        FOREIGN KEY (`owner_id`)
        REFERENCES `sysgroup` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- role
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` TEXT NOT NULL,
    `privileges` TEXT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- role_grant_role
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `role_grant_role`;

CREATE TABLE `role_grant_role`
(
    `role_id` BIGINT(20) DEFAULT 0 NOT NULL,
    `grant_role_id` BIGINT(20) DEFAULT 0 NOT NULL,
    PRIMARY KEY (`role_id`,`grant_role_id`),
    INDEX `role_grant_role_FI_1` (`grant_role_id`),
    CONSTRAINT `role_grant_role_FK_1`
        FOREIGN KEY (`grant_role_id`)
        REFERENCES `role` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `role_grant_role_FK_2`
        FOREIGN KEY (`role_id`)
        REFERENCES `role` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- user_role
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role`
(
    `role_id` BIGINT(20) DEFAULT 0 NOT NULL,
    `user_id` BIGINT(20) DEFAULT 0 NOT NULL,
    PRIMARY KEY (`role_id`,`user_id`),
    INDEX `user_role_FI_1` (`user_id`),
    CONSTRAINT `user_role_FK_1`
        FOREIGN KEY (`user_id`)
        REFERENCES `user` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `user_role_FK_2`
        FOREIGN KEY (`role_id`)
        REFERENCES `role` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- sysgroup
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `sysgroup`;

CREATE TABLE `sysgroup`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` TEXT NOT NULL,
    `lft` INTEGER(11) NOT NULL,
    `rgt` INTEGER(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- user_group
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `user_group`;

CREATE TABLE `user_group`
(
    `group_id` BIGINT(20) DEFAULT 0 NOT NULL,
    `user_id` BIGINT(20) DEFAULT 0 NOT NULL,
    PRIMARY KEY (`group_id`,`user_id`),
    INDEX `user_id` (`user_id`),
    CONSTRAINT `user_group_ibfk_1`
        FOREIGN KEY (`user_id`)
        REFERENCES `user` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `user_group_ibfk_2`
        FOREIGN KEY (`group_id`)
        REFERENCES `sysgroup` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- email
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `email`;

CREATE TABLE `email`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `service` VARCHAR(100) NOT NULL,
    `time_created` DATETIME NOT NULL,
    `from` VARCHAR(100) NOT NULL,
    `to` TEXT NOT NULL,
    `subject` VARCHAR(200) NOT NULL,
    `header` TEXT NOT NULL,
    `body_text` TEXT NOT NULL,
    `body_html` TEXT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- company
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `company`;

CREATE TABLE `company`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL,
    `email` VARCHAR(100) NOT NULL,
    `description` TEXT NOT NULL,
    `active` TINYINT(1) DEFAULT 1 NOT NULL,
    `send_report` TINYINT(1) DEFAULT 0 NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `company_I_1` (`name`),
    INDEX `company_I_2` (`active`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- building
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `building`;

CREATE TABLE `building`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `local_number` VARCHAR(10) NOT NULL,
    `local_om` VARCHAR(10) NOT NULL,
    `description` TEXT NOT NULL,
    `intention` VARCHAR(50) NOT NULL,
    `status` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- meter
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `meter`;

CREATE TABLE `meter`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `serial_number` VARCHAR(50) NOT NULL,
    `type_energy` VARCHAR(20) NOT NULL,
    `internal_number` VARCHAR(50) NOT NULL,
    `description` TEXT NOT NULL,
    `status` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- source
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `source`;

CREATE TABLE `source`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `description` TEXT NOT NULL,
    `status` VARCHAR(50) NOT NULL,
    `type_energy` VARCHAR(20) NOT NULL,
    `parent_id` BIGINT(20),
    PRIMARY KEY (`id`),
    INDEX `source_FI_1` (`parent_id`),
    CONSTRAINT `source_FK_1`
        FOREIGN KEY (`parent_id`)
        REFERENCES `source` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- point
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `point`;

CREATE TABLE `point`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `type_reading` VARCHAR(20) NOT NULL,
    `status` VARCHAR(50) NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `om` VARCHAR(20) NOT NULL,
    `company_id` BIGINT(20),
    `building_id` BIGINT(20),
    `meter_id` BIGINT(20) NOT NULL,
    `source_id` BIGINT(20) NOT NULL,
    `name_exit` VARCHAR(50) NOT NULL,
    `protection` VARCHAR(50) NOT NULL,
    `location` VARCHAR(50) NOT NULL,
    `tarif` VARCHAR(20) NOT NULL,
    `description` TEXT NOT NULL,
    `ord` VARCHAR(10) NOT NULL,
    `constant` BIGINT(20) DEFAULT 1 NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `point_FI_1` (`company_id`),
    INDEX `point_FI_2` (`building_id`),
    INDEX `point_FI_3` (`meter_id`),
    INDEX `point_FI_4` (`source_id`),
    CONSTRAINT `point_FK_1`
        FOREIGN KEY (`company_id`)
        REFERENCES `company` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `point_FK_2`
        FOREIGN KEY (`building_id`)
        REFERENCES `building` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `point_FK_3`
        FOREIGN KEY (`meter_id`)
        REFERENCES `meter` (`id`)
        ON UPDATE CASCADE,
    CONSTRAINT `point_FK_4`
        FOREIGN KEY (`source_id`)
        REFERENCES `source` (`id`)
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- merge_point
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `merge_point`;

CREATE TABLE `merge_point`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `point_a_id` BIGINT(20) NOT NULL,
    `point_b_id` BIGINT(20) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `merge_point_FI_1` (`point_a_id`),
    INDEX `merge_point_FI_2` (`point_b_id`),
    CONSTRAINT `merge_point_FK_1`
        FOREIGN KEY (`point_a_id`)
        REFERENCES `point` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `merge_point_FK_2`
        FOREIGN KEY (`point_b_id`)
        REFERENCES `point` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- reading
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `reading`;

CREATE TABLE `reading`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `year` VARCHAR(4) NOT NULL,
    `month` VARCHAR(2) NOT NULL,
    `status_reading` VARCHAR(50) NOT NULL,
    `status_loss` VARCHAR(50) NOT NULL,
    `description` TEXT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- reading_item
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `reading_item`;

CREATE TABLE `reading_item`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `reading_id` BIGINT(20) NOT NULL,
    `point_id` BIGINT(20) NOT NULL,
    `old_state` VARCHAR(50) NOT NULL,
    `new_state` VARCHAR(50) NOT NULL,
    `diff` VARCHAR(50) NOT NULL,
    `result` VARCHAR(50) NOT NULL,
    `status` VARCHAR(50) NOT NULL,
    `description` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `reading_item_FI_1` (`reading_id`),
    INDEX `reading_item_FI_2` (`point_id`),
    CONSTRAINT `reading_item_FK_1`
        FOREIGN KEY (`reading_id`)
        REFERENCES `reading` (`id`)
        ON UPDATE CASCADE,
    CONSTRAINT `reading_item_FK_2`
        FOREIGN KEY (`point_id`)
        REFERENCES `point` (`id`)
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- loss
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `loss`;

CREATE TABLE `loss`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(20) NOT NULL,
    `type_reading` VARCHAR(20) NOT NULL,
    `status` VARCHAR(50) NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `meter_id` BIGINT(20),
    `source_id` BIGINT(20) NOT NULL,
    `description` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `loss_FI_1` (`meter_id`),
    INDEX `loss_FI_2` (`source_id`),
    CONSTRAINT `loss_FK_1`
        FOREIGN KEY (`meter_id`)
        REFERENCES `meter` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `loss_FK_2`
        FOREIGN KEY (`source_id`)
        REFERENCES `source` (`id`)
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- loss_item
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `loss_item`;

CREATE TABLE `loss_item`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `reading_id` BIGINT(20) NOT NULL,
    `loss_id` BIGINT(20) NOT NULL,
    `value` VARCHAR(50) NOT NULL,
    `status` VARCHAR(50) NOT NULL,
    `description` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `loss_item_FI_1` (`reading_id`),
    INDEX `loss_item_FI_2` (`loss_id`),
    CONSTRAINT `loss_item_FK_1`
        FOREIGN KEY (`reading_id`)
        REFERENCES `reading` (`id`)
        ON UPDATE CASCADE,
    CONSTRAINT `loss_item_FK_2`
        FOREIGN KEY (`loss_id`)
        REFERENCES `loss` (`id`)
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- balance
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `balance`;

CREATE TABLE `balance`
(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `reading_id` BIGINT(20) NOT NULL,
    `type_energy` VARCHAR(50) NOT NULL,
    `purchase` VARCHAR(50) NOT NULL,
    `sold` VARCHAR(50) NOT NULL,
    `own` VARCHAR(50) NOT NULL,
    `loss` VARCHAR(50) NOT NULL,
    `not_measured` VARCHAR(50) NOT NULL,
    `efectivity` VARCHAR(50) NOT NULL,
    `status` VARCHAR(50) NOT NULL,
    `description` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `balance_FI_1` (`reading_id`),
    CONSTRAINT `balance_FK_1`
        FOREIGN KEY (`reading_id`)
        REFERENCES `reading` (`id`)
        ON UPDATE CASCADE
) ENGINE=InnoDB;

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