-- ============================================================================
-- UPPI MARKETPLACE - TABELAS EXTRAS: TMS, CDs, Regras, Stock Consolidado
-- Executar em db_erp
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─── TMS: Veículos ───
CREATE TABLE IF NOT EXISTS `tms_vehicles` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `warehouse_id`      BIGINT UNSIGNED NOT NULL,
    `plate`             VARCHAR(10)     NOT NULL,
    `model`             VARCHAR(100)    NOT NULL,
    `type`              VARCHAR(20)     NOT NULL DEFAULT 'van',
    `capacity_kg`       DECIMAL(8,2)    NOT NULL DEFAULT 0,
    `capacity_m3`       DECIMAL(8,4)    NOT NULL DEFAULT 0,
    `status`            VARCHAR(20)     NOT NULL DEFAULT 'available',
    `current_driver_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `last_maintenance`  DATE            NULL DEFAULT NULL,
    `next_maintenance`  DATE            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_vehicles_plate` (`plate`),
    CONSTRAINT `fk_vehicles_wh` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `chk_vehicles_type` CHECK (`type` IN ('bike','moto','car','van','truck','vuc')),
    CONSTRAINT `chk_vehicles_status` CHECK (`status` IN ('available','in_use','maintenance','inactive'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── TMS: Motoristas ───
CREATE TABLE IF NOT EXISTS `tms_drivers` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`           BIGINT UNSIGNED NOT NULL,
    `warehouse_id`      BIGINT UNSIGNED NULL DEFAULT NULL,
    `uuid`              CHAR(36)        NOT NULL,
    `cnh_number`        VARCHAR(20)     NULL DEFAULT NULL,
    `cnh_category`      VARCHAR(5)      NULL DEFAULT NULL,
    `cnh_expiration`    DATE            NULL DEFAULT NULL,
    `vehicle_id`        BIGINT UNSIGNED NULL DEFAULT NULL,
    `status`            VARCHAR(20)     NOT NULL DEFAULT 'active',
    `is_available`      TINYINT(1)      NOT NULL DEFAULT 1,
    `current_latitude`  DECIMAL(10,7)   NULL DEFAULT NULL,
    `current_longitude` DECIMAL(10,7)   NULL DEFAULT NULL,
    `location_updated_at` TIMESTAMP     NULL DEFAULT NULL,
    `rating_avg`        DECIMAL(3,2)    NOT NULL DEFAULT 5.00,
    `total_deliveries`  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_drivers_uuid` (`uuid`),
    KEY `idx_drivers_user` (`user_id`),
    KEY `idx_drivers_wh` (`warehouse_id`),
    KEY `idx_drivers_available` (`is_available`, `warehouse_id`),
    CONSTRAINT `fk_drivers_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
    CONSTRAINT `fk_drivers_wh` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_drivers_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `tms_vehicles`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── TMS: Rotas ───
CREATE TABLE IF NOT EXISTS `tms_routes` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid`              CHAR(36)        NOT NULL,
    `route_number`      VARCHAR(30)     NOT NULL,
    `warehouse_id`      BIGINT UNSIGNED NOT NULL,
    `driver_id`         BIGINT UNSIGNED NULL DEFAULT NULL,
    `vehicle_id`        BIGINT UNSIGNED 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,
    `total_weight_kg`   DECIMAL(10,2)   NULL DEFAULT NULL,
    `started_at`        TIMESTAMP       NULL DEFAULT NULL,
    `completed_at`      TIMESTAMP       NULL DEFAULT NULL,
    `optimized`         TINYINT(1)      NOT NULL DEFAULT 0,
    `optimization_data` JSON            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_uuid` (`uuid`),
    UNIQUE KEY `uk_routes_number` (`route_number`),
    KEY `idx_routes_wh_date` (`warehouse_id`, `date`),
    KEY `idx_routes_driver` (`driver_id`),
    KEY `idx_routes_status` (`status`),
    CONSTRAINT `fk_routes_wh` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `fk_routes_driver` FOREIGN KEY (`driver_id`) REFERENCES `tms_drivers`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_routes_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `tms_vehicles`(`id`) ON DELETE SET NULL,
    CONSTRAINT `chk_routes_status` CHECK (`status` IN ('planned','optimized','assigned','in_progress','completed','cancelled'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── TMS: Paradas da Rota ───
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,
    `address_id`        BIGINT UNSIGNED NULL DEFAULT NULL,
    `latitude`          DECIMAL(10,7)   NOT NULL,
    `longitude`         DECIMAL(10,7)   NOT NULL,
    `recipient`         VARCHAR(200)    NOT NULL,
    `phone`             VARCHAR(20)     NULL DEFAULT NULL,
    `instructions`      VARCHAR(500)    NULL DEFAULT NULL,
    `estimated_arrival` TIME            NULL DEFAULT NULL,
    `actual_arrival`    TIMESTAMP       NULL DEFAULT NULL,
    `status`            VARCHAR(20)     NOT NULL DEFAULT 'pending',
    `attempt_count`     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `failure_reason`    VARCHAR(300)    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`),
    KEY `idx_stops_status` (`status`),
    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;

-- ─── TMS: Provas de Entrega ───
CREATE TABLE IF NOT EXISTS `tms_delivery_proofs` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `shipment_id`       BIGINT UNSIGNED NOT NULL,
    `route_stop_id`     BIGINT UNSIGNED NULL DEFAULT NULL,
    `driver_id`         BIGINT UNSIGNED NOT NULL,
    `proof_type`        VARCHAR(20)     NOT NULL,
    `recipient_name`    VARCHAR(200)    NULL DEFAULT NULL,
    `recipient_document` VARCHAR(20)    NULL DEFAULT NULL,
    `signature_url`     VARCHAR(500)    NULL DEFAULT NULL,
    `photo_url`         VARCHAR(500)    NULL DEFAULT NULL,
    `latitude`          DECIMAL(10,7)   NULL DEFAULT NULL,
    `longitude`         DECIMAL(10,7)   NULL DEFAULT NULL,
    `notes`             VARCHAR(500)    NULL DEFAULT NULL,
    `collected_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_pod_shipment` (`shipment_id`),
    KEY `idx_pod_driver` (`driver_id`),
    CONSTRAINT `fk_pod_shipment` FOREIGN KEY (`shipment_id`) REFERENCES `shipments`(`id`),
    CONSTRAINT `fk_pod_driver` FOREIGN KEY (`driver_id`) REFERENCES `tms_drivers`(`id`),
    CONSTRAINT `chk_pod_type` CHECK (`proof_type` IN ('signature','photo','document','pin_code','no_contact'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── TMS: Tentativas de Entrega ───
CREATE TABLE IF NOT EXISTS `tms_delivery_attempts` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `shipment_id`       BIGINT UNSIGNED NOT NULL,
    `driver_id`         BIGINT UNSIGNED NOT NULL,
    `attempt_number`    TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `status`            VARCHAR(20)     NOT NULL,
    `failure_reason`    VARCHAR(50)     NULL DEFAULT NULL,
    `failure_detail`    VARCHAR(300)    NULL DEFAULT NULL,
    `latitude`          DECIMAL(10,7)   NULL DEFAULT NULL,
    `longitude`         DECIMAL(10,7)   NULL DEFAULT NULL,
    `photo_url`         VARCHAR(500)    NULL DEFAULT NULL,
    `next_attempt_date` DATE            NULL DEFAULT NULL,
    `attempted_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_attempts_shipment` (`shipment_id`),
    CONSTRAINT `fk_attempts_shipment` FOREIGN KEY (`shipment_id`) REFERENCES `shipments`(`id`),
    CONSTRAINT `fk_attempts_driver` FOREIGN KEY (`driver_id`) REFERENCES `tms_drivers`(`id`),
    CONSTRAINT `chk_attempts_status` CHECK (`status` IN ('delivered','absent','refused','wrong_address','damaged','other')),
    CONSTRAINT `chk_attempts_reason` CHECK (`failure_reason` IS NULL OR `failure_reason` IN (
        'no_one_home','address_not_found','refused_by_recipient','access_denied',
        'bad_weather','vehicle_issue','package_damaged','wrong_package','other'
    ))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── TMS: Zonas de Entrega ───
CREATE TABLE IF NOT EXISTS `tms_delivery_zones` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `warehouse_id`      BIGINT UNSIGNED NOT NULL,
    `name`              VARCHAR(100)    NOT NULL,
    `zipcode_from`      VARCHAR(8)      NOT NULL,
    `zipcode_to`        VARCHAR(8)      NOT NULL,
    `delivery_days`     TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `delivery_fee`      DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    `is_active`         TINYINT(1)      NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    KEY `idx_zones_wh` (`warehouse_id`),
    KEY `idx_zones_cep` (`zipcode_from`, `zipcode_to`),
    CONSTRAINT `fk_zones_wh` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── CDs: Sync Log ───
CREATE TABLE IF NOT EXISTS `cd_sync_log` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `warehouse_id`      BIGINT UNSIGNED NOT NULL,
    `direction`         VARCHAR(20)     NOT NULL,
    `entity_type`       VARCHAR(50)     NOT NULL,
    `entity_id`         BIGINT UNSIGNED NOT NULL,
    `action`            VARCHAR(20)     NOT NULL,
    `payload`           JSON            NOT NULL,
    `status`            VARCHAR(20)     NOT NULL DEFAULT 'pending',
    `error_message`     TEXT            NULL DEFAULT NULL,
    `retries`           TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `synced_at`         TIMESTAMP       NULL DEFAULT NULL,
    `created_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_sync_wh` (`warehouse_id`, `status`),
    KEY `idx_sync_entity` (`entity_type`, `entity_id`),
    KEY `idx_sync_created` (`created_at`),
    CONSTRAINT `fk_sync_wh` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`),
    CONSTRAINT `chk_sync_dir` CHECK (`direction` IN ('central_to_cd','cd_to_central')),
    CONSTRAINT `chk_sync_action` CHECK (`action` IN ('create','update','delete','stock_adjust','stock_sync')),
    CONSTRAINT `chk_sync_status` CHECK (`status` IN ('pending','processing','synced','failed','skipped'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── CDs: Stock Snapshot (Reconciliacao) ───
CREATE TABLE IF NOT EXISTS `cd_stock_snapshot` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `warehouse_id`      BIGINT UNSIGNED NOT NULL,
    `product_id`        BIGINT UNSIGNED NOT NULL,
    `variant_id`        BIGINT UNSIGNED NULL DEFAULT NULL,
    `central_qty`       INT             NOT NULL,
    `cd_qty`            INT             NOT NULL,
    `variance`          INT GENERATED ALWAYS AS (`cd_qty` - `central_qty`) STORED,
    `reconciled`        TINYINT(1)      NOT NULL DEFAULT 0,
    `reconciled_at`     TIMESTAMP       NULL DEFAULT NULL,
    `snapshot_at`       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_snapshot_wh` (`warehouse_id`, `snapshot_at`),
    KEY `idx_snapshot_variance` (`variance`),
    CONSTRAINT `fk_snapshot_wh` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Motor de Regras ───
CREATE TABLE IF NOT EXISTS `rules` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `domain`        VARCHAR(30)     NOT NULL,
    `name`          VARCHAR(200)    NOT NULL,
    `description`   VARCHAR(500)    NULL DEFAULT NULL,
    `conditions`    JSON            NOT NULL,
    `actions`       JSON            NOT NULL,
    `priority`      INT             NOT NULL DEFAULT 0,
    `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,
    `valid_from`    TIMESTAMP       NULL DEFAULT NULL,
    `valid_to`      TIMESTAMP       NULL DEFAULT NULL,
    `created_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`),
    KEY `idx_rules_domain` (`domain`, `is_active`, `priority`),
    CONSTRAINT `chk_rules_domain` CHECK (`domain` IN (
        'shipping_routing','warehouse_routing','payment_gateway','free_shipping',
        'commission','tax_calculation','ads_ranking','fraud_detection','auto_approve_return'
    ))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rules_execution_log` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `rule_id`       BIGINT UNSIGNED NOT NULL,
    `domain`        VARCHAR(30)     NOT NULL,
    `input_data`    JSON            NOT NULL,
    `result`        JSON            NOT NULL,
    `matched`       TINYINT(1)      NOT NULL,
    `execution_ms`  INT UNSIGNED    NOT NULL DEFAULT 0,
    `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_rlog_rule` (`rule_id`),
    KEY `idx_rlog_domain` (`domain`, `created_at`),
    CONSTRAINT `fk_rlog_rule` FOREIGN KEY (`rule_id`) REFERENCES `rules`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Stock Consolidado (cache para marketplace) ───
CREATE TABLE IF NOT EXISTS `stock_consolidated` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `product_id`    BIGINT UNSIGNED NOT NULL,
    `variant_id`    BIGINT UNSIGNED NULL DEFAULT NULL,
    `warehouse_id`  BIGINT UNSIGNED NOT NULL,
    `available`     INT             NOT NULL DEFAULT 0,
    `reserved`      INT             NOT NULL DEFAULT 0,
    `last_sync_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_stock_cons` (`product_id`, `variant_id`, `warehouse_id`),
    KEY `idx_stock_cons_product` (`product_id`, `available`),
    CONSTRAINT `fk_sc_product` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`),
    CONSTRAINT `fk_sc_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Idempotency Cache (para API middleware) ───
CREATE TABLE IF NOT EXISTS `idempotency_cache` (
    `key`           VARCHAR(255)    NOT NULL,
    `response_code` INT             NOT NULL,
    `response_body` JSON            NOT NULL,
    `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at`    TIMESTAMP       NOT NULL,
    PRIMARY KEY (`key`),
    KEY `idx_idemp_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
