-- MySQL Script generated by MySQL Workbench
-- Fri Nov 14 19:11:04 2025
-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `mydb`.`table1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`master_locations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`master_locations` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`master_routes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`master_routes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_origin` INT NOT NULL,
  `id_destination` INT NOT NULL,
  `distance` DECIMAL(17,2) NOT NULL,
  `duration` INT NOT NULL,
  `toll` ENUM('non', 'full', 'half') NOT NULL,
  `pocket_money_1` DECIMAL(17,2) NOT NULL,
  `pocket_money_2` DECIMAL(17,2) NULL,
  `pocket_money_3` DECIMAL(17,2) NULL,
  `bonus` DECIMAL(17,2) NULL,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) GENERATED ALWAYS AS (),
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_master_route_master_location_idx` (`id_origin` ASC) VISIBLE,
  INDEX `fk_master_route_master_location1_idx` (`id_destination` ASC) VISIBLE,
  CONSTRAINT `fk_master_route_master_location`
    FOREIGN KEY (`id_origin`)
    REFERENCES `mydb`.`master_locations` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_master_route_master_location1`
    FOREIGN KEY (`id_destination`)
    REFERENCES `mydb`.`master_locations` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`master_drivers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`master_drivers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(16) NOT NULL,
  `full_name` VARCHAR(255) NOT NULL,
  `nick_name` VARCHAR(255) NULL,
  `phone` VARCHAR(16) NOT NULL,
  `address` VARCHAR(255) NOT NULL,
  `identity_number` VARCHAR(16) NOT NULL,
  `license_number` VARCHAR(16) NOT NULL,
  `kk_number` VARCHAR(16) NULL,
  `bpjs_number` VARCHAR(16) NULL,
  `identity_file` VARCHAR(45) NOT NULL,
  `license_file` VARCHAR(45) NOT NULL,
  `license_type` ENUM('B1', 'B1 UMUM', 'B2', 'B2 UMUM') NOT NULL,
  `license_date` DATE NOT NULL,
  `kk_file` VARCHAR(45) NULL,
  `photo_file` VARCHAR(45) NOT NULL,
  `skck_file` VARCHAR(45) NULL,
  `bank_name` VARCHAR(45) NULL,
  `bank_owner_name` VARCHAR(45) NULL,
  `bank_no` VARCHAR(45) NULL,
  `joined_date` TIMESTAMP NOT NULL,
  `status` ENUM('active', 'blacklist', 'resigned') NOT NULL DEFAULT 'active',
  `notes` VARCHAR(512) NULL,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`master_customers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`master_customers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `full_name` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(16) NOT NULL,
  `address` VARCHAR(255) NOT NULL,
  `email` VARCHAR(45) NULL,
  `pic_name` VARCHAR(255) NOT NULL,
  `pic_phone` VARCHAR(45) NOT NULL,
  `is_ppn` TINYINT(1) NOT NULL,
  `is_pph` TINYINT(1) NOT NULL,
  `npwp_no` VARCHAR(16) NULL,
  `term_of_payment` VARCHAR(45) NOT NULL,
  `credit_limit` DECIMAL(17,2) NULL,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`master_truck_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`master_truck_types` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`master_trucks`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`master_trucks` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_master_type` INT NOT NULL,
  `code` VARCHAR(16) NOT NULL,
  `brand` VARCHAR(64) NOT NULL,
  `body_no` VARCHAR(45) NOT NULL,
  `machine_no` VARCHAR(45) NOT NULL,
  `vehicle_year` VARCHAR(4) NOT NULL,
  `stnk_owner` VARCHAR(255) NOT NULL,
  `kir_name` VARCHAR(255) NULL,
  `license_plate` VARCHAR(16) NOT NULL,
  `tax_date` DATE NOT NULL,
  `kir_date` DATE NOT NULL,
  `stnk_date` DATE NOT NULL,
  `kir_file` VARCHAR(45) NOT NULL,
  `bpkb_file` VARCHAR(45) NOT NULL,
  `stnk_file` VARCHAR(45) NOT NULL,
  `credit_start_date` DATE NULL,
  `credit_end_date` DATE NULL,
  `cctv_gps_no` VARCHAR(32) NULL,
  `gps_contact` VARCHAR(16) NULL,
  `note` VARCHAR(255) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_master_trucks_master_truck_types1_idx` (`id_master_type` ASC) VISIBLE,
  CONSTRAINT `fk_master_trucks_master_truck_types1`
    FOREIGN KEY (`id_master_type`)
    REFERENCES `mydb`.`master_truck_types` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`master_truck_loadout`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`master_truck_loadout` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`master_prices`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`master_prices` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_master_route` INT NOT NULL,
  `id_master_loadout` INT NOT NULL,
  `id_master_customer` INT NULL,
  `price` DECIMAL(17,2) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`, `id_master_route`, `id_master_loadout`),
  INDEX `fk_master_routes_has_master_truck_loadout_master_truck_load_idx` (`id_master_loadout` ASC) VISIBLE,
  INDEX `fk_master_routes_has_master_truck_loadout_master_routes1_idx` (`id_master_route` ASC) VISIBLE,
  INDEX `fk_master_prices_master_customers1_idx` (`id_master_customer` ASC) VISIBLE,
  CONSTRAINT `fk_master_routes_has_master_truck_loadout_master_routes1`
    FOREIGN KEY (`id_master_route`)
    REFERENCES `mydb`.`master_routes` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_master_routes_has_master_truck_loadout_master_truck_loadout1`
    FOREIGN KEY (`id_master_loadout`)
    REFERENCES `mydb`.`master_truck_loadout` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_master_prices_master_customers1`
    FOREIGN KEY (`id_master_customer`)
    REFERENCES `mydb`.`master_customers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`master_role`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`master_role` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `id_master_role` INT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_users_master_role1_idx` (`id_master_role` ASC) VISIBLE,
  CONSTRAINT `fk_users_master_role1`
    FOREIGN KEY (`id_master_role`)
    REFERENCES `mydb`.`master_role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`transactions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`transactions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_master_customer` INT NOT NULL,
  `id_master_sales` INT NOT NULL,
  `id_transaction` INT NOT NULL,
  `code` VARCHAR(64) NOT NULL,
  `invoice_date` DATE NOT NULL,
  `ppn` DECIMAL(17,2) NOT NULL,
  `pph` DECIMAL(17,2) NOT NULL,
  `discount` DECIMAL(17,2) NOT NULL,
  `total` DECIMAL(17,2) NOT NULL,
  `grand_total` DECIMAL(17,2) NULL,
  `type` ENUM('quotation', 'salesorder') NULL,
  `status` ENUM('pending', 'approved', 'rejected', 'onprogress', 'done', 'paid') NULL DEFAULT 'pending',
  `notes` MEDIUMTEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_transactions_master_customers1_idx` (`id_master_customer` ASC) VISIBLE,
  INDEX `fk_transactions_users1_idx` (`id_master_sales` ASC) VISIBLE,
  INDEX `fk_transactions_transactions1_idx` (`id_transaction` ASC) VISIBLE,
  CONSTRAINT `fk_transactions_master_customers1`
    FOREIGN KEY (`id_master_customer`)
    REFERENCES `mydb`.`master_customers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_transactions_users1`
    FOREIGN KEY (`id_master_sales`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_transactions_transactions1`
    FOREIGN KEY (`id_transaction`)
    REFERENCES `mydb`.`transactions` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`transaction_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`transaction_details` (
  `id` INT NOT NULL,
  `id_transaction` INT NOT NULL,
  `id_origin` INT NOT NULL,
  `id_destination` INT NOT NULL,
  `id_loadout` INT NOT NULL,
  `price` DECIMAL(17,2) NOT NULL,
  `pocket_money_1` DECIMAL(17,2) NOT NULL,
  `pocket_money_2` DECIMAL(17,2) NULL,
  `pocket_money_3` DECIMAL(17,2) NULL,
  `sub_total` DECIMAL(17,2) NOT NULL,
  `quantity` INT NOT NULL,
  `origin_action` ENUM('unload', 'load') NOT NULL,
  `destination_action` ENUM('unload', 'load') NOT NULL,
  `notes` MEDIUMTEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_transaction_details_transactions1_idx` (`id_transaction` ASC) VISIBLE,
  INDEX `fk_transaction_details_master_locations1_idx` (`id_origin` ASC) VISIBLE,
  INDEX `fk_transaction_details_master_locations2_idx` (`id_destination` ASC) VISIBLE,
  INDEX `fk_transaction_details_master_truck_loadout1_idx` (`id_loadout` ASC) VISIBLE,
  CONSTRAINT `fk_transaction_details_transactions1`
    FOREIGN KEY (`id_transaction`)
    REFERENCES `mydb`.`transactions` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_transaction_details_master_locations1`
    FOREIGN KEY (`id_origin`)
    REFERENCES `mydb`.`master_locations` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_transaction_details_master_locations2`
    FOREIGN KEY (`id_destination`)
    REFERENCES `mydb`.`master_locations` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_transaction_details_master_truck_loadout1`
    FOREIGN KEY (`id_loadout`)
    REFERENCES `mydb`.`master_truck_loadout` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`delivery_orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`delivery_orders` (
  `id` INT NOT NULL,
  `code` VARCHAR(64) NOT NULL,
  `id_transaction` INT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) ZEROFILL NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_delivery_order_transactions1_idx` (`id_transaction` ASC) VISIBLE,
  CONSTRAINT `fk_delivery_order_transactions1`
    FOREIGN KEY (`id_transaction`)
    REFERENCES `mydb`.`transactions` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`deliver_order_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`deliver_order_details` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_delivery_order` INT NOT NULL,
  `id_detail` INT NOT NULL,
  `id_driver` INT NOT NULL,
  `id_truck` INT NOT NULL,
  `delivery_date` DATE NOT NULL,
  `status` ENUM('pending', 'loading', 'unloading', 'ondelivery', 'onhold', 'finish') NOT NULL DEFAULT 'pending',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) ZEROFILL NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_deliver_order_details_delivery_order1_idx` (`id_delivery_order` ASC) VISIBLE,
  INDEX `fk_deliver_order_details_transaction_details1_idx` (`id_detail` ASC) VISIBLE,
  INDEX `fk_deliver_order_details_master_drivers1_idx` (`id_driver` ASC) VISIBLE,
  INDEX `fk_deliver_order_details_master_trucks1_idx` (`id_truck` ASC) VISIBLE,
  CONSTRAINT `fk_deliver_order_details_delivery_order1`
    FOREIGN KEY (`id_delivery_order`)
    REFERENCES `mydb`.`delivery_orders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_deliver_order_details_transaction_details1`
    FOREIGN KEY (`id_detail`)
    REFERENCES `mydb`.`transaction_details` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_deliver_order_details_master_drivers1`
    FOREIGN KEY (`id_driver`)
    REFERENCES `mydb`.`master_drivers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_deliver_order_details_master_trucks1`
    FOREIGN KEY (`id_truck`)
    REFERENCES `mydb`.`master_trucks` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`delivery_order_status_logs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`delivery_order_status_logs` (
  `id` INT NOT NULL,
  `id_delivery_order_detail` INT NOT NULL,
  `status` ENUM('pending', 'loading', 'unloading', 'ondelivery', 'onhold', 'finish') NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) ZEROFILL NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_delivery_order_status_log_deliver_order_details1_idx` (`id_delivery_order_detail` ASC) VISIBLE,
  CONSTRAINT `fk_delivery_order_status_log_deliver_order_details1`
    FOREIGN KEY (`id_delivery_order_detail`)
    REFERENCES `mydb`.`deliver_order_details` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`delivery_order_files`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`delivery_order_files` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_delivery_detail_log_status` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `type` ENUM('loading', 'unloading', 'onhold') NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) ZEROFILL NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_delivery_order_file_delivery_order_status_log1_idx` (`id_delivery_detail_log_status` ASC) VISIBLE,
  CONSTRAINT `fk_delivery_order_file_delivery_order_status_log1`
    FOREIGN KEY (`id_delivery_detail_log_status`)
    REFERENCES `mydb`.`delivery_order_status_logs` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`account_categories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`account_categories` (
  `id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`account_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`account_types` (
  `id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`accounts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_category` INT NOT NULL,
  `id_type` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `code` VARCHAR(16) NOT NULL,
  `nature` ENUM('debit', 'credit') NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_accounts_account_categories1_idx` (`id_category` ASC) VISIBLE,
  INDEX `fk_accounts_account_types1_idx` (`id_type` ASC) VISIBLE,
  CONSTRAINT `fk_accounts_account_categories1`
    FOREIGN KEY (`id_category`)
    REFERENCES `mydb`.`account_categories` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_accounts_account_types1`
    FOREIGN KEY (`id_type`)
    REFERENCES `mydb`.`account_types` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`invoices`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`invoices` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(64) NOT NULL,
  `invoice_date` DATE NOT NULL,
  `total` DECIMAL(17,2) NOT NULL,
  `total_discount` DECIMAL(17,2) NOT NULL,
  `total_ppn` DECIMAL(17,2) NOT NULL,
  `total_pph` DECIMAL(17,2) NOT NULL,
  `grand_total` DECIMAL(17,2) NOT NULL,
  `status` ENUM('pending', 'paid', 'verified', 'closed') NOT NULL DEFAULT 'pending',
  `id_payment_account` INT NOT NULL,
  `notes` MEDIUMTEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_invoices_accounts1_idx` (`id_payment_account` ASC) VISIBLE,
  CONSTRAINT `fk_invoices_accounts1`
    FOREIGN KEY (`id_payment_account`)
    REFERENCES `mydb`.`accounts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`invoice_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`invoice_details` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_invoice` INT NOT NULL,
  `id_transaction` INT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) ZEROFILL NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_invoice_details_transactions1_idx` (`id_transaction` ASC) VISIBLE,
  INDEX `fk_invoice_details_invoices1_idx` (`id_invoice` ASC) VISIBLE,
  CONSTRAINT `fk_invoice_details_transactions1`
    FOREIGN KEY (`id_transaction`)
    REFERENCES `mydb`.`transactions` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_invoice_details_invoices1`
    FOREIGN KEY (`id_invoice`)
    REFERENCES `mydb`.`invoices` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`journals`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`journals` (
  `id` INT NOT NULL,
  `code` VARCHAR(64) NOT NULL,
  `date` DATE NOT NULL,
  `reference_type` VARCHAR(64) NOT NULL,
  `reference_id` INT NOT NULL,
  `description` VARCHAR(255) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  `deleted_at` TIMESTAMP NULL,
  `created_by` INT(11) NOT NULL,
  `updated_by` INT(11) NULL,
  `deleted_by` INT(11) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`account_journals`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`account_journals` (
  `id` INT NOT NULL,
  `id_journal` INT NOT NULL,
  `id_accounts` INT NOT NULL,
  `code` VARCHAR(64) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_account_journals_accounts1_idx` (`id_accounts` ASC) VISIBLE,
  INDEX `fk_account_journals_journals1_idx` (`id_journal` ASC) VISIBLE,
  CONSTRAINT `fk_account_journals_accounts1`
    FOREIGN KEY (`id_accounts`)
    REFERENCES `mydb`.`accounts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_account_journals_journals1`
    FOREIGN KEY (`id_journal`)
    REFERENCES `mydb`.`journals` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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