-- =============================================================================
-- XPERT 3PL - Migracion 018
-- Expedicion: maestros de flota (vehiculos) y choferes, y el viaje de despacho
-- que agrupa uno o varios documentos de egreso YA empacados (de la misma o
-- distintas empresas) en un mismo camion/chofer. Reemplaza al despacho suelto
-- por documento (texto libre de transportista/chofer/patente) que tenia
-- OutboundDocumentController::dispatch() - esta migracion no toca esa tabla,
-- el cambio de codigo se hace aparte.
-- =============================================================================

CREATE TABLE IF NOT EXISTS `vehicles` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `plate`          VARCHAR(20)  NOT NULL,
    `brand`          VARCHAR(60)  NULL,
    `model`          VARCHAR(60)  NULL,
    `vehicle_type`   ENUM('CAMION','FURGON','CAMIONETA','SEMIRREMOLQUE','MOTO') NOT NULL DEFAULT 'CAMION',
    `max_weight_kg`  DECIMAL(12,2) NULL COMMENT 'Capacidad de carga declarada',
    `max_volume_m3`  DECIMAL(10,4) NULL,
    `carrier_name`   VARCHAR(150) NULL COMMENT 'Transportista tercerizado dueno del vehiculo, si no es flota propia',
    `is_active`      TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_vehicles_plate` (`plate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Flota - nunca se borra, se desactiva';

CREATE TABLE IF NOT EXISTS `drivers` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `full_name`       VARCHAR(150) NOT NULL,
    `document_number` VARCHAR(30)  NULL,
    `license_number`  VARCHAR(30)  NULL,
    `license_category` VARCHAR(10) NULL COMMENT 'Ej: B, C, E segun categoria de licencia de conducir',
    `phone`           VARCHAR(30)  NULL,
    `is_active`       TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Choferes - nunca se borra, se desactiva';

CREATE TABLE IF NOT EXISTS `dispatch_trips` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `trip_number`   VARCHAR(30)  NOT NULL,
    `warehouse_id`  INT UNSIGNED NOT NULL,
    `vehicle_id`    INT UNSIGNED NOT NULL,
    `driver_id`     INT UNSIGNED NOT NULL,
    `status`        ENUM('PLANNING','DISPATCHED','CANCELLED') NOT NULL DEFAULT 'PLANNING',
    `notes`         TEXT NULL,
    `dispatched_at` DATETIME NULL,
    `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_by`    INT UNSIGNED NULL,
    UNIQUE KEY `uq_trip_number` (`trip_number`),
    KEY `idx_trip_warehouse` (`warehouse_id`),
    KEY `idx_trip_status` (`status`),
    CONSTRAINT `fk_trip_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_trip_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`),
    CONSTRAINT `fk_trip_driver` FOREIGN KEY (`driver_id`) REFERENCES `drivers`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Hoja de ruta / viaje de despacho. Puede llevar documentos de varias empresas a la vez';

CREATE TABLE IF NOT EXISTS `dispatch_trip_documents` (
    `id`                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `trip_id`               INT UNSIGNED NOT NULL,
    `outbound_document_id`  INT UNSIGNED NOT NULL,
    `added_at`              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_tripdoc` (`trip_id`, `outbound_document_id`),
    UNIQUE KEY `uq_tripdoc_document` (`outbound_document_id`) COMMENT 'Un documento solo puede viajar en un viaje a la vez',
    CONSTRAINT `fk_tripdoc_trip` FOREIGN KEY (`trip_id`) REFERENCES `dispatch_trips`(`id`),
    CONSTRAINT `fk_tripdoc_document` FOREIGN KEY (`outbound_document_id`) REFERENCES `outbound_documents`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `doc_sequences` (`sequence_key`, `prefix`, `current_value`, `pad_length`) VALUES
    ('TRIP', 'VIA-', 0, 8)
ON DUPLICATE KEY UPDATE `prefix` = VALUES(`prefix`);
