-- HOSTIVA V3.1 — Safe migrations (run on existing v3.0 databases)
-- Compatible with MySQL 5.7+

CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `token` VARCHAR(64) NOT NULL UNIQUE,
  `expires_at` DATETIME NOT NULL,
  `used_at` DATETIME DEFAULT NULL,
  `ip` VARCHAR(64) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_user_id` (`user_id`),
  KEY `idx_token` (`token`),
  KEY `idx_expires` (`expires_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_sessions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `session_id` VARCHAR(255) NOT NULL UNIQUE,
  `ip_address` VARCHAR(64) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `last_activity` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME DEFAULT NULL,
  KEY `idx_user_id` (`user_id`),
  KEY `idx_session_id` (`session_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `channel_connections` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT DEFAULT NULL,
  `provider` ENUM('airbnb','booking_com','vrbo','hospitable','guesty','direct','other') NOT NULL DEFAULT 'other',
  `name` VARCHAR(100) NOT NULL,
  `external_id` VARCHAR(100) DEFAULT NULL,
  `sync_enabled` TINYINT(1) DEFAULT 0,
  `last_sync_at` DATETIME DEFAULT NULL,
  `status` ENUM('active','inactive','error') DEFAULT 'inactive',
  `settings` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_channel_property` (`property_id`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `permissions` (`code`, `module`, `label`) VALUES
  ('channels.view', 'channels', 'View channel integrations'),
  ('channels.manage', 'channels', 'Manage channel integrations'),
  ('dashboard.view', 'dashboard', 'View dashboard'),
  ('bookings.manage', 'bookings', 'Manage bookings'),
  ('properties.manage', 'properties', 'Manage properties'),
  ('finance.manage', 'finance', 'Manage finance'),
  ('users.manage', 'users', 'Manage users'),
  ('tickets.manage', 'tickets', 'Manage tickets'),
  ('settings.manage', 'settings', 'Manage settings'),
  ('reports.view', 'reports', 'View reports')
ON DUPLICATE KEY UPDATE label=VALUES(label);

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, id FROM `permissions` WHERE `code` IN ('channels.view','channels.manage','dashboard.view');

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 2, id FROM `permissions` WHERE `code` IN ('channels.view','channels.manage','dashboard.view');

DELETE FROM `password_resets` WHERE `expires_at` < NOW() AND `used_at` IS NULL;
DELETE FROM `user_sessions` WHERE `expires_at` IS NOT NULL AND `expires_at` < NOW();
