-- Migration 003: Zahlungslogik, Rechnungspositionen, Dokumentenpfade
-- Idempotent: prüft per INFORMATION_SCHEMA, ob Spalten/Tabellen bereits existieren.

USE `pacim_saas`;

-- ---------------------------------------------------------------
-- invoice_items: Positionen einer Rechnung (aus booking_products generiert)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoice_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_id` INT UNSIGNED NOT NULL,
  `product_id` INT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(191) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `quantity` INT UNSIGNED NOT NULL DEFAULT 1,
  `price_net` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `vat_rate` DECIMAL(5,2) NOT NULL DEFAULT 19.00,
  `price_gross` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `total_net` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `total_vat` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `total_gross` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- invoices.html_path (Fallback-Pfad zur HTML-Datei)
-- ---------------------------------------------------------------
SET @dbn = DATABASE();

SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_SCHEMA = @dbn
                      AND TABLE_NAME = 'invoices'
                      AND COLUMN_NAME = 'html_path');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `invoices` ADD COLUMN `html_path` VARCHAR(255) DEFAULT NULL AFTER `pdf_path`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------
-- bookings.confirmation_path (Buchungsbestätigungs-HTML)
-- ---------------------------------------------------------------
SET @col_exists := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_SCHEMA = @dbn
                      AND TABLE_NAME = 'bookings'
                      AND COLUMN_NAME = 'confirmation_path');
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `bookings` ADD COLUMN `confirmation_path` VARCHAR(255) DEFAULT NULL AFTER `notes`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------
-- payments: sicherstellen, dass payment_method passende Werte erlaubt (TEXT-Feld, kein Constraint nötig)
-- Existierende Zahlungsmethoden werden bei der Anzeige normalisiert.
-- ---------------------------------------------------------------

-- Fertig.