-- ===================================================================
-- Migration 3: customer directory + emailing invoices as PDF
-- ===================================================================
-- Run this ONCE against your existing database (phpMyAdmin → SQL tab,
-- with your database selected in the left sidebar → paste → Go).
-- Nothing existing is deleted or changed.
-- ===================================================================

CREATE TABLE IF NOT EXISTS customers (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(150) NOT NULL,
    email       VARCHAR(150) NOT NULL,
    phone       VARCHAR(50) NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

ALTER TABLE invoices
    ADD COLUMN customer_id INT NULL AFTER line_manager_id,
    ADD COLUMN customer_emailed_at DATETIME NULL AFTER approved_at,
    ADD CONSTRAINT fk_invoices_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL;