-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema bpmsdb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema bpmsdb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `bpmsdb` DEFAULT CHARACTER SET utf8mb4 COLLATE
utf8mb4_0900_ai_ci ;
USE `bpmsdb` ;

-- -----------------------------------------------------
-- Table `bpmsdb`.`tbladmin`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bpmsdb`.`tbladmin` (
`ID` INT NOT NULL AUTO_INCREMENT,
`AdminName` CHAR(50) NULL DEFAULT NULL,
`UserName` CHAR(50) NULL DEFAULT NULL,
`MobileNumber` BIGINT NULL DEFAULT NULL,
`Email` VARCHAR(200) NULL DEFAULT NULL,
`Password` VARCHAR(200) NULL DEFAULT NULL,
`AdminRegdate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;

-- -----------------------------------------------------
-- Table `bpmsdb`.`tblappointment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bpmsdb`.`tblappointment` (
`ID` INT NOT NULL AUTO_INCREMENT,
`AptNumber` VARCHAR(80) NULL DEFAULT NULL,
`Name` VARCHAR(120) NULL DEFAULT NULL,
`Email` VARCHAR(120) NULL DEFAULT NULL,
`PhoneNumber` BIGINT NULL DEFAULT NULL,
`AptDate` VARCHAR(120) NULL DEFAULT NULL,
`AptTime` VARCHAR(120) NULL DEFAULT NULL,
`Services` VARCHAR(120) NULL DEFAULT NULL,
`ApplyDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`Remark` VARCHAR(250) NOT NULL,
`Status` VARCHAR(50) NOT NULL,
`RemarkDate` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`))
ENGINE = InnoDB
AUTO_INCREMENT = 14
DEFAULT CHARACTER SET = latin1;
-- -----------------------------------------------------
-- Table `bpmsdb`.`tblcustomers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bpmsdb`.`tblcustomers` (
`ID` INT NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(120) NULL DEFAULT NULL,
`Email` VARCHAR(200) NULL DEFAULT NULL,
`MobileNumber` BIGINT NULL DEFAULT NULL,
`Gender` ENUM('Mujer', 'Hombre', 'No definido') NULL DEFAULT NULL,
`Details` MEDIUMTEXT NULL DEFAULT NULL,
`CreationDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`UpdationDate` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB
AUTO_INCREMENT = 8
DEFAULT CHARACTER SET = latin1;

-- -----------------------------------------------------
-- Table `bpmsdb`.`tblservices`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bpmsdb`.`tblservices` (
`ID` INT NOT NULL AUTO_INCREMENT,
`ServiceName` VARCHAR(200) NULL DEFAULT NULL,
`Cost` INT NULL DEFAULT NULL,
`CreationDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`))
ENGINE = InnoDB
AUTO_INCREMENT = 19
DEFAULT CHARACTER SET = latin1;

-- -----------------------------------------------------
-- Table `bpmsdb`.`tblinvoice`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bpmsdb`.`tblinvoice` (
`id` INT NOT NULL AUTO_INCREMENT,
`Userid` INT NULL DEFAULT NULL,
`ServiceId` INT NULL DEFAULT NULL,
`costumerId` INT NULL,
`BillingId` INT NULL DEFAULT NULL,
`PostingDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `id` (`id` ASC),
INDEX `Service1_idx` (`ServiceId` ASC),
INDEX `admin1_idx` (`Userid` ASC),
INDEX `costumer1_idx` (`costumerId` ASC),
CONSTRAINT `Service1`
FOREIGN KEY (`ServiceId`)
REFERENCES `bpmsdb`.`tblservices` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `admin1`
FOREIGN KEY (`Userid`)
REFERENCES `bpmsdb`.`tbladmin` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `costumer1`
FOREIGN KEY (`costumerId`)
REFERENCES `bpmsdb`.`tblcustomers` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 31
DEFAULT CHARACTER SET = latin1;

-- -----------------------------------------------------
-- Table `bpmsdb`.`tblpage`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `bpmsdb`.`tblpage` (
`ID` INT NOT NULL AUTO_INCREMENT,
`user_id_admin` INT NULL,
`PageType` VARCHAR(200) NULL DEFAULT NULL,
`PageTitle` MEDIUMTEXT NULL DEFAULT NULL,
`PageDescription` MEDIUMTEXT NULL DEFAULT NULL,
`Email` VARCHAR(200) NULL DEFAULT NULL,
`MobileNumber` BIGINT NULL DEFAULT NULL,
`UpdationDate` DATE NULL DEFAULT NULL,
`Timing` VARCHAR(200) NOT NULL,
PRIMARY KEY (`ID`),
INDEX `admin2_idx` (`user_id_admin` ASC),
CONSTRAINT `admin2`
FOREIGN KEY (`user_id_admin`)
REFERENCES `bpmsdb`.`tbladmin` (`ID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;

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