-- Create packages table
CREATE TABLE IF NOT EXISTS `packages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `monthly_limit` int(11) DEFAULT NULL,
  `weekly_limit` int(11) DEFAULT NULL,
  `validity_days` int(11) NOT NULL DEFAULT 30,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `packages_is_active_sort_order_index` (`is_active`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add package_id column to payments table
ALTER TABLE `payments` 
ADD COLUMN `package_id` bigint(20) UNSIGNED NULL AFTER `user_id`,
ADD CONSTRAINT `payments_package_id_foreign` 
FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE SET NULL;

-- Insert default packages
INSERT INTO `packages` (`name`, `description`, `type`, `price`, `monthly_limit`, `weekly_limit`, `validity_days`, `is_active`, `sort_order`, `created_at`, `updated_at`) VALUES
('Jedan čas', 'Rezervacija za jedan pilates čas', 'single_class', 1500.00, NULL, NULL, 30, 1, 1, NOW(), NOW()),
('Paket 2x nedeljno', 'Dva časa nedeljno, maksimalno 9 časova mesečno', 'package_2x_weekly', 12000.00, 9, 2, 30, 1, 2, NOW(), NOW()),
('Paket 3x nedeljno', 'Tri časa nedeljno, maksimalno 14 časova mesečno', 'package_3x_weekly', 18000.00, 14, 3, 30, 1, 3, NOW(), NOW());