-- Migration 006: Authentifizierung, Rollen, Audit-Log, Settings
-- Idempotent: prüft per INFORMATION_SCHEMA, ob Spalten existieren.

USE `pacim_saas`;
SET @dbn = DATABASE();

-- ---------------------------------------------------------------
-- users: zusätzliche Felder
-- ---------------------------------------------------------------
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'users' AND COLUMN_NAME = 'last_login_at');
SET @sql := IF(@c = 0, 'ALTER TABLE users ADD COLUMN last_login_at DATETIME DEFAULT NULL AFTER status', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'users' AND COLUMN_NAME = 'failed_login_count');
SET @sql := IF(@c = 0, 'ALTER TABLE users ADD COLUMN failed_login_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER last_login_at', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'users' AND COLUMN_NAME = 'remember_token');
SET @sql := IF(@c = 0, 'ALTER TABLE users ADD COLUMN remember_token VARCHAR(100) DEFAULT NULL AFTER failed_login_count', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Default-Admin sicherstellen (Passwort: "admin")
INSERT INTO users (email, password, first_name, last_name, role, status)
SELECT 'admin@pacim.local',
       '$2y$10$UA0PwEqycFDRJT2TApiuYuAv6Eqbghf0MvMqVxHShss2VfrM3NLXW',
       'Admin', 'PaCIM', 'admin', 'active'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'admin@pacim.local');

-- Bestehenden Admin auf bekannten Hash updaten (für lokale Entwicklung)
UPDATE users
   SET password = '$2y$10$UA0PwEqycFDRJT2TApiuYuAv6Eqbghf0MvMqVxHShss2VfrM3NLXW',
       role    = 'admin',
       status  = 'active'
 WHERE email = 'admin@pacim.local';

-- Demo-User für die übrigen Rollen (Passwort jeweils "admin")
INSERT INTO users (email, password, first_name, last_name, role, status) VALUES
('manager@pacim.local',    '$2y$10$UA0PwEqycFDRJT2TApiuYuAv6Eqbghf0MvMqVxHShss2VfrM3NLXW', 'Marie',    'Manager',    'manager',    'active'),
('checkin@pacim.local',    '$2y$10$UA0PwEqycFDRJT2TApiuYuAv6Eqbghf0MvMqVxHShss2VfrM3NLXW', 'Carla',    'Checkin',    'checkin',    'active'),
('accounting@pacim.local', '$2y$10$UA0PwEqycFDRJT2TApiuYuAv6Eqbghf0MvMqVxHShss2VfrM3NLXW', 'Alex',     'Accounting', 'accounting', 'active')
ON DUPLICATE KEY UPDATE
   password = VALUES(password),
   role     = VALUES(role),
   status   = VALUES(status);

-- ---------------------------------------------------------------
-- history_log: Erweiterung zum echten Audit-Log
-- ---------------------------------------------------------------
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'history_log' AND COLUMN_NAME = 'user_id');
SET @sql := IF(@c = 0, 'ALTER TABLE history_log ADD COLUMN user_id INT UNSIGNED DEFAULT NULL AFTER entity_id', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'history_log' AND COLUMN_NAME = 'old_values');
SET @sql := IF(@c = 0, 'ALTER TABLE history_log ADD COLUMN old_values JSON DEFAULT NULL AFTER message', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'history_log' AND COLUMN_NAME = 'new_values');
SET @sql := IF(@c = 0, 'ALTER TABLE history_log ADD COLUMN new_values JSON DEFAULT NULL AFTER old_values', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'history_log' AND COLUMN_NAME = 'ip_address');
SET @sql := IF(@c = 0, 'ALTER TABLE history_log ADD COLUMN ip_address VARCHAR(45) DEFAULT NULL AFTER new_values', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'history_log' AND COLUMN_NAME = 'user_agent');
SET @sql := IF(@c = 0, 'ALTER TABLE history_log ADD COLUMN user_agent VARCHAR(255) DEFAULT NULL AFTER ip_address', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Index für User-Filter
SET @ix := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'history_log' AND INDEX_NAME = 'idx_history_user');
SET @sql := IF(@ix = 0, 'CREATE INDEX idx_history_user ON history_log (user_id)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---------------------------------------------------------------
-- settings: Default-Keys für Firma, Rechnung, Mail, PDF, Check-in
-- ---------------------------------------------------------------
INSERT INTO settings (config_key, config_value) VALUES
('company_name',     'PaCIM Cruise Parking'),
('company_street',   'Hafenstraße 1'),
('company_zip',      '20457'),
('company_city',     'Hamburg'),
('company_phone',    '+49 40 0000000'),
('company_email',    'service@pacim.local'),
('company_website',  'https://pacim.local'),
('company_vat_id',   'DE000000000'),
('invoice_prefix',   'RE'),
('booking_prefix',   '#'),
('default_vat',      '19.00'),
('logo_path',        ''),
('smtp_host',        ''),
('smtp_port',        '587'),
('smtp_user',        ''),
('smtp_password',    ''),
('smtp_encryption',  'tls'),
('mail_from_email',  'service@pacim.local'),
('mail_from_name',   'PaCIM Cruise Parking'),
('checkin_lock_unpaid', '1')
ON DUPLICATE KEY UPDATE config_key = VALUES(config_key);

-- Fertig.