-- ===================================================================
-- Migration 2: line-item invoices (description + price per item)
-- ===================================================================
-- Run this ONCE against your existing database (phpMyAdmin → SQL tab,
-- with your database selected in the left sidebar → paste → Go).
-- Nothing existing is deleted: old invoices keep their single
-- description as an overall note; new invoices will also have rows
-- in invoice_items.
-- ===================================================================

-- The old single description is now an optional overall note —
-- the real per-item detail lives in invoice_items below.
ALTER TABLE invoices MODIFY COLUMN description TEXT NULL;

CREATE TABLE IF NOT EXISTS invoice_items (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id   INT NOT NULL,
    description  VARCHAR(255) NOT NULL,
    quantity     DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_price   DECIMAL(12,2) NOT NULL,
    line_total   DECIMAL(12,2) NOT NULL,
    sort_order   INT NOT NULL DEFAULT 0,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;