-- HOSTIVA V5 — Elite STR Ecosystem (additive on V3/V4)
-- mysql -u root -p hostiva < database/migrations_003_v5_ecosystem.sql

INSERT INTO `roles` (`name`,`label`,`description`,`is_system`) VALUES
  ('owner','Property Owner','Owner/client portal access',1)
ON DUPLICATE KEY UPDATE label=VALUES(label);

INSERT INTO `permissions` (`code`, `module`, `label`) VALUES
  ('owner.portal', 'owner', 'Access owner portal'),
  ('owner.finance', 'owner', 'View owner financials'),
  ('owner.bookings', 'owner', 'View owner bookings'),
  ('owner.messages', 'owner', 'Owner messaging'),
  ('owner.support', 'owner', 'Owner support tickets'),
  ('jobs.run', 'system', 'Run background jobs'),
  ('automations.builder', 'automations', 'Edit automation workflows')
ON DUPLICATE KEY UPDATE label=VALUES(label);

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.name='super_admin' AND p.code IN ('owner.portal','owner.finance','owner.bookings','owner.messages','owner.support','jobs.run','automations.builder');

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.name='owner' AND p.code IN ('owner.portal','owner.finance','owner.bookings','owner.messages','owner.support');

CREATE TABLE IF NOT EXISTS `property_owners` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `commission_pct` DECIMAL(5,2) DEFAULT 15.00,
  `payout_email` VARCHAR(190) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_prop_user` (`property_id`,`user_id`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `owner_announcements` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(190) NOT NULL,
  `body` TEXT NOT NULL,
  `created_by` INT DEFAULT NULL,
  `published_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_oa_published` (`published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `owner_support_tickets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `property_id` INT DEFAULT NULL,
  `subject` VARCHAR(190) NOT NULL,
  `body` TEXT,
  `status` ENUM('open','in_progress','resolved','closed') DEFAULT 'open',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `reservation_locks` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT NOT NULL,
  `check_in` DATE NOT NULL,
  `check_out` DATE NOT NULL,
  `lock_token` VARCHAR(64) NOT NULL UNIQUE,
  `source` VARCHAR(40) DEFAULT 'direct',
  `booking_id` INT DEFAULT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_rl_property_dates` (`property_id`,`check_in`,`check_out`),
  KEY `idx_rl_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `availability_cache` (
  `property_id` INT NOT NULL PRIMARY KEY,
  `snapshot` JSON NOT NULL,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ota_health` (
  `ota_connection_id` INT NOT NULL PRIMARY KEY,
  `status` ENUM('healthy','degraded','down') DEFAULT 'healthy',
  `last_success_at` DATETIME DEFAULT NULL,
  `last_error` TEXT,
  `fail_count` INT DEFAULT 0,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `background_jobs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `job_type` VARCHAR(60) NOT NULL,
  `payload` JSON NOT NULL,
  `status` ENUM('pending','running','done','failed') DEFAULT 'pending',
  `attempts` INT DEFAULT 0,
  `scheduled_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `started_at` DATETIME DEFAULT NULL,
  `finished_at` DATETIME DEFAULT NULL,
  `last_error` TEXT,
  KEY `idx_bj_status` (`status`,`scheduled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `pricing_suggestions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT NOT NULL,
  `suggested_rate` DECIMAL(10,2) NOT NULL,
  `reason` VARCHAR(255) DEFAULT NULL,
  `confidence` DECIMAL(5,2) DEFAULT NULL,
  `valid_date` DATE NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_ps_property` (`property_id`,`valid_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(190) NOT NULL,
  `ip` VARCHAR(64) DEFAULT NULL,
  `success` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_la_email` (`email`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- webhook_verified: add manually in phpMyAdmin if column missing on ota_connections
