-- Venue Events Manager — Database update v1.000.001
-- Safe to run once on an existing installation (MySQL / MariaDB).
-- Import via phpMyAdmin → Import, or: mysql -u USER -p DATABASE < update_v1.000.001.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------------
-- 1) Package type: Global | Per Guest
-- ---------------------------------------------------------------------------
SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'packages'
      AND COLUMN_NAME = 'package_type'
);

SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE `packages` ADD COLUMN `package_type` VARCHAR(32) NOT NULL DEFAULT ''global'' AFTER `category`, ADD INDEX `packages_package_type_index` (`package_type`)',
    'SELECT ''packages.package_type already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Prefer Per Guest when most line items use per_guest / per_guest_per_day
UPDATE `packages` p
SET p.`package_type` = 'per_guest'
WHERE EXISTS (
    SELECT 1
    FROM `package_items` pi
    LEFT JOIN `products` pr ON pr.`id` = pi.`product_id`
    WHERE pi.`package_id` = p.`id`
    GROUP BY pi.`package_id`
    HAVING SUM(
        CASE
            WHEN COALESCE(pi.`charge_basis_override`, pr.`default_charge_basis`) IN ('per_guest', 'per_guest_per_day') THEN 1
            ELSE 0
        END
    ) >= GREATEST(COUNT(*), 1) / 2
)
AND (p.`package_type` IS NULL OR p.`package_type` = '' OR p.`package_type` = 'global');

UPDATE `packages`
SET `package_type` = 'global'
WHERE `package_type` IS NULL OR `package_type` = '';

-- ---------------------------------------------------------------------------
-- 2) Version settings
-- ---------------------------------------------------------------------------
INSERT INTO `settings` (`key`, `value`, `is_encrypted`, `created_at`, `updated_at`)
VALUES ('schema_version', '1.000.001', 0, NOW(), NOW())
ON DUPLICATE KEY UPDATE `value` = '1.000.001', `updated_at` = NOW();

INSERT INTO `settings` (`key`, `value`, `is_encrypted`, `created_at`, `updated_at`)
VALUES ('app_version', '1.000.001', 0, NOW(), NOW())
ON DUPLICATE KEY UPDATE `value` = '1.000.001', `updated_at` = NOW();

SET FOREIGN_KEY_CHECKS = 1;

SELECT 'Update v1.000.001 applied successfully' AS result;
