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

-- -----------------------------------------------------
-- Table `role`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `role` (
  `id_role` INT NOT NULL AUTO_INCREMENT,
  `name_role` VARCHAR(30) NOT NULL,
  `description` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id_role`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `account`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `account` (
  `id_account` INT NOT NULL AUTO_INCREMENT,
  `user` VARCHAR(70) NOT NULL,
  `password` VARCHAR(170) NOT NULL,
  `state` TINYINT NOT NULL,
  `role_id_role` INT NOT NULL,
  PRIMARY KEY (`id_account`),
  UNIQUE INDEX `id_account_UNIQUE` (`id_account`),
  INDEX `fk_account_role1_idx` (`role_id_role`),
  CONSTRAINT `fk_account_role1`
    FOREIGN KEY (`role_id_role`)
    REFERENCES `role` (`id_role`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `business`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `business` (
  `id_business` INT NOT NULL AUTO_INCREMENT,
  `company_name` VARCHAR(100) NOT NULL,
  `photo` VARCHAR(100) NOT NULL,
  `nit` VARCHAR(30) NOT NULL,
  `location` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(13) NOT NULL,
  `cellular` VARCHAR(13) NOT NULL,
  `category` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id_business`),
  UNIQUE INDEX `id_business_UNIQUE` (`id_business`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `person`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `person` (
  `id_person` INT NOT NULL AUTO_INCREMENT,
  `names` VARCHAR(30) NOT NULL,
  `surnames` VARCHAR(30) NOT NULL,
  `photo` VARCHAR(100) NOT NULL,
  `di` VARCHAR(17) NOT NULL,
  `cellular` VARCHAR(17) NOT NULL,
  `e_mail` VARCHAR(70) NOT NULL,
  `account_id_account` INT NOT NULL,
  `business_id_business` INT NOT NULL,
  PRIMARY KEY (`id_person`),
  UNIQUE INDEX `id_person_UNIQUE` (`id_person`),
  INDEX `fk_person_account1_idx` (`account_id_account`),
  INDEX `fk_person_business1_idx` (`business_id_business`),
  CONSTRAINT `fk_person_account1`
    FOREIGN KEY (`account_id_account`)
    REFERENCES `account` (`id_account`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_person_business1`
    FOREIGN KEY (`business_id_business`)
    REFERENCES `business` (`id_business`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `category` (
  `id_category` INT NOT NULL AUTO_INCREMENT,
  `name_category` VARCHAR(100) NOT NULL,
  `photo` VARCHAR(100) NOT NULL,
  `state` TINYINT NOT NULL,
  `business_id_business` INT NOT NULL,
  PRIMARY KEY (`id_category`),
  UNIQUE INDEX `id_category_UNIQUE` (`id_category`),
  INDEX `fk_category_business1_idx` (`business_id_business`),
  CONSTRAINT `fk_category_business1`
    FOREIGN KEY (`business_id_business`)
    REFERENCES `business` (`id_business`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `product` (
  `id_product` INT NOT NULL AUTO_INCREMENT,
  `code_product` VARCHAR(100) NOT NULL,
  `name_product` VARCHAR(100) NOT NULL,
  `photo` VARCHAR(100) NOT NULL,
  `description_product` TEXT NOT NULL,
  `price_unit` DECIMAL(10,2) NOT NULL,
  `price_wholesale` DECIMAL(10,2) NOT NULL,
  `stock` INT NOT NULL,
  `status` VARCHAR(30) NOT NULL,
  `state` TINYINT NOT NULL,
  `category_id_category` INT NOT NULL,
  `business_id_business` INT NOT NULL,
  PRIMARY KEY (`id_product`),
  UNIQUE INDEX `id_product_UNIQUE` (`id_product`),
  INDEX `fk_product_business1_idx` (`business_id_business`),
  CONSTRAINT `fk_product_business1`
    FOREIGN KEY (`business_id_business`)
    REFERENCES `business` (`id_business`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  INDEX `fk_product_categpry1_idx` (`category_id_category`),
  CONSTRAINT `fk_product_category1`
    FOREIGN KEY (`category_id_category`)
    REFERENCES `category` (`id_category`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `client`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `client` (
  `id_client` INT NOT NULL AUTO_INCREMENT,
  `full_name` VARCHAR(100) NOT NULL,
  `di_nit` VARCHAR(30) NOT NULL,
  `cellular` VARCHAR(13) NOT NULL,
  `e_mail` VARCHAR(37) NOT NULL,
  `account_id_account` INT NOT NULL,
  PRIMARY KEY (`id_client`),
  UNIQUE INDEX `id_client_UNIQUE` (`id_client`),
  INDEX `fk_client_account1_idx` (`account_id_account`),
  CONSTRAINT `fk_client_account1`
    FOREIGN KEY (`account_id_account`)
    REFERENCES `account` (`id_account`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pay_method`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pay_method` (
  `id_pay_method` INT NOT NULL AUTO_INCREMENT,
  `method_name` VARCHAR(70) NOT NULL,
  `description` TEXT NOT NULL,
  PRIMARY KEY (`id_pay_method`),
  UNIQUE INDEX `id_pay_method_UNIQUE` (`id_pay_method`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `liquor`.`sale`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sale` (
  `id_sale` INT NOT NULL AUTO_INCREMENT,
  `date_time_sale` DATETIME NOT NULL,
  `total_sale` DECIMAL(10,2) NOT NULL,
  `sale_promotion` TINYINT NOT NULL,
  `homeDelivery` TINYINT NOT NULL,
  `status` VARCHAR(30) NOT NULL,
  `state` TINYINT NOT NULL,
  `person_id_person` INT NOT NULL,
  `client_id_client` INT NOT NULL,
  `pay_method_id_pay_method` INT NOT NULL,
  PRIMARY KEY (`id_sale`),
  UNIQUE INDEX `id_sale_UNIQUE` (`id_sale`),
  INDEX `fk_sale_person1_idx` (`person_id_person`),
  INDEX `fk_sale_client1_idx` (`client_id_client`),
  INDEX `fk_sale_pay_method1_idx` (`pay_method_id_pay_method`),
  CONSTRAINT `fk_sale_person1`
    FOREIGN KEY (`person_id_person`)
    REFERENCES `person` (`id_person`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sale_client1`
    FOREIGN KEY (`client_id_client`)
    REFERENCES `client` (`id_client`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sale_pay_method1`
    FOREIGN KEY (`pay_method_id_pay_method`)
    REFERENCES `pay_method` (`id_pay_method`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `detail_sale`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `detail_sale` (
  `id_detail_sale` INT NOT NULL AUTO_INCREMENT,
  `amount_sale` INT NOT NULL,
  `price_sale` DECIMAL(10,2) NOT NULL,
  `product_id_product` INT NOT NULL,
  `sale_id_sale` INT NOT NULL,
  PRIMARY KEY (`id_detail_sale`),
  UNIQUE INDEX `id_detail_sale_UNIQUE` (`id_detail_sale`),
  INDEX `fk_detail_sale_product1_idx` (`product_id_product`),
  INDEX `fk_detail_sale_sale1_idx` (`sale_id_sale`),
  CONSTRAINT `fk_detail_sale_product1`
    FOREIGN KEY (`product_id_product`)
    REFERENCES `product` (`id_product`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_detail_sale_sale1`
    FOREIGN KEY (`sale_id_sale`)
    REFERENCES `sale` (`id_sale`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `delivery`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `delivery` (
  `id_delivery` INT NOT NULL AUTO_INCREMENT,
  `originLatitude` VARCHAR(170) NOT NULL,
  `originLength` VARCHAR(170) NOT NULL,
  `destinationLatitude` VARCHAR(170) NOT NULL,
  `destinationLength` VARCHAR(170) not null,
  `price` DECIMAL(10,2) NOT NULL,
  `sale_id_sale` INT NOT NULL,
  PRIMARY KEY (`id_delivery`),
  UNIQUE INDEX `id_delivery_UNIQUE` (`id_delivery`),
  INDEX `fk_delivery_id_sale1_idx` (`sale_id_sale`),
  CONSTRAINT `fk_delivery_sale_sale1`
    FOREIGN KEY (`sale_id_sale`)
    REFERENCES `sale` (`id_sale`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `provider`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `provider` (
  `id_provider` INT NOT NULL AUTO_INCREMENT,
  `company_name` VARCHAR(37) NOT NULL,
  `representative` VARCHAR(70) NOT NULL,
  `nit` VARCHAR(30) NULL,
  `phone` VARCHAR(13) NULL,
  `cellular` VARCHAR(13) NULL,
  `address` VARCHAR(170) NULL,
  `business_id_business` INT NOT NULL,
  PRIMARY KEY (`id_provider`),
  UNIQUE INDEX `id_provider_UNIQUE` (`id_provider`),
  INDEX `fk_provider_business1_idx` (`business_id_business`),
  CONSTRAINT `fk_provider_business1`
    FOREIGN KEY (`business_id_business`)
    REFERENCES `business` (`id_business`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `shopping`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `shopping` (
  `id_shopping` INT NOT NULL AUTO_INCREMENT,
  `date_time_shopping` DATETIME NOT NULL,
  `total_pay` DECIMAL(10,2) NOT NULL,
  `status` VARCHAR(30) NOT NULL,
  `state` TINYINT NOT NULL,
  `provider_id_provider` INT NOT NULL,
  `person_id_person` INT NOT NULL,
  PRIMARY KEY (`id_shopping`),
  UNIQUE INDEX `id_shopping_UNIQUE` (`id_shopping`),
  INDEX `fk_shopping_provider1_idx` (`provider_id_provider`),
  INDEX `fk_shopping_person1_idx` (`person_id_person`),
  CONSTRAINT `fk_shopping_provider1`
    FOREIGN KEY (`provider_id_provider`)
    REFERENCES `provider` (`id_provider`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_shopping_person1`
    FOREIGN KEY (`person_id_person`)
    REFERENCES `person` (`id_person`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `detail_shopping`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `detail_shopping` (
  `id_detail_shopping` INT NOT NULL AUTO_INCREMENT,
  `amount_shopping` INT NOT NULL,
  `price_shopping` DECIMAL(10,2) NOT NULL,
  `product_id_product` INT NOT NULL,
  `shopping_id_shopping` INT NOT NULL,
  PRIMARY KEY (`id_detail_shopping`),
  UNIQUE INDEX `id_detail_shopping_UNIQUE` (`id_detail_shopping`),
  INDEX `fk_detail_shopping_product1_idx` (`product_id_product`),
  INDEX `fk_detail_shopping_shopping1_idx` (`shopping_id_shopping`),
  CONSTRAINT `fk_detail_shopping_product1`
    FOREIGN KEY (`product_id_product`)
    REFERENCES `product` (`id_product`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_detail_shopping_shopping1`
    FOREIGN KEY (`shopping_id_shopping`)
    REFERENCES `shopping` (`id_shopping`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `promotion`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `promotion` (
  `id_promotion` INT NOT NULL AUTO_INCREMENT,
  `date_time_start` DATETIME NOT NULL,
  `date_time_end` DATETIME NOT NULL,
  PRIMARY KEY (`id_promotion`),
  UNIQUE INDEX `id_promotion_UNIQUE` (`id_promotion`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `promotion_has_product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `promotion_has_product` (
  `promotion_id_promotion` INT NOT NULL,
  `product_id_product` INT NOT NULL,
  PRIMARY KEY (`promotion_id_promotion`, `product_id_product`),
  INDEX `fk_promotion_has_product_product1_idx` (`product_id_product`),
  INDEX `fk_promotion_has_product_promotion1_idx` (`promotion_id_promotion`),
  CONSTRAINT `fk_promotion_has_product_promotion1`
    FOREIGN KEY (`promotion_id_promotion`)
    REFERENCES `promotion` (`id_promotion`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_promotion_has_product_product1`
    FOREIGN KEY (`product_id_product`)
    REFERENCES `product` (`id_product`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sale_has_promotion`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sale_has_promotion` (
  `sale_id_sale` INT NOT NULL,
  `promotion_id_promotion` INT NOT NULL,
  PRIMARY KEY (`sale_id_sale`, `promotion_id_promotion`),
  INDEX `fk_sale_has_promotion_promotion1_idx` (`promotion_id_promotion`),
  INDEX `fk_sale_has_promotion_sale1_idx` (`sale_id_sale`),
  CONSTRAINT `fk_sale_has_promotion_sale1`
    FOREIGN KEY (`sale_id_sale`)
    REFERENCES `sale` (`id_sale`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sale_has_promotion_promotion1`
    FOREIGN KEY (`promotion_id_promotion`)
    REFERENCES `promotion` (`id_promotion`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `voucher`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `voucher` (
  `id_voucher` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(10) NOT NULL,
  `number` INT NULL,
  `sale_id_sale` INT NOT NULL,
  PRIMARY KEY (`id_voucher`),
  UNIQUE INDEX `id_voucher_UNIQUE` (`id_voucher`),
  INDEX `fk_voucher_sale1_idx` (`sale_id_sale`),
  CONSTRAINT `fk_voucher_sale1`
    FOREIGN KEY (`sale_id_sale`)
    REFERENCES `sale` (`id_sale`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sending`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sending` (
  `id_sending` INT NOT NULL AUTO_INCREMENT,
  `date_time_sending` DATETIME NOT NULL,
  `origin` TEXT NOT NULL,
  `destiny` TEXT NOT NULL,
  `state` VARCHAR(30) NOT NULL,
  `status` TINYINT NOT NULL,
  `observations` TEXT NOT NULL,
  `sale_id_sale` INT NOT NULL,
  PRIMARY KEY (`id_sending`),
  UNIQUE INDEX `id_sending_UNIQUE` (`id_sending`),
  INDEX `fk_sending_sale1_idx` (`sale_id_sale`),
  CONSTRAINT `fk_sending_sale1`
    FOREIGN KEY (`sale_id_sale`)
    REFERENCES `sale` (`id_sale`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO `role` (`name_role`, `description`) VALUES ('Administrador', 'Esta una cuenta para los administradores o encagados de cada negocio registrado.');
INSERT INTO `role` (`name_role`, `description`) VALUES ('Vendedor', 'Esta es una cuenta para cuentas de vendedores de cada negocio registrado.');
INSERT INTO `role` (`name_role`, `description`) VALUES ('Cliente', 'Esta es una cuenta para los clientes.');

INSERT INTO `business` (`company_name`, `photo`, `nit`, `location`, `phone`, `cellular`, `category`) VALUES ('Liquor', "default.jpg", '1234567', 'Av. Villazon', '4156511', '77458962', 'Casa Matriz');

INSERT INTO `account` (`user`, `password`, `state`, `role_id_role`) VALUES ('yelena', '$2y$10$Pc3DG9AZtWJf.mxgTFkUTuK7z8ZHW7XBXgiAWODYSNMcvZ6TtmZZ6', '1', '1');

INSERT INTO `person` (`names`, `surnames`, `photo`, `di`, `cellular`, `e_mail`, `account_id_account`, `business_id_business`) VALUES ('Yelena', 'Yelena', 'default.jpg', '4651654', '78459625', 'yelena@gmail.com', '1', '1');

INSERT INTO `pay_method` (`method_name`, `description`) VALUES ('Efectivo', 'Pago con dinero en efectivo.');
INSERT INTO `pay_method` (`method_name`, `description`) VALUES ('Código QR', 'Pago realizado mediante Código QR.');
INSERT INTO `pay_method` (`method_name`, `description`) VALUES ('Transferencia Bancaria', 'Pago realizado mediante transferencia bancaria.');