-- credit notes (storno): own number series; one per refund (automatic) or per correction / credit by staff
CREATE TABLE credit_notes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  number VARCHAR(32) NOT NULL,
  invoice_id INT UNSIGNED NOT NULL,
  client_id INT UNSIGNED NOT NULL,
  date DATE NOT NULL,
  description VARCHAR(255) NOT NULL,
  subtotal DECIMAL(16,2) NOT NULL,
  tax DECIMAL(16,2) NOT NULL DEFAULT 0,
  total DECIMAL(16,2) NOT NULL,
  currency_id INT UNSIGNED NOT NULL DEFAULT 1,
  settlement VARCHAR(10) NOT NULL COMMENT 'refund|credit|none',
  transaction_id VARCHAR(190) NOT NULL DEFAULT '' COMMENT 'refund transaction',
  admin_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY number (number),
  KEY invoice (invoice_id),
  KEY client (client_id),
  KEY date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO settings (k, v) VALUES ('credit_note_next_number', '1'), ('credit_note_number_format', 'CN-{year}-{n}');