-- Run once on company database (mysql2)
CREATE TABLE IF NOT EXISTS `gate_pass` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `gate_pass_no` VARCHAR(50) NOT NULL,
    `gate_pass_date` DATE NOT NULL,
    `so_id` INT UNSIGNED NOT NULL,
    `so_no` VARCHAR(50) NULL,
    `so_date` DATE NULL,
    `buyers_id` INT UNSIGNED NULL,
    `customer_name` VARCHAR(255) NULL,
    `destination` VARCHAR(255) NULL,
    `transporter_name` VARCHAR(255) NULL,
    `vehicle_no` VARCHAR(50) NULL,
    `vehicle_type` VARCHAR(50) NULL,
    `driver_name` VARCHAR(255) NULL,
    `driver_cnic` VARCHAR(30) NULL,
    `driver_contact` VARCHAR(30) NULL,
    `remarks` TEXT NULL,
    `gate_pass_status` TINYINT NOT NULL DEFAULT 1,
    `status` TINYINT NOT NULL DEFAULT 1,
    `username` VARCHAR(255) NULL,
    `date` DATE NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `gate_pass_data` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `master_id` INT UNSIGNED NOT NULL,
    `so_data_id` INT UNSIGNED NULL,
    `item_id` INT UNSIGNED NULL,
    `qty` DECIMAL(15,4) NOT NULL DEFAULT 0,
    `uom_id` INT UNSIGNED NULL,
    `remarks` VARCHAR(255) NULL,
    `status` TINYINT NOT NULL DEFAULT 1,
    `username` VARCHAR(255) NULL,
    `date` DATE NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`)
);
