-- V9 Migration: customers code, vehicles owner fields, suppliers, supplier links
ALTER TABLE customers ADD COLUMN customer_code VARCHAR(30) NULL UNIQUE AFTER id;

ALTER TABLE vehicles 
  ADD COLUMN owner_type VARCHAR(60) NULL AFTER source,
  ADD COLUMN owner_name VARCHAR(190) NULL AFTER owner_type,
  ADD COLUMN supplier_id INT NULL AFTER supplier_name;

CREATE TABLE IF NOT EXISTS suppliers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  nif VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  address VARCHAR(220) NULL,
  notes TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE vehicle_costs ADD COLUMN supplier_id INT NULL AFTER supplier;
ALTER TABLE documents ADD COLUMN supplier_id INT NULL AFTER supplier;

-- Indexes + FKs (safe)
CREATE INDEX idx_vehicle_supplier_id ON vehicles(supplier_id);
CREATE INDEX idx_cost_supplier_id ON vehicle_costs(supplier_id);
CREATE INDEX idx_doc_supplier_id ON documents(supplier_id);

ALTER TABLE vehicles
  ADD CONSTRAINT fk_vehicle_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL;

ALTER TABLE vehicle_costs
  ADD CONSTRAINT fk_cost_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL;

ALTER TABLE documents
  ADD CONSTRAINT fk_doc_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL;
