Mon forum PunBB

-- MySQL Script generated by MySQL Workbench
-- jeu. 28 févr. 2019 09:00:26 CET
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema paysagest
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `paysagest` ;

-- -----------------------------------------------------
-- Schema paysagest
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `paysagest` DEFAULT CHARACTER SET utf8 ;
USE `paysagest` ;

-- -----------------------------------------------------
-- Table `paysagest`.`entreprise`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`entreprise` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`entreprise` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `siret` VARCHAR(14) NOT NULL,
  `nom` VARCHAR(255) NULL,
  `ape` VARCHAR(25) NULL,
  `tva_intracom` VARCHAR(45) NULL,
  `adresse` VARCHAR(255) NULL,
  `suite_adresse` VARCHAR(255) NULL,
  `cp` VARCHAR(16) NULL,
  `ville` VARCHAR(150) NULL,
  `tel` VARCHAR(45) NULL,
  `portable` VARCHAR(45) NULL,
  `email` VARCHAR(255) NULL,
  `regime_commercial` VARCHAR(150) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `siret_UNIQUE` (`siret` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`role`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`role` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`role` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`user` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entreprise_id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(32) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`, `entreprise_id`),
  INDEX `fk_user_entreprise_idx` (`entreprise_id` ASC),
  INDEX `fk_user_role_idx` (`role_id` ASC),
  CONSTRAINT `fk_user_entreprise`
    FOREIGN KEY (`entreprise_id`)
    REFERENCES `paysagest`.`entreprise` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_role`
    FOREIGN KEY (`role_id`)
    REFERENCES `paysagest`.`role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`civilite`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`civilite` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`civilite` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `libelle` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`client`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`client` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`client` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entreprise_id` INT UNSIGNED NOT NULL,
  `code_client` VARCHAR(32) NOT NULL,
  `civilite_id` INT UNSIGNED NULL,
  `nom` VARCHAR(64) NOT NULL,
  `prenom` VARCHAR(32) NULL,
  `tel` VARCHAR(32) NULL,
  `portable` VARCHAR(32) NULL,
  `email` VARCHAR(255) NULL,
  `tva_intracom` VARCHAR(32) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`, `entreprise_id`),
  UNIQUE INDEX `code_client_UNIQUE` (`code_client` ASC),
  INDEX `fk_client_civilite_idx` (`civilite_id` ASC),
  INDEX `fk_client_entreprise_idx` (`entreprise_id` ASC),
  CONSTRAINT `fk_client_entreprise`
    FOREIGN KEY (`entreprise_id`)
    REFERENCES `paysagest`.`entreprise` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_client_civilite`
    FOREIGN KEY (`civilite_id`)
    REFERENCES `paysagest`.`civilite` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`rib`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`rib` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`rib` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` INT UNSIGNED NOT NULL,
  `nom_banque` VARCHAR(64) NULL,
  `nom_agence` VARCHAR(64) NULL,
  `code_banque` VARCHAR(5) NOT NULL,
  `code_guichet` VARCHAR(5) NOT NULL,
  `num_compte` VARCHAR(11) NOT NULL,
  `cle_rib` VARCHAR(2) NOT NULL,
  `iban` VARCHAR(34) NOT NULL,
  `bic` VARCHAR(11) NOT NULL,
  PRIMARY KEY (`id`, `client_id`),
  INDEX `fk_rib_client_idx` (`client_id` ASC),
  CONSTRAINT `fk_rib_client`
    FOREIGN KEY (`client_id`)
    REFERENCES `paysagest`.`client` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`tva`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`tva` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`tva` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code_tva` INT NOT NULL,
  `libelle` VARCHAR(32) NOT NULL,
  `taux` DECIMAL(4,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `code_tva_UNIQUE` (`code_tva` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`famille_article`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`famille_article` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`famille_article` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `libelle` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`article`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`article` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`article` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entreprise_id` INT UNSIGNED NOT NULL,
  `code` VARCHAR(64) NOT NULL,
  `famille_article_id` INT UNSIGNED NULL,
  `libelle` TEXT NULL,
  `unite` VARCHAR(45) NULL,
  `ht` DECIMAL(12,4) NULL DEFAULT 0.0000,
  `prix_achat` DECIMAL(12,4) NULL DEFAULT 0.0000,
  `coef` DECIMAL(5,2) NULL DEFAULT 0.00,
  `ttc` DECIMAL(12,4) NULL DEFAULT 0.0000,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL,
  `tva_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`, `entreprise_id`),
  INDEX `fk_article_tva_idx` (`tva_id` ASC),
  INDEX `fk_article_famille_article_idx` (`famille_article_id` ASC),
  UNIQUE INDEX `code_UNIQUE` (`code` ASC),
  INDEX `fk_articles_entreprise_idx` (`entreprise_id` ASC),
  CONSTRAINT `fk_articles_tva`
    FOREIGN KEY (`tva_id`)
    REFERENCES `paysagest`.`tva` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_articles_entreprise`
    FOREIGN KEY (`entreprise_id`)
    REFERENCES `paysagest`.`entreprise` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_article_famille_article`
    FOREIGN KEY (`famille_article_id`)
    REFERENCES `paysagest`.`famille_article` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`adresse_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`adresse_type` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`adresse_type` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` INT UNSIGNED NOT NULL,
  `libelle` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `code_UNIQUE` (`code` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`adresse`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`adresse` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`adresse` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` INT UNSIGNED NOT NULL,
  `adresse_1` VARCHAR(150) NULL,
  `adresse_2` VARCHAR(150) NULL,
  `adresse_3` VARCHAR(150) NULL,
  `cp` VARCHAR(10) NULL,
  `ville` VARCHAR(100) NULL,
  `pays` VARCHAR(64) NULL,
  `adresse_type_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`, `client_id`),
  INDEX `fk_adresse_adresse_type_idx` (`adresse_type_id` ASC),
  INDEX `fk_adresse_client_idx` (`client_id` ASC),
  CONSTRAINT `fk_adresse_adresse_type`
    FOREIGN KEY (`adresse_type_id`)
    REFERENCES `paysagest`.`adresse_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_adresse_client`
    FOREIGN KEY (`client_id`)
    REFERENCES `paysagest`.`client` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`echeance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`echeance` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`echeance` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `libelle` VARCHAR(32) NOT NULL,
  `interval` VARCHAR(32) NOT NULL,
  `unite` INT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`facture`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`facture` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`facture` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` INT UNSIGNED NOT NULL,
  `entreprise_id` INT UNSIGNED NOT NULL,
  `code_facture` VARCHAR(32) NOT NULL,
  `adresse_id` INT UNSIGNED NOT NULL,
  `echeance_id` INT UNSIGNED NOT NULL,
  `date_edition` DATETIME NULL,
  `total_ht` DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `total_tva` DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `total_ttc` DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`, `client_id`, `entreprise_id`),
  UNIQUE INDEX `code_facture_UNIQUE` (`code_facture` ASC),
  INDEX `fk_facture_adresse_idx` (`adresse_id` ASC),
  INDEX `fk_facture_echeance_idx` (`echeance_id` ASC),
  INDEX `fk_facture_client_idx` (`client_id` ASC, `entreprise_id` ASC),
  CONSTRAINT `fk_facture_adresse`
    FOREIGN KEY (`adresse_id`)
    REFERENCES `paysagest`.`adresse` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_facture_echeance`
    FOREIGN KEY (`echeance_id`)
    REFERENCES `paysagest`.`echeance` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_facture_client`
    FOREIGN KEY (`client_id` , `entreprise_id`)
    REFERENCES `paysagest`.`client` (`id` , `entreprise_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`statut_devis`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`statut_devis` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`statut_devis` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `libelle` VARCHAR(32) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`devis`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`devis` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`devis` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` INT UNSIGNED NOT NULL,
  `entreprise_id` INT UNSIGNED NOT NULL,
  `code_devis` VARCHAR(32) NOT NULL,
  `statut_devis_id` INT UNSIGNED NOT NULL,
  `adresse_id` INT UNSIGNED NOT NULL,
  `validite` DATETIME NULL,
  `total_ht` DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `total_tva` DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `total_ttc` DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`, `client_id`, `entreprise_id`),
  UNIQUE INDEX `code_devis_UNIQUE` (`code_devis` ASC),
  INDEX `fk_devis_adresse_idx` (`adresse_id` ASC),
  INDEX `fk_devis_client_idx` (`client_id` ASC, `entreprise_id` ASC),
  INDEX `fk_devis_statut_devis_idx` (`statut_devis_id` ASC),
  CONSTRAINT `fk_devis_adresse`
    FOREIGN KEY (`adresse_id`)
    REFERENCES `paysagest`.`adresse` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_devis_client`
    FOREIGN KEY (`client_id` , `entreprise_id`)
    REFERENCES `paysagest`.`client` (`id` , `entreprise_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_devis_statut_devis`
    FOREIGN KEY (`statut_devis_id`)
    REFERENCES `paysagest`.`statut_devis` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`tranche_devis`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`tranche_devis` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`tranche_devis` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `devis_id` INT UNSIGNED NOT NULL,
  `num_ligne` INT UNSIGNED NOT NULL,
  `libelle` TEXT NULL,
  `sous_total_ht` DECIMAL(12,4) NULL,
  `sous_total_tva` DECIMAL(12,4) NULL,
  `sous_total_ttc` DECIMAL(12,4) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_tranche_devis_devis_idx` (`devis_id` ASC),
  CONSTRAINT `fk_tranche_devis_devis`
    FOREIGN KEY (`devis_id`)
    REFERENCES `paysagest`.`devis` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`ligne_devis`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`ligne_devis` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`ligne_devis` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `devis_id` INT UNSIGNED NOT NULL,
  `num_ligne` INT UNSIGNED NOT NULL,
  `libelle` TEXT NULL,
  `quantite` DECIMAL(6,2) NULL,
  `ht` DECIMAL(12,4) NULL,
  `tva` DECIMAL(12,4) NULL,
  `ttc` DECIMAL(12,4) NULL,
  `article_id` INT UNSIGNED NULL,
  `tranche_devis_id` INT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_ligne_devis_article_idx` (`article_id` ASC),
  INDEX `fk_ligne_devis_devis_idx` (`devis_id` ASC),
  INDEX `fk_ligne_devis_tranche_devis_idx` (`tranche_devis_id` ASC),
  CONSTRAINT `fk_ligne_devis_article`
    FOREIGN KEY (`article_id`)
    REFERENCES `paysagest`.`article` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ligne_devis_devis`
    FOREIGN KEY (`devis_id`)
    REFERENCES `paysagest`.`devis` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ligne_devis_tranche_devis`
    FOREIGN KEY (`tranche_devis_id`)
    REFERENCES `paysagest`.`tranche_devis` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`tranche_facture`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`tranche_facture` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`tranche_facture` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `facture_id` INT UNSIGNED NOT NULL,
  `num_ligne` INT UNSIGNED NOT NULL,
  `libelle` TEXT NULL,
  `sous_total_ht` DECIMAL(12,4) NULL,
  `sous_total_tva` DECIMAL(12,4) NULL,
  `sous_total_ttc` DECIMAL(12,4) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_tranche_facture_facture_idx` (`facture_id` ASC),
  CONSTRAINT `fk_tranche_facture_facture`
    FOREIGN KEY (`facture_id`)
    REFERENCES `paysagest`.`facture` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`ligne_facture`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`ligne_facture` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`ligne_facture` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `num_ligne` INT UNSIGNED NOT NULL,
  `libelle` TEXT NULL,
  `quantite` DECIMAL(6,2) NULL,
  `ht` DECIMAL(12,4) NULL,
  `tva` DECIMAL(12,4) NULL,
  `ttc` DECIMAL(12,4) NULL,
  `article_id` INT UNSIGNED NULL,
  `facture_id` INT UNSIGNED NOT NULL,
  `tranche_facture_id` INT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_ligne_facture_article_idx` (`article_id` ASC),
  INDEX `fk_ligne_facture_facture_idx` (`facture_id` ASC),
  INDEX `fk_ligne_facture_tranche_facture_idx` (`tranche_facture_id` ASC),
  CONSTRAINT `fk_ligne_facture_article`
    FOREIGN KEY (`article_id`)
    REFERENCES `paysagest`.`article` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ligne_facture_facture`
    FOREIGN KEY (`facture_id`)
    REFERENCES `paysagest`.`facture` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ligne_facture_tranche_facture`
    FOREIGN KEY (`tranche_facture_id`)
    REFERENCES `paysagest`.`tranche_facture` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`reglement`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`reglement` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`reglement` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `facture_id` INT UNSIGNED NOT NULL,
  `client_id` INT UNSIGNED NOT NULL,
  `entreprise_id` INT UNSIGNED NOT NULL,
  `reglement` DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `reste` DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `total_ttc` DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  PRIMARY KEY (`id`, `facture_id`, `client_id`, `entreprise_id`),
  INDEX `fk_reglement_facture_idx` (`facture_id` ASC, `client_id` ASC, `entreprise_id` ASC),
  CONSTRAINT `fk_reglement_facture`
    FOREIGN KEY (`facture_id` , `client_id` , `entreprise_id`)
    REFERENCES `paysagest`.`facture` (`id` , `client_id` , `entreprise_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`acompte`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`acompte` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`acompte` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `devis_id` INT UNSIGNED NOT NULL,
  `montant` DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  PRIMARY KEY (`id`),
  INDEX `fk_acompte_devis_idx` (`devis_id` ASC),
  CONSTRAINT `fk_acompte_devis`
    FOREIGN KEY (`devis_id`)
    REFERENCES `paysagest`.`devis` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`periodicite`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`periodicite` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`periodicite` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `libelle` VARCHAR(32) NOT NULL,
  `interval` VARCHAR(32) NOT NULL,
  `unite` INT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`contrat`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`contrat` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`contrat` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` INT UNSIGNED NOT NULL,
  `entreprise_id` INT UNSIGNED NOT NULL,
  `code_contrat` VARCHAR(32) NOT NULL,
  `total_ht` DECIMAL(12,4) NULL DEFAULT 0.0000,
  `tva_id` INT UNSIGNED NOT NULL,
  `total_ttc` DECIMAL(12,4) NULL DEFAULT 0.0000,
  `total_ht_period` DECIMAL(12,4) NULL DEFAULT 0.0000,
  `total_ttc_period` DECIMAL(12,4) NULL DEFAULT 0.0000,
  `periodicite_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`, `client_id`, `entreprise_id`),
  UNIQUE INDEX `num_contrat_UNIQUE` (`code_contrat` ASC),
  INDEX `fk_contrat_tva_idx` (`tva_id` ASC),
  INDEX `fk_contrat_periodicite_idx` (`periodicite_id` ASC),
  INDEX `fk_contrat_client_idx` (`client_id` ASC, `entreprise_id` ASC),
  CONSTRAINT `fk_contrat_tva`
    FOREIGN KEY (`tva_id`)
    REFERENCES `paysagest`.`tva` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_contrat_periodicite`
    FOREIGN KEY (`periodicite_id`)
    REFERENCES `paysagest`.`periodicite` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_contrat_client`
    FOREIGN KEY (`client_id` , `entreprise_id`)
    REFERENCES `paysagest`.`client` (`id` , `entreprise_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`dernier_code`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`dernier_code` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`dernier_code` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `table_nom` VARCHAR(64) NOT NULL,
  `colonne` VARCHAR(64) NOT NULL,
  `code_table` VARCHAR(32) NOT NULL,
  `prochain_code` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`facture_periodique`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`facture_periodique` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`facture_periodique` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` INT UNSIGNED NOT NULL,
  `entreprise_id` INT UNSIGNED NOT NULL,
  `contrat_id` INT UNSIGNED NOT NULL,
  `prochaine_facture` DATE NOT NULL,
  `date_debut` DATE NOT NULL,
  INDEX `fk_facture_periodique_contrat_idx` (`contrat_id` ASC),
  PRIMARY KEY (`id`, `client_id`, `entreprise_id`),
  INDEX `fk_facture_periodique_client_idx` (`client_id` ASC, `entreprise_id` ASC),
  CONSTRAINT `fk_facture_periodique_contrat`
    FOREIGN KEY (`contrat_id`)
    REFERENCES `paysagest`.`contrat` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_facture_periodique_client`
    FOREIGN KEY (`client_id` , `entreprise_id`)
    REFERENCES `paysagest`.`client` (`id` , `entreprise_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`tranche_facture_periodique`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`tranche_facture_periodique` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`tranche_facture_periodique` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `facture_periodique_id` INT UNSIGNED NOT NULL,
  `num_ligne` INT UNSIGNED NOT NULL,
  `libelle` TEXT NULL,
  `sous_total_ht` DECIMAL(12,4) NULL,
  `sous_total_tva` DECIMAL(12,4) NULL,
  `sous_total_ttc` DECIMAL(12,4) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_tranche_facture_periodique_facture_periodique_idx` (`facture_periodique_id` ASC),
  CONSTRAINT `fk_tranche_facture_periodique_facture_periodique`
    FOREIGN KEY (`facture_periodique_id`)
    REFERENCES `paysagest`.`facture_periodique` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`ligne_facture_periodique`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`ligne_facture_periodique` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`ligne_facture_periodique` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `facture_periodique_id` INT UNSIGNED NOT NULL,
  `article_id` INT UNSIGNED NULL,
  `num_ligne` INT UNSIGNED NULL,
  `libelle` TEXT NULL,
  `quantite` DECIMAL(6,2) NULL,
  `ht` DECIMAL(12,4) NULL,
  `tva` DECIMAL(12,4) NULL,
  `ttc` DECIMAL(12,4) NULL,
  `tranche_facture_periodique_id` INT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_ligne_facture_periodique_article_idx` (`article_id` ASC),
  INDEX `fk_ligne_facture_periodique_facture_periodique_idx` (`facture_periodique_id` ASC),
  INDEX `fk_ligne_facture_periodique_tranche_facture_periodique_idx` (`tranche_facture_periodique_id` ASC),
  CONSTRAINT `fk_ligne_facture_periodique_article`
    FOREIGN KEY (`article_id`)
    REFERENCES `paysagest`.`article` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ligne_facture_periodique_facture_periodique`
    FOREIGN KEY (`facture_periodique_id`)
    REFERENCES `paysagest`.`facture_periodique` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ligne_facture_periodique_tranche_facture_periodique`
    FOREIGN KEY (`tranche_facture_periodique_id`)
    REFERENCES `paysagest`.`tranche_facture_periodique` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `paysagest`.`prefix`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `paysagest`.`prefix` ;

CREATE TABLE IF NOT EXISTS `paysagest`.`prefix` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `table_nom` VARCHAR(64) NOT NULL,
  `libelle` VARCHAR(16) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

USE `paysagest`;

DELIMITER $$

USE `paysagest`$$
DROP TRIGGER IF EXISTS `paysagest`.`client_AFTER_INSERT` $$
USE `paysagest`$$
CREATE TRIGGER `paysagest`.`client_AFTER_INSERT` 
AFTER INSERT ON `client` FOR EACH ROW
BEGIN
DECLARE len INTEGER;
DECLARE new_code VARCHAR(32);
DECLARE num_code INTEGER UNSIGNED;
DECLARE pref VARCHAR(16);

    IF (NEW.code_client IS NOT NULL) THEN
        SET pref = (SELECT COALESCE(libelle, 'CL') from prefix 
                    WHERE table_nom = 'client');
        SET len = char_length(pref);
        SET new_code = substring(NEW.code_client, len + 1);
        SET num_code = CAST(new_code AS UNSIGNED);
        SET new_code = CONCAT(pref, num_code +1);
        
        INSERT INTO dernier_code (table_nom, colonne, code_table, prochain_code)
        VALUES ('client', 'code_client', NEW.code_client, new_code);
    END IF;
END$$


USE `paysagest`$$
DROP TRIGGER IF EXISTS `paysagest`.`facture_AFTER_INSERT` $$
USE `paysagest`$$
CREATE TRIGGER `paysagest`.`facture_AFTER_INSERT` 
AFTER INSERT ON `facture` FOR EACH ROW
BEGIN
DECLARE len INTEGER;
DECLARE new_code VARCHAR(32);
DECLARE num_code INTEGER UNSIGNED;
DECLARE pref VARCHAR(16);

    IF (NEW.code_facture IS NOT NULL) THEN
        SET pref = (SELECT COALESCE(libelle, 'FC') from prefix 
                    WHERE table_nom = 'facture');
        SET len = char_length(pref);
        SET new_code = substring(NEW.code_facture, len + 1);
        SET num_code = CAST(new_code AS UNSIGNED);
        SET new_code = CONCAT(pref, num_code +1);
        
        INSERT INTO dernier_code (table_nom, colonne, code_table, prochain_code)
        VALUES ('facture', 'code_facture', NEW.code_facture, new_code);
    END IF;
END$$


USE `paysagest`$$
DROP TRIGGER IF EXISTS `paysagest`.`devis_AFTER_INSERT` $$
USE `paysagest`$$
CREATE TRIGGER `paysagest`.`devis_AFTER_INSERT` 
AFTER INSERT ON `devis` FOR EACH ROW
BEGIN
DECLARE len INTEGER;
DECLARE new_code VARCHAR(32);
DECLARE num_code INTEGER UNSIGNED;
DECLARE pref VARCHAR(16);
    IF (NEW.code_devis IS NOT NULL) THEN
        SET pref = (SELECT COALESCE(libelle, 'DC') from prefix 
                    WHERE table_nom = 'devis');
        SET len = char_length(pref);
        SET new_code = substring(NEW.code_devis, len + 1);
        SET num_code = CAST(new_code AS UNSIGNED);
        SET new_code = CONCAT(pref, num_code +1);
        
        INSERT INTO dernier_code (table_nom, colonne, code_table, prochain_code)
        VALUES ('devis', 'code_devis', NEW.code_devis, new_code);
    END IF;
END$$


USE `paysagest`$$
DROP TRIGGER IF EXISTS `paysagest`.`contrat_AFTER_INSERT` $$
USE `paysagest`$$
CREATE TRIGGER `paysagest`.`contrat_AFTER_INSERT` 
AFTER INSERT ON `contrat` FOR EACH ROW
BEGIN
DECLARE len INTEGER;
DECLARE new_code VARCHAR(32);
DECLARE num_code INTEGER UNSIGNED;
DECLARE pref VARCHAR(16);

    IF (NEW.code_contrat IS NOT NULL) THEN
        SET pref = (SELECT COALESCE(libelle, 'C') from prefix 
                    WHERE table_nom = 'contrat');
        SET len = char_length(pref);
        SET new_code = substring(NEW.code_contrat, len + 1);
        SET num_code = CAST(new_code AS UNSIGNED);
        SET new_code = CONCAT(pref, num_code +1);
        
        INSERT INTO dernier_code (table_nom, colonne, code_table, prochain_code)
        VALUES ('contrat', 'code_contrat', NEW.code_contrat, new_code);
    END IF;
END$$


DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `paysagest`.`role`
-- -----------------------------------------------------
START TRANSACTION;
USE `paysagest`;
INSERT INTO `paysagest`.`role` (`id`, `role`) VALUES (1, 'admin');
INSERT INTO `paysagest`.`role` (`id`, `role`) VALUES (2, 'user');

COMMIT;


-- -----------------------------------------------------
-- Data for table `paysagest`.`civilite`
-- -----------------------------------------------------
START TRANSACTION;
USE `paysagest`;
INSERT INTO `paysagest`.`civilite` (`id`, `libelle`) VALUES (1, 'M. et MM.');
INSERT INTO `paysagest`.`civilite` (`id`, `libelle`) VALUES (2, 'Madame');
INSERT INTO `paysagest`.`civilite` (`id`, `libelle`) VALUES (3, 'Monsieur');
INSERT INTO `paysagest`.`civilite` (`id`, `libelle`) VALUES (4, 'Madame et Monsieur');

COMMIT;


-- -----------------------------------------------------
-- Data for table `paysagest`.`tva`
-- -----------------------------------------------------
START TRANSACTION;
USE `paysagest`;
INSERT INTO `paysagest`.`tva` (`id`, `code_tva`, `libelle`, `taux`) VALUES (1, 1, 'tva_20', 20.00);
INSERT INTO `paysagest`.`tva` (`id`, `code_tva`, `libelle`, `taux`) VALUES (2, 2, 'tva_5.5', 5.50);

COMMIT;


-- -----------------------------------------------------
-- Data for table `paysagest`.`adresse_type`
-- -----------------------------------------------------
START TRANSACTION;
USE `paysagest`;
INSERT INTO `paysagest`.`adresse_type` (`id`, `code`, `libelle`) VALUES (1, 1, 'standard');
INSERT INTO `paysagest`.`adresse_type` (`id`, `code`, `libelle`) VALUES (2, 2, 'facturation');
INSERT INTO `paysagest`.`adresse_type` (`id`, `code`, `libelle`) VALUES (3, 3, 'travaux');

COMMIT;


-- -----------------------------------------------------
-- Data for table `paysagest`.`statut_devis`
-- -----------------------------------------------------
START TRANSACTION;
USE `paysagest`;
INSERT INTO `paysagest`.`statut_devis` (`id`, `libelle`) VALUES (1, 'en cours');
INSERT INTO `paysagest`.`statut_devis` (`id`, `libelle`) VALUES (2, 'accepté');
INSERT INTO `paysagest`.`statut_devis` (`id`, `libelle`) VALUES (3, 'terminé');
INSERT INTO `paysagest`.`statut_devis` (`id`, `libelle`) VALUES (4, 'rejeté');

COMMIT;


-- -----------------------------------------------------
-- Data for table `paysagest`.`periodicite`
-- -----------------------------------------------------
START TRANSACTION;
USE `paysagest`;
INSERT INTO `paysagest`.`periodicite` (`id`, `libelle`, `interval`, `unite`) VALUES (1, 'mensuelle', 'MONTH', 1);
INSERT INTO `paysagest`.`periodicite` (`id`, `libelle`, `interval`, `unite`) VALUES (2, 'bimestrielle', 'MONTH', 2);
INSERT INTO `paysagest`.`periodicite` (`id`, `libelle`, `interval`, `unite`) VALUES (3, 'trimestrielle', 'MONTH', 3);

COMMIT;


-- -----------------------------------------------------
-- Data for table `paysagest`.`prefix`
-- -----------------------------------------------------
START TRANSACTION;
USE `paysagest`;
INSERT INTO `paysagest`.`prefix` (`id`, `table_nom`, `libelle`) VALUES (1, 'client', 'CL');
INSERT INTO `paysagest`.`prefix` (`id`, `table_nom`, `libelle`) VALUES (2, 'contrat', 'C');
INSERT INTO `paysagest`.`prefix` (`id`, `table_nom`, `libelle`) VALUES (3, 'devis', 'DC');
INSERT INTO `paysagest`.`prefix` (`id`, `table_nom`, `libelle`) VALUES (4, 'facture', 'FC');

COMMIT;