USE ims_delivery;

SET FOREIGN_KEY_CHECKS = 0;

-- =========================================================
-- SUPPLIERS table updates
-- =========================================================

ALTER TABLE suppliers
  ADD COLUMN IF NOT EXISTS supplier_code VARCHAR(50) NULL AFTER id,
  ADD COLUMN IF NOT EXISTS city VARCHAR(120) NULL AFTER name,
  ADD COLUMN IF NOT EXISTS country VARCHAR(120) NULL AFTER city,
  ADD COLUMN IF NOT EXISTS contact_person VARCHAR(120) NULL,
  ADD COLUMN IF NOT EXISTS phone VARCHAR(40) NULL,
  ADD COLUMN IF NOT EXISTS email VARCHAR(120) NULL,
  ADD COLUMN IF NOT EXISTS address TEXT NULL,
  ADD COLUMN IF NOT EXISTS is_active TINYINT(1) NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

CREATE INDEX IF NOT EXISTS idx_suppliers_active ON suppliers(is_active);
CREATE UNIQUE INDEX IF NOT EXISTS uq_supplier_code ON suppliers(supplier_code);

-- =========================================================
-- PRODUCTS table updates (supplier support)
-- =========================================================

ALTER TABLE products
  ADD COLUMN IF NOT EXISTS supplier_id INT UNSIGNED NULL AFTER category_id;

CREATE INDEX IF NOT EXISTS idx_products_supplier ON products(supplier_id);

-- add FK only if it does not exist
ALTER TABLE products
  ADD CONSTRAINT fk_products_supplier
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
  ON DELETE SET NULL
  ON UPDATE CASCADE;

-- =========================================================
-- DELIVERIES table (timestamps safety)
-- =========================================================

ALTER TABLE deliveries
  ADD COLUMN IF NOT EXISTS picked_up_at DATETIME NULL,
  ADD COLUMN IF NOT EXISTS delivered_at DATETIME NULL;

-- =========================================================
-- DELIVERY ITEMS safety
-- =========================================================

ALTER TABLE delivery_items
  ADD COLUMN IF NOT EXISTS unit_price DECIMAL(12,2) NULL;

-- =========================================================
-- INVENTORY MOVEMENTS (reference support)
-- =========================================================

ALTER TABLE inventory_movements
  MODIFY COLUMN reference_type ENUM('PURCHASE','SALE','DELIVERY','MANUAL')
    NOT NULL DEFAULT 'MANUAL';

SET FOREIGN_KEY_CHECKS = 1;
