-- ============================================================================
-- LASER CUT SAAS - PRODUCTION SCHEMA
-- Tables for production workflow management
-- ============================================================================

-- Production Jobs
CREATE TABLE IF NOT EXISTS `production_jobs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `order_id` INT UNSIGNED NOT NULL,
  `job_number` VARCHAR(20) NOT NULL,
  `status` ENUM('pending', 'queued', 'in_progress', 'paused', 'quality_check', 'completed', 'cancelled') DEFAULT 'pending',
  `priority` ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
  `assigned_operator_id` INT UNSIGNED NULL,
  `current_step` VARCHAR(50) DEFAULT 'received',
  `total_steps` TINYINT UNSIGNED DEFAULT 11,
  `notes` TEXT NULL,
  `started_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `estimated_completion` DATETIME NULL,
  `actual_hours` DECIMAL(6,2) NULL,
  `estimated_hours` DECIMAL(6,2) NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_job_number` (`job_number`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_order_id` (`order_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_priority` (`priority`),
  INDEX `idx_operator` (`assigned_operator_id`),
  INDEX `idx_current_step` (`current_step`),
  CONSTRAINT `fk_production_jobs_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_production_jobs_order` FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_production_jobs_operator` FOREIGN KEY (`assigned_operator_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Production Steps
CREATE TABLE IF NOT EXISTS `production_steps` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` INT UNSIGNED NOT NULL,
  `step_key` VARCHAR(50) NOT NULL COMMENT 'received, artwork_review, preparation, etc',
  `step_name` VARCHAR(100) NOT NULL COMMENT 'Display name in pt-BR',
  `step_order` TINYINT UNSIGNED NOT NULL,
  `status` ENUM('pending', 'in_progress', 'paused', 'completed', 'skipped') DEFAULT 'pending',
  `assigned_operator_id` INT UNSIGNED NULL,
  `estimated_minutes` INT UNSIGNED NULL,
  `actual_minutes` INT UNSIGNED NULL,
  `notes` TEXT NULL,
  `started_at` DATETIME NULL,
  `paused_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_job_id` (`job_id`),
  INDEX `idx_step_key` (`step_key`),
  INDEX `idx_status` (`status`),
  INDEX `idx_step_order` (`step_order`),
  CONSTRAINT `fk_production_steps_job` FOREIGN KEY (`job_id`) REFERENCES `production_jobs`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Production Logs
CREATE TABLE IF NOT EXISTS `production_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` INT UNSIGNED NOT NULL,
  `step_id` INT UNSIGNED NULL,
  `user_id` INT UNSIGNED NULL,
  `action` VARCHAR(50) NOT NULL COMMENT 'job_created, step_started, step_completed, etc',
  `description` TEXT NULL,
  `old_value` VARCHAR(255) NULL,
  `new_value` VARCHAR(255) NULL,
  `metadata` TEXT NULL COMMENT 'JSON additional data',
  `ip_address` VARCHAR(45) NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_job_id` (`job_id`),
  INDEX `idx_step_id` (`step_id`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_action` (`action`),
  INDEX `idx_created_at` (`created_at`),
  CONSTRAINT `fk_production_logs_job` FOREIGN KEY (`job_id`) REFERENCES `production_jobs`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_production_logs_step` FOREIGN KEY (`step_id`) REFERENCES `production_steps`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Order Status History
CREATE TABLE IF NOT EXISTS `order_status_history` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `status` VARCHAR(50) NOT NULL,
  `notes` TEXT NULL,
  `created_by` INT UNSIGNED NULL,
  `notified_customer` TINYINT(1) DEFAULT 0,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_order_id` (`order_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_created_at` (`created_at`),
  CONSTRAINT `fk_order_status_history_order` FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;