SET NAMES utf8mb4;
START TRANSACTION;

CREATE TABLE IF NOT EXISTS invoice_issuer_settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, issuer_scope_key VARCHAR(80) NOT NULL,
 scope_type ENUM('unit','province') NOT NULL, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL,
 issuer_name VARCHAR(180) NOT NULL, issuer_address VARCHAR(1000) NOT NULL, issuer_email VARCHAR(254) NULL,
 registration_number VARCHAR(100) NULL, vat_treatment ENUM('not_registered','registered','outside_scope') NOT NULL DEFAULT 'not_registered',
 vat_number VARCHAR(80) NULL, default_vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
 invoice_prefix VARCHAR(30) NOT NULL DEFAULT 'INV-', invoice_padding TINYINT UNSIGNED NOT NULL DEFAULT 5, next_invoice_number BIGINT UNSIGNED NOT NULL DEFAULT 1,
 credit_prefix VARCHAR(30) NOT NULL DEFAULT 'CN-', credit_padding TINYINT UNSIGNED NOT NULL DEFAULT 5, next_credit_number BIGINT UNSIGNED NOT NULL DEFAULT 1,
 payment_terms_days SMALLINT UNSIGNED NOT NULL DEFAULT 14, payment_terms_text VARCHAR(1000) NULL, payment_instructions VARCHAR(2000) NULL,
 updated_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_invoice_issuer_scope(issuer_scope_key), FOREIGN KEY(province_id) REFERENCES provinces(id),
 FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(updated_by) REFERENCES users(id),
 CHECK(invoice_padding BETWEEN 1 AND 12), CHECK(credit_padding BETWEEN 1 AND 12), CHECK(default_vat_rate>=0 AND default_vat_rate<=100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MariaDB cannot rebuild a table while another table retains a foreign key to
-- it. Remove and restore the existing optional subscription link explicitly.
ALTER TABLE subscription_charges DROP FOREIGN KEY subscription_charges_ibfk_7;
ALTER TABLE finance_invoices
 DROP INDEX uq_invoice_number,
 ADD province_id BIGINT UNSIGNED NULL AFTER id,
 ADD scope_type ENUM('unit','province') NOT NULL DEFAULT 'unit' AFTER province_id,
 ADD issuer_scope_key VARCHAR(80) NULL AFTER scope_type,
 MODIFY unit_id BIGINT UNSIGNED NULL,
 MODIFY invoice_number VARCHAR(60) NULL,
 MODIFY person_id BIGINT UNSIGNED NULL,
 ADD booking_id BIGINT UNSIGNED NULL AFTER person_id,
 ADD recipient_name VARCHAR(220) NOT NULL DEFAULT '' AFTER booking_id,
 ADD recipient_email VARCHAR(254) NULL AFTER recipient_name,
 MODIFY issued_on DATE NULL,
 MODIFY due_on DATE NULL,
 MODIFY total DECIMAL(12,2) NOT NULL DEFAULT 0,
 MODIFY status ENUM('draft','issued','partly_paid','paid','voided','credited') NOT NULL DEFAULT 'draft',
 ADD currency_code CHAR(3) NOT NULL DEFAULT 'GBP' AFTER total,
 ADD replaces_invoice_id BIGINT UNSIGNED NULL AFTER currency_code,
 ADD issued_by BIGINT UNSIGNED NULL AFTER created_by,
 ADD issued_at DATETIME NULL AFTER issued_by,
 ADD voided_by BIGINT UNSIGNED NULL AFTER issued_at,
 ADD voided_at DATETIME NULL AFTER voided_by,
 ADD updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 ADD UNIQUE KEY uq_invoice_scope_number(issuer_scope_key,invoice_number),
 ADD KEY idx_invoice_unit(unit_id), ADD KEY idx_invoice_scope(issuer_scope_key,status,id), ADD KEY idx_invoice_booking(booking_id),
 ADD CONSTRAINT fk_invoice_province FOREIGN KEY(province_id) REFERENCES provinces(id),
 ADD CONSTRAINT fk_invoice_booking FOREIGN KEY(booking_id) REFERENCES bookings(id),
 ADD CONSTRAINT fk_invoice_issuer FOREIGN KEY(issued_by) REFERENCES users(id) ON DELETE SET NULL,
 ADD CONSTRAINT fk_invoice_voider FOREIGN KEY(voided_by) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE finance_invoices ADD CONSTRAINT fk_invoice_replacement FOREIGN KEY(replaces_invoice_id) REFERENCES finance_invoices(id);
ALTER TABLE subscription_charges ADD CONSTRAINT subscription_charges_ibfk_7 FOREIGN KEY(invoice_id) REFERENCES finance_invoices(id);

CREATE TABLE finance_invoice_lines (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, line_number SMALLINT UNSIGNED NOT NULL,
 description VARCHAR(500) NOT NULL, quantity DECIMAL(10,2) NOT NULL DEFAULT 1, unit_price DECIMAL(12,2) NOT NULL,
 vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0, net_amount DECIMAL(12,2) NOT NULL, vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 line_total DECIMAL(12,2) NOT NULL, ledger_entry_id BIGINT UNSIGNED NULL,
 source_type ENUM('booking_charge','subscription_charge','manual_charge') NOT NULL, source_id BIGINT UNSIGNED NULL,
 approved_by BIGINT UNSIGNED NULL, approved_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_invoice_line(invoice_id,line_number), KEY idx_invoice_line_ledger(ledger_entry_id),
 FOREIGN KEY(invoice_id) REFERENCES finance_invoices(id), FOREIGN KEY(ledger_entry_id) REFERENCES finance_ledger_entries(id),
 FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL,
 CHECK(quantity>0), CHECK(vat_rate>=0 AND vat_rate<=100), CHECK(line_total=net_amount+vat_amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_invoice_issue_snapshots (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, snapshot_cipher LONGTEXT NOT NULL,
 snapshot_hash CHAR(64) NOT NULL, issued_by BIGINT UNSIGNED NOT NULL, issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_invoice_issue_snapshot(invoice_id), FOREIGN KEY(invoice_id) REFERENCES finance_invoices(id), FOREIGN KEY(issued_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_invoice_payment_allocations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, payment_entry_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(12,2) NOT NULL, allocated_by BIGINT UNSIGNED NOT NULL, allocated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_invoice_payment(invoice_id,payment_entry_id), FOREIGN KEY(invoice_id) REFERENCES finance_invoices(id),
 FOREIGN KEY(payment_entry_id) REFERENCES finance_ledger_entries(id), FOREIGN KEY(allocated_by) REFERENCES users(id), CHECK(amount>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_credit_notes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, issuer_scope_key VARCHAR(80) NOT NULL,
 credit_number VARCHAR(60) NOT NULL, amount DECIMAL(12,2) NOT NULL, reason VARCHAR(500) NOT NULL,
 ledger_entry_id BIGINT UNSIGNED NOT NULL, snapshot_cipher LONGTEXT NOT NULL, snapshot_hash CHAR(64) NOT NULL,
 issued_by BIGINT UNSIGNED NOT NULL, issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_credit_scope_number(issuer_scope_key,credit_number), KEY idx_credit_invoice(invoice_id,id),
 FOREIGN KEY(invoice_id) REFERENCES finance_invoices(id), FOREIGN KEY(ledger_entry_id) REFERENCES finance_ledger_entries(id),
 FOREIGN KEY(issued_by) REFERENCES users(id), CHECK(amount>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE outbound_mail_queue ADD invoice_id BIGINT UNSIGNED NULL AFTER booking_id,
 ADD KEY idx_mail_invoice(invoice_id,status), ADD CONSTRAINT fk_mail_invoice FOREIGN KEY(invoice_id) REFERENCES finance_invoices(id) ON DELETE SET NULL;

CREATE TABLE finance_invoice_mailings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, mail_queue_id BIGINT UNSIGNED NOT NULL,
 recipient VARCHAR(254) NOT NULL, queued_by BIGINT UNSIGNED NOT NULL, queued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_invoice_mail_queue(mail_queue_id), FOREIGN KEY(invoice_id) REFERENCES finance_invoices(id),
 FOREIGN KEY(mail_queue_id) REFERENCES outbound_mail_queue(id), FOREIGN KEY(queued_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_invoice_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, event_key VARCHAR(50) NOT NULL,
 actor_id BIGINT UNSIGNED NOT NULL, details_json VARCHAR(1000) NULL, occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_invoice_event(invoice_id,id), FOREIGN KEY(invoice_id) REFERENCES finance_invoices(id), FOREIGN KEY(actor_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TRIGGER invoice_snapshot_no_update BEFORE UPDATE ON finance_invoice_issue_snapshots FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Issued invoice snapshots are immutable';
CREATE TRIGGER invoice_snapshot_no_delete BEFORE DELETE ON finance_invoice_issue_snapshots FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Issued invoice snapshots are immutable';
CREATE TRIGGER credit_note_no_update BEFORE UPDATE ON finance_credit_notes FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Credit notes are immutable';
CREATE TRIGGER credit_note_no_delete BEFORE DELETE ON finance_credit_notes FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Credit notes are immutable';

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202609280040','Versioned invoices issuer numbering payments and credit notes',SHA2('202609280040_invoices_v1',256),0);
COMMIT;