-- Migration: Add repair order support to orders table
-- Run on production: ssh mkl 'mysql -u root mkl < /var/www/html/mkl/scripts/migrations/add-repair-order-fields.sql'

-- 1. Add repair-specific columns to orders table
ALTER TABLE orders
  ADD COLUMN order_type ENUM('sales','repair') NOT NULL DEFAULT 'sales' AFTER zte_received_at,
  ADD COLUMN repair_type ENUM('warranty','paid','evaluation') DEFAULT NULL AFTER order_type,
  ADD COLUMN diagnosis TEXT DEFAULT NULL AFTER repair_type,
  ADD COLUMN resolution TEXT DEFAULT NULL AFTER diagnosis,
  ADD COLUMN condition_in VARCHAR(100) DEFAULT NULL AFTER resolution,
  ADD COLUMN condition_out VARCHAR(100) DEFAULT NULL AFTER condition_in,
  ADD COLUMN inbound_tracking VARCHAR(255) DEFAULT NULL AFTER condition_out,
  ADD COLUMN inbound_carrier VARCHAR(100) DEFAULT NULL AFTER inbound_tracking,
  ADD COLUMN quoted_amount DECIMAL(12,2) DEFAULT NULL AFTER inbound_carrier,
  ADD COLUMN final_amount DECIMAL(12,2) DEFAULT NULL AFTER quoted_amount,
  ADD COLUMN assigned_to INT DEFAULT NULL AFTER final_amount,
  ADD COLUMN created_by INT DEFAULT NULL AFTER assigned_to,
  ADD COLUMN received_at TIMESTAMP NULL AFTER created_by,
  ADD COLUMN diagnosed_at TIMESTAMP NULL AFTER received_at,
  ADD COLUMN completed_at TIMESTAMP NULL AFTER diagnosed_at,
  ADD COLUMN shipped_at TIMESTAMP NULL AFTER completed_at,
  ADD COLUMN delivered_at TIMESTAMP NULL AFTER shipped_at;

-- 2. Add indexes for repair queries
ALTER TABLE orders
  ADD INDEX idx_order_type (order_type),
  ADD INDEX idx_order_type_status (order_type, status),
  ADD INDEX idx_assigned_to (assigned_to);

-- 3. Add foreign keys for assigned_to and created_by
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_assigned_to FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_orders_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;

-- 4. Expand status ENUM to include repair statuses
ALTER TABLE orders
  MODIFY COLUMN status ENUM('new','awaiting','customer_response','approved','on_order','in_transit','fulfillment','picked','ready_to_ship','shipped','delivered','complete','closed','cancelled','return','inbound','in_process') DEFAULT 'new';

-- 5. Add RMA sequence to purchase_order_sequences
INSERT IGNORE INTO purchase_order_sequences (prefix, next_number) VALUES ('RMA', 100001);

-- 6. Create order-ticket link table
CREATE TABLE IF NOT EXISTS order_tickets (
    order_id INT NOT NULL,
    ticket_id INT NOT NULL,
    linked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id, ticket_id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- All existing orders are unaffected — they default to order_type='sales'
-- and all new columns are NULL for sales orders.