-- Migration 005: Tagesabschluss + zusätzliche Indizes für Exporte und Reports
-- Idempotent: prüft per INFORMATION_SCHEMA, ob Tabelle/Indizes bereits existieren.

USE `pacim_saas`;

-- ---------------------------------------------------------------
-- daily_closings: persistierte Snapshots des Tagesabschlusses
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `daily_closings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `closing_date` DATE NOT NULL,
  `event_id` INT UNSIGNED DEFAULT NULL,
  `total_bookings` INT UNSIGNED NOT NULL DEFAULT 0,
  `checked_in_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `checked_out_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `open_payment_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `paid_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `open_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `cash_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `card_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `bank_transfer_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `other_amount` 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,
  `closed_by` VARCHAR(100) DEFAULT NULL,
  `closed_at` DATETIME DEFAULT NULL,
  `note` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `closing_date_event` (`closing_date`, `event_id`),
  FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @dbn = DATABASE();

-- ---------------------------------------------------------------
-- Zusätzliche Indizes für Tagesabrechnung und Reports
-- ---------------------------------------------------------------
SET @ix := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'bookings' AND INDEX_NAME = 'idx_bookings_travel_from');
SET @sql := IF(@ix = 0, 'CREATE INDEX idx_bookings_travel_from ON bookings (travel_from)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ix := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'bookings' AND INDEX_NAME = 'idx_bookings_travel_to');
SET @sql := IF(@ix = 0, 'CREATE INDEX idx_bookings_travel_to ON bookings (travel_to)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ix := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'bookings' AND INDEX_NAME = 'idx_bookings_payment_status');
SET @sql := IF(@ix = 0, 'CREATE INDEX idx_bookings_payment_status ON bookings (payment_status)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ix := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'payments' AND INDEX_NAME = 'idx_payments_method');
SET @sql := IF(@ix = 0, 'CREATE INDEX idx_payments_method ON payments (payment_method)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @ix := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'payments' AND INDEX_NAME = 'idx_payments_paid_at');
SET @sql := IF(@ix = 0, 'CREATE INDEX idx_payments_paid_at ON payments (paid_at)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Fertig.