-- ============================================================================
-- UPPI CD-01 - DDL COMPLETO (20 tabelas)
-- Executar em db_erp_cd01
-- ============================================================================


-- ─── Catalogo local (sincronizado do central) ───
CREATE TABLE IF NOT EXISTS `products_local` (
    `id`                BIGINT UNSIGNED NOT NULL COMMENT 'Mesmo ID do central',
    `seller_id`         BIGINT UNSIGNED NOT NULL,
    `sku`               VARCHAR(50)     NOT NULL,
    `name`              VARCHAR(300)    NOT NULL,
    `ean`               VARCHAR(14)     NULL DEFAULT NULL,
    `weight_kg`         DECIMAL(8,3)    NULL DEFAULT NULL,
    `width_cm`          DECIMAL(8,2)    NULL DEFAULT NULL,
    `height_cm`         DECIMAL(8,2)    NULL DEFAULT NULL,
    `depth_cm`          DECIMAL(8,2)    NULL DEFAULT NULL,
    `ncm`               VARCHAR(10)     NULL DEFAULT NULL,
    `is_active`         TINYINT(1)      NOT NULL DEFAULT 1,
    `synced_at`         TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_pl_sku` (`sku`),
    KEY `idx_pl_ean` (`ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Pedidos locais (recebidos do central) ───
CREATE TABLE IF NOT EXISTS `orders_local` (
    `id`                    BIGINT UNSIGNED NOT NULL COMMENT 'ID do order_split no central',
    `order_id`              BIGINT UNSIGNED NOT NULL,
    `order_number`          VARCHAR(30)     NOT NULL,
    `split_number`          VARCHAR(30)     NOT NULL,
    `seller_id`             BIGINT UNSIGNED NOT NULL,
    `status`                VARCHAR(30)     NOT NULL DEFAULT 'pending',
    `shipping_recipient`    VARCHAR(200)    NOT NULL,
    `shipping_street`       VARCHAR(300)    NOT NULL,
    `shipping_number`       VARCHAR(20)     NOT NULL,
    `shipping_complement`   VARCHAR(200)    NULL DEFAULT NULL,
    `shipping_neighborhood` VARCHAR(150)    NOT NULL,
    `shipping_city`         VARCHAR(150)    NOT NULL,
    `shipping_state`        CHAR(2)         NOT NULL,
    `shipping_zipcode`      VARCHAR(10)     NOT NULL,
    `shipping_latitude`     DECIMAL(10,7)   NULL DEFAULT NULL,
    `shipping_longitude`    DECIMAL(10,7)   NULL DEFAULT NULL,
    `shipping_phone`        VARCHAR(20)     NULL DEFAULT NULL,
    `customer_note`         TEXT            NULL DEFAULT NULL,
    `total`                 DECIMAL(15,2)   NOT NULL,
    `synced_at`             TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`            TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ol_status` (`status`),
    KEY `idx_ol_order` (`order_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_items_local` (
    `id`                BIGINT UNSIGNED NOT NULL COMMENT 'ID do order_item no central',
    `order_local_id`    BIGINT UNSIGNED NOT NULL,
    `product_id`        BIGINT UNSIGNED NOT NULL,
    `variant_id`        BIGINT UNSIGNED NULL DEFAULT NULL,
    `product_name`      VARCHAR(300)    NOT NULL,
    `sku`               VARCHAR(50)     NOT NULL,
    `ean`               VARCHAR(14)     NULL DEFAULT NULL,
    `quantity`          INT UNSIGNED    NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_oil_order` (`order_local_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── WMS: Zonas e Locacoes ───
CREATE TABLE IF NOT EXISTS `warehouse_zones` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code`          VARCHAR(30)     NOT NULL,
    `name`          VARCHAR(100)    NOT NULL,
    `zone_type`     VARCHAR(20)     NOT NULL DEFAULT 'storage',
    `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_wz_code` (`code`),
    CONSTRAINT `chk_wz_type` CHECK (`zone_type` IN ('receiving','storage','picking','packing','shipping','returns'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `warehouse_locations` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `zone_id`       BIGINT UNSIGNED NOT NULL,
    `code`          VARCHAR(30)     NOT NULL,
    `aisle`         VARCHAR(10)     NULL DEFAULT NULL,
    `rack`          VARCHAR(10)     NULL DEFAULT NULL,
    `shelf`         VARCHAR(10)     NULL DEFAULT NULL,
    `bin`           VARCHAR(10)     NULL DEFAULT NULL,
    `max_weight_kg` DECIMAL(8,2)    NULL DEFAULT NULL,
    `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_wl_code` (`zone_id`, `code`),
    CONSTRAINT `fk_wl_zone` FOREIGN KEY (`zone_id`) REFERENCES `warehouse_zones`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── WMS: Estoque ───
CREATE TABLE IF NOT EXISTS `wms_stock` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `product_id`        BIGINT UNSIGNED NOT NULL,
    `variant_id`        BIGINT UNSIGNED NULL DEFAULT NULL,
    `seller_id`         BIGINT UNSIGNED NOT NULL,
    `location_id`       BIGINT UNSIGNED NULL DEFAULT NULL,
    `quantity`          INT             NOT NULL DEFAULT 0,
    `reserved`          INT             NOT NULL DEFAULT 0,
    `available`         INT GENERATED ALWAYS AS (`quantity` - `reserved`) STORED,
    `min_quantity`      INT             NOT NULL DEFAULT 0,
    `lot_number`        VARCHAR(50)     NULL DEFAULT NULL,
    `expiration_date`   DATE            NULL DEFAULT NULL,
    `unit_cost`         DECIMAL(15,2)   NULL DEFAULT NULL,
    `last_counted_at`   TIMESTAMP       NULL DEFAULT NULL,
    `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_stock_pos` (`product_id`, `variant_id`, `location_id`, `lot_number`),
    KEY `idx_stock_product` (`product_id`),
    KEY `idx_stock_available` (`available`),
    CONSTRAINT `fk_stock_location` FOREIGN KEY (`location_id`) REFERENCES `warehouse_locations`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wms_stock_movements` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `stock_id`          BIGINT UNSIGNED NOT NULL,
    `movement_type`     VARCHAR(20)     NOT NULL,
    `quantity`          INT             NOT NULL,
    `quantity_before`   INT             NOT NULL,
    `quantity_after`    INT             NOT NULL,
    `reference_type`    VARCHAR(50)     NULL DEFAULT NULL,
    `reference_id`      BIGINT UNSIGNED NULL DEFAULT NULL,
    `reason`            VARCHAR(300)    NULL DEFAULT NULL,
    `performed_by`      BIGINT UNSIGNED NULL DEFAULT NULL,
    `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_sm_stock` (`stock_id`),
    KEY `idx_sm_type` (`movement_type`),
    KEY `idx_sm_created` (`created_at`),
    CONSTRAINT `fk_sm_stock` FOREIGN KEY (`stock_id`) REFERENCES `wms_stock`(`id`),
    CONSTRAINT `chk_sm_type` CHECK (`movement_type` IN (
        'inbound','outbound','reserve','release','adjustment',
        'transfer_in','transfer_out','return','damage','count'
    ))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── WMS: Recebimento ───
CREATE TABLE IF NOT EXISTS `wms_receiving` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `receiving_number`  VARCHAR(30)     NOT NULL,
    `type`              VARCHAR(20)     NOT NULL DEFAULT 'purchase',
    `seller_id`         BIGINT UNSIGNED NULL DEFAULT NULL,
    `supplier_name`     VARCHAR(300)    NULL DEFAULT NULL,
    `invoice_number`    VARCHAR(50)     NULL DEFAULT NULL,
    `invoice_key`       VARCHAR(50)     NULL DEFAULT NULL,
    `expected_date`     DATE            NULL DEFAULT NULL,
    `received_date`     DATE            NULL DEFAULT NULL,
    `status`            VARCHAR(20)     NOT NULL DEFAULT 'pending',
    `total_expected`    INT UNSIGNED    NOT NULL DEFAULT 0,
    `total_received`    INT UNSIGNED    NOT NULL DEFAULT 0,
    `total_rejected`    INT UNSIGNED    NOT NULL DEFAULT 0,
    `notes`             TEXT            NULL DEFAULT NULL,
    `received_by`       BIGINT UNSIGNED NULL DEFAULT NULL,
    `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_recv_number` (`receiving_number`),
    KEY `idx_recv_status` (`status`),
    CONSTRAINT `chk_recv_type` CHECK (`type` IN ('purchase','return','transfer','adjustment')),
    CONSTRAINT `chk_recv_status` CHECK (`status` IN ('pending','in_progress','completed','cancelled'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wms_receiving_items` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `receiving_id`      BIGINT UNSIGNED NOT NULL,
    `product_id`        BIGINT UNSIGNED NOT NULL,
    `variant_id`        BIGINT UNSIGNED NULL DEFAULT NULL,
    `expected_qty`      INT UNSIGNED    NOT NULL,
    `received_qty`      INT UNSIGNED    NOT NULL DEFAULT 0,
    `rejected_qty`      INT UNSIGNED    NOT NULL DEFAULT 0,
    `location_id`       BIGINT UNSIGNED NULL DEFAULT NULL,
    `lot_number`        VARCHAR(50)     NULL DEFAULT NULL,
    `expiration_date`   DATE            NULL DEFAULT NULL,
    `notes`             VARCHAR(500)    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_ri_receiving` (`receiving_id`),
    CONSTRAINT `fk_ri_receiving` FOREIGN KEY (`receiving_id`) REFERENCES `wms_receiving`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ri_location` FOREIGN KEY (`location_id`) REFERENCES `warehouse_locations`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── WMS: Picking ───
CREATE TABLE IF NOT EXISTS `wms_picking_orders` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_local_id`    BIGINT UNSIGNED NOT NULL,
    `picking_number`    VARCHAR(30)     NOT NULL,
    `type`              VARCHAR(20)     NOT NULL DEFAULT 'single',
    `priority`          TINYINT UNSIGNED NOT NULL DEFAULT 5,
    `status`            VARCHAR(20)     NOT NULL DEFAULT 'pending',
    `assigned_to`       BIGINT UNSIGNED NULL DEFAULT NULL,
    `started_at`        TIMESTAMP       NULL DEFAULT NULL,
    `completed_at`      TIMESTAMP       NULL DEFAULT NULL,
    `total_items`       INT UNSIGNED    NOT NULL DEFAULT 0,
    `picked_items`      INT UNSIGNED    NOT NULL DEFAULT 0,
    `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_pick_number` (`picking_number`),
    KEY `idx_pick_status` (`status`, `priority`),
    CONSTRAINT `fk_pick_order` FOREIGN KEY (`order_local_id`) REFERENCES `orders_local`(`id`),
    CONSTRAINT `chk_pick_type` CHECK (`type` IN ('single','batch','wave','zone')),
    CONSTRAINT `chk_pick_status` CHECK (`status` IN ('pending','assigned','in_progress','completed','cancelled'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wms_picking_items` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `picking_order_id`  BIGINT UNSIGNED NOT NULL,
    `order_item_id`     BIGINT UNSIGNED NULL DEFAULT NULL,
    `product_id`        BIGINT UNSIGNED NOT NULL,
    `variant_id`        BIGINT UNSIGNED NULL DEFAULT NULL,
    `location_id`       BIGINT UNSIGNED NULL DEFAULT NULL,
    `quantity_required` INT UNSIGNED    NOT NULL,
    `quantity_picked`   INT UNSIGNED    NOT NULL DEFAULT 0,
    `status`            VARCHAR(20)     NOT NULL DEFAULT 'pending',
    `picked_at`         TIMESTAMP       NULL DEFAULT NULL,
    `picked_by`         BIGINT UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_pi_picking` (`picking_order_id`),
    CONSTRAINT `fk_pi_picking` FOREIGN KEY (`picking_order_id`) REFERENCES `wms_picking_orders`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_pi_location` FOREIGN KEY (`location_id`) REFERENCES `warehouse_locations`(`id`) ON DELETE SET NULL,
    CONSTRAINT `chk_pi_status` CHECK (`status` IN ('pending','picked','short','skipped'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── WMS: Inventario ───
CREATE TABLE IF NOT EXISTS `wms_inventory_counts` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `count_number`      VARCHAR(30)     NOT NULL,
    `type`              VARCHAR(20)     NOT NULL DEFAULT 'cycle',
    `zone_id`           BIGINT UNSIGNED NULL DEFAULT NULL,
    `status`            VARCHAR(20)     NOT NULL DEFAULT 'planned',
    `planned_date`      DATE            NULL DEFAULT NULL,
    `started_at`        TIMESTAMP       NULL DEFAULT NULL,
    `completed_at`      TIMESTAMP       NULL DEFAULT NULL,
    `created_by`        BIGINT UNSIGNED NULL DEFAULT NULL,
    `approved_by`       BIGINT UNSIGNED NULL DEFAULT NULL,
    `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_ic_number` (`count_number`),
    KEY `idx_ic_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wms_inventory_count_items` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `count_id`          BIGINT UNSIGNED NOT NULL,
    `stock_id`          BIGINT UNSIGNED NOT NULL,
    `system_qty`        INT             NOT NULL,
    `counted_qty`       INT             NULL DEFAULT NULL,
    `variance`          INT GENERATED ALWAYS AS (COALESCE(`counted_qty`,0) - `system_qty`) STORED,
    `counted_by`        BIGINT UNSIGNED NULL DEFAULT NULL,
    `counted_at`        TIMESTAMP       NULL DEFAULT NULL,
    `adjustment_applied` TINYINT(1)     NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_ici_count` (`count_id`),
    CONSTRAINT `fk_ici_count` FOREIGN KEY (`count_id`) REFERENCES `wms_inventory_counts`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ici_stock` FOREIGN KEY (`stock_id`) REFERENCES `wms_stock`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── TMS: Shipments locais ───
CREATE TABLE IF NOT EXISTS `shipments` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_local_id`        BIGINT UNSIGNED NOT NULL,
    `central_shipment_id`   BIGINT UNSIGNED NULL COMMENT 'ID no central apos sync',
    `shipment_number`       VARCHAR(30)     NOT NULL,
    `carrier_code`          VARCHAR(30)     NULL DEFAULT NULL,
    `carrier_name`          VARCHAR(200)    NULL DEFAULT NULL,
    `tracking_code`         VARCHAR(100)    NULL DEFAULT NULL,
    `invoice_key`           VARCHAR(50)     NULL DEFAULT NULL,
    `weight_kg`             DECIMAL(8,3)    NULL DEFAULT NULL,
    `declared_value`        DECIMAL(15,2)   NULL DEFAULT NULL,
    `shipping_cost`         DECIMAL(15,2)   NOT NULL DEFAULT 0.00,
    `status`                VARCHAR(20)     NOT NULL DEFAULT 'pending',
    `label_url`             VARCHAR(500)    NULL DEFAULT NULL,
    `shipped_at`            TIMESTAMP       NULL DEFAULT NULL,
    `delivered_at`          TIMESTAMP       NULL DEFAULT NULL,
    `delivery_attempts`     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`            TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`            TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_ship_number` (`shipment_number`),
    KEY `idx_ship_order` (`order_local_id`),
    KEY `idx_ship_status` (`status`),
    KEY `idx_ship_tracking` (`tracking_code`),
    CONSTRAINT `fk_ship_order` FOREIGN KEY (`order_local_id`) REFERENCES `orders_local`(`id`),
    CONSTRAINT `chk_ship_status` CHECK (`status` IN (
        'pending','label_created','picked_up','in_transit',
        'out_for_delivery','delivered','failed_delivery',
        'returning','returned','lost','cancelled'
    ))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── TMS: Rotas locais ───
CREATE TABLE IF NOT EXISTS `tms_routes` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `route_number`      VARCHAR(30)     NOT NULL,
    `driver_name`       VARCHAR(200)    NULL DEFAULT NULL,
    `driver_phone`      VARCHAR(20)     NULL DEFAULT NULL,
    `vehicle_plate`     VARCHAR(10)     NULL DEFAULT NULL,
    `date`              DATE            NOT NULL,
    `status`            VARCHAR(20)     NOT NULL DEFAULT 'planned',
    `total_stops`       INT UNSIGNED    NOT NULL DEFAULT 0,
    `completed_stops`   INT UNSIGNED    NOT NULL DEFAULT 0,
    `total_distance_km` DECIMAL(10,2)   NULL DEFAULT NULL,
    `started_at`        TIMESTAMP       NULL DEFAULT NULL,
    `completed_at`      TIMESTAMP       NULL DEFAULT NULL,
    `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_routes_number` (`route_number`),
    KEY `idx_routes_date` (`date`),
    KEY `idx_routes_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tms_route_stops` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `route_id`          BIGINT UNSIGNED NOT NULL,
    `shipment_id`       BIGINT UNSIGNED NOT NULL,
    `stop_order`        INT UNSIGNED    NOT NULL,
    `recipient`         VARCHAR(200)    NOT NULL,
    `address`           VARCHAR(500)    NOT NULL,
    `phone`             VARCHAR(20)     NULL DEFAULT NULL,
    `latitude`          DECIMAL(10,7)   NOT NULL,
    `longitude`         DECIMAL(10,7)   NOT NULL,
    `status`            VARCHAR(20)     NOT NULL DEFAULT 'pending',
    `actual_arrival`    TIMESTAMP       NULL DEFAULT NULL,
    `attempt_count`     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `failure_reason`    VARCHAR(300)    NULL DEFAULT NULL,
    `proof_photo_url`   VARCHAR(500)    NULL DEFAULT NULL,
    `proof_recipient`   VARCHAR(200)    NULL DEFAULT NULL,
    `proof_latitude`    DECIMAL(10,7)   NULL DEFAULT NULL,
    `proof_longitude`   DECIMAL(10,7)   NULL DEFAULT NULL,
    `notes`             TEXT            NULL DEFAULT NULL,
    `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_stops_route` (`route_id`, `stop_order`),
    KEY `idx_stops_shipment` (`shipment_id`),
    CONSTRAINT `fk_stops_route` FOREIGN KEY (`route_id`) REFERENCES `tms_routes`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_stops_shipment` FOREIGN KEY (`shipment_id`) REFERENCES `shipments`(`id`),
    CONSTRAINT `chk_stops_status` CHECK (`status` IN ('pending','arrived','delivered','failed','skipped','rescheduled'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Sync outbox ───
CREATE TABLE IF NOT EXISTS `sync_outbox` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `event_type`    VARCHAR(100)    NOT NULL,
    `entity_type`   VARCHAR(50)     NOT NULL,
    `entity_id`     BIGINT UNSIGNED NOT NULL,
    `payload`       JSON            NOT NULL,
    `sent`          TINYINT(1)      NOT NULL DEFAULT 0,
    `sent_at`       TIMESTAMP       NULL DEFAULT NULL,
    `retries`       TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_outbox_sent` (`sent`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─── Zonas padrao ───
INSERT INTO `warehouse_zones` (`code`, `name`, `zone_type`) VALUES
('RCV-01', 'Recebimento', 'receiving'),
('STR-01', 'Armazenagem Principal', 'storage'),
('STR-02', 'Armazenagem Secundaria', 'storage'),
('PCK-01', 'Area de Picking', 'picking'),
('PAK-01', 'Embalagem', 'packing'),
('SHP-01', 'Expedicao', 'shipping'),
('RET-01', 'Devolucoes', 'returns');
