-- =============================================================================
-- Manual SQL bundle (equivalent to Doctrine migrations, in dependency order):
--   Version20260418120000  maincompany.dimensioner_* columns
--   Version20260418140000  device_inventory
--   Version20260419120000  dimensioner_station
--   Version20260420120000  dimensioner_measurement_log
--
-- Run against the target database. If a step already exists, skip that block
-- or comment it out to avoid errors.
--
-- Requires: MySQL 5.7.8+ (JSON columns) / MariaDB 10.2+ with JSON support.
-- =============================================================================

SET NAMES utf8mb4;

-- -----------------------------------------------------------------------------
-- 1) Version20260418120000 — maincompany dimensioner flags
-- -----------------------------------------------------------------------------
ALTER TABLE `maincompany`
  ADD COLUMN `dimensioner_module_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN `dimensioner_module_status` VARCHAR(20) NOT NULL DEFAULT 'DISABLED';

-- -----------------------------------------------------------------------------
-- 2) Version20260418140000 — device_inventory
-- -----------------------------------------------------------------------------
CREATE TABLE `device_inventory` (
  `id` INT AUTO_INCREMENT NOT NULL,
  `asset_tag` VARCHAR(64) NOT NULL,
  `serial_number` VARCHAR(128) NOT NULL,
  `model` VARCHAR(120) NOT NULL,
  `fingerprint` VARCHAR(512) DEFAULT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'IN_STOCK',
  `notes` LONGTEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  UNIQUE INDEX `uniq_device_inventory_asset_tag` (`asset_tag`),
  INDEX `idx_device_inventory_status` (`status`),
  PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

-- -----------------------------------------------------------------------------
-- 3) Version20260419120000 — dimensioner_station
-- -----------------------------------------------------------------------------
CREATE TABLE `dimensioner_station` (
  `id` INT AUTO_INCREMENT NOT NULL,
  `maincompany_id` INT NOT NULL COMMENT 'Tenant (maincompany) that owns this station.',
  `device_inventory_id` INT NOT NULL COMMENT 'Physical device row in device_inventory.',
  `code` VARCHAR(64) NOT NULL COMMENT 'Human-readable unique station code, e.g. MIA-DIM-01.',
  `name` VARCHAR(120) NOT NULL COMMENT 'Display name for operators.',
  `fingerprint` VARCHAR(255) DEFAULT NULL COMMENT 'Machine fingerprint for client validation.',
  `status` VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING, ACTIVE, INACTIVE, BLOCKED.',
  `current_station_user_id` INT DEFAULT NULL COMMENT 'User currently assigned to the station session.',
  `activated_at` DATETIME DEFAULT NULL,
  `deactivated_at` DATETIME DEFAULT NULL,
  `blocked_at` DATETIME DEFAULT NULL,
  `last_seen_at` DATETIME DEFAULT NULL COMMENT 'Last heartbeat from station software.',
  `last_ip` VARCHAR(45) DEFAULT NULL COMMENT 'Last seen client IP (IPv4/IPv6).',
  `app_version` VARCHAR(40) DEFAULT NULL COMMENT 'Reported application build/version.',
  `notes` VARCHAR(500) DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY(`id`),
  UNIQUE INDEX `uniq_dimensioner_station_code` (`code`),
  UNIQUE INDEX `uniq_dimensioner_station_fingerprint` (`fingerprint`),
  INDEX `idx_dimensioner_station_maincompany` (`maincompany_id`),
  INDEX `idx_dimensioner_station_device_inventory` (`device_inventory_id`),
  INDEX `idx_dimensioner_station_status` (`status`),
  INDEX `idx_dimensioner_station_current_user` (`current_station_user_id`),
  CONSTRAINT `fk_dimensioner_station_company` FOREIGN KEY (`maincompany_id`) REFERENCES `maincompany` (`id`),
  CONSTRAINT `fk_dimensioner_station_inventory` FOREIGN KEY (`device_inventory_id`) REFERENCES `device_inventory` (`id`),
  CONSTRAINT `fk_dimensioner_station_user` FOREIGN KEY (`current_station_user_id`) REFERENCES `user` (`id`) ON DELETE SET NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

-- -----------------------------------------------------------------------------
-- 4) Version20260420120000 — dimensioner_measurement_log
-- -----------------------------------------------------------------------------
CREATE TABLE `dimensioner_measurement_log` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
  `company_id` INT NOT NULL COMMENT 'Tenant (maincompany) that owns this measurement.',
  `station_id` INT NOT NULL COMMENT 'Dimensioner station that performed the capture.',
  `captured_by_user_id` INT NOT NULL COMMENT 'User who performed the capture.',
  `tracking_number` VARCHAR(100) DEFAULT NULL COMMENT 'Detected or entered tracking number.',
  `tracking_source` VARCHAR(20) DEFAULT NULL COMMENT 'OCR, MANUAL, OCR_CORRECTED.',
  `carrier_code` VARCHAR(20) DEFAULT NULL COMMENT 'Detected carrier e.g. UPS, FEDEX.',
  `length` NUMERIC(10, 2) DEFAULT NULL,
  `width` NUMERIC(10, 2) DEFAULT NULL,
  `height` NUMERIC(10, 2) DEFAULT NULL,
  `dimension_unit` VARCHAR(5) DEFAULT NULL COMMENT 'CM, IN.',
  `weight` NUMERIC(10, 2) DEFAULT NULL,
  `weight_unit` VARCHAR(5) DEFAULT NULL COMMENT 'GR, LB.',
  `volumetric_weight` NUMERIC(10, 2) DEFAULT NULL,
  `billable_weight` NUMERIC(10, 2) DEFAULT NULL,
  `volumetric_factor` INT DEFAULT NULL COMMENT 'Divisor used e.g. 139, 166.',
  `ocr_status` VARCHAR(20) DEFAULT NULL COMMENT 'SUCCESS, PARTIAL, FAILED.',
  `ocr_confidence` NUMERIC(5, 2) DEFAULT NULL COMMENT '0–100 confidence score.',
  `ocr_raw_text` LONGTEXT DEFAULT NULL,
  `ocr_parsed_json` JSON DEFAULT NULL,
  `dimensioner_log_json` JSON DEFAULT NULL COMMENT 'Technical dimension calculation detail.',
  `package_image_path` VARCHAR(255) DEFAULT NULL,
  `measurement_overlay_image_path` VARCHAR(255) DEFAULT NULL,
  `label_image_path` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'CAPTURED' COMMENT 'CAPTURED, REVIEWED, CORRECTED, VOID, DISPUTED.',
  `has_manual_edits` TINYINT(1) NOT NULL DEFAULT 0,
  `manual_edit_reason` VARCHAR(255) DEFAULT NULL,
  `review_required` TINYINT(1) NOT NULL DEFAULT 0,
  `dispute_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `capture_started_at` DATETIME DEFAULT NULL,
  `capture_completed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_dimensioner_measurement_log_company_created` (`company_id`, `created_at`),
  INDEX `idx_dimensioner_measurement_log_station` (`station_id`),
  INDEX `idx_dimensioner_measurement_log_tracking` (`tracking_number`),
  INDEX `idx_dimensioner_measurement_log_status` (`status`),
  CONSTRAINT `fk_dimensioner_measurement_log_company` FOREIGN KEY (`company_id`) REFERENCES `maincompany` (`id`),
  CONSTRAINT `fk_dimensioner_measurement_log_station` FOREIGN KEY (`station_id`) REFERENCES `dimensioner_station` (`id`),
  CONSTRAINT `fk_dimensioner_measurement_log_user` FOREIGN KEY (`captured_by_user_id`) REFERENCES `user` (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

-- =============================================================================
-- Optional: register rows in doctrine_migration_versions so `migrate` skips them.
-- Uncomment ONLY if your table matches (Doctrine 3 default: version PK, executed_at).
-- In SQL, one backslash in the stored value is written as \\ inside single quotes.
-- =============================================================================
/*
INSERT INTO `doctrine_migration_versions` (`version`, `executed_at`) VALUES
  ('DoctrineMigrations\\Version20260418120000', NOW()),
  ('DoctrineMigrations\\Version20260418140000', NOW()),
  ('DoctrineMigrations\\Version20260419120000', NOW()),
  ('DoctrineMigrations\\Version20260420120000', NOW())
ON DUPLICATE KEY UPDATE `executed_at` = NOW();
*/