-- ============================================================
-- Hostiva Migration 004 — Guest Portal & Booking Fixes
-- Run this in phpMyAdmin > SQL tab (select your hostiva DB first)
-- Safe to run multiple times (uses IF NOT EXISTS / IF EXISTS)
-- ============================================================

-- ── 1. bookings table: add guest_user_id (fixes admin booking POST crash) ──
ALTER TABLE `bookings`
  ADD COLUMN IF NOT EXISTS `guest_user_id` INT DEFAULT NULL AFTER `guest_id`,
  ADD COLUMN IF NOT EXISTS `guest_hash`    VARCHAR(64) DEFAULT NULL AFTER `guest_user_id`,
  ADD COLUMN IF NOT EXISTS `adults`        TINYINT DEFAULT 1 AFTER `guests_count`,
  ADD COLUMN IF NOT EXISTS `children`      TINYINT DEFAULT 0 AFTER `adults`;

-- Index for fast hash lookups (guest portal link)
ALTER TABLE `bookings`
  ADD INDEX IF NOT EXISTS `idx_guest_hash` (`guest_hash`);

-- Foreign key for guest_user_id (only if users table exists — safe)
SET @fk_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'bookings'
    AND CONSTRAINT_NAME = 'fk_booking_guest_user'
);
SET @sql = IF(@fk_exists = 0,
  'ALTER TABLE `bookings` ADD CONSTRAINT `fk_booking_guest_user` FOREIGN KEY (`guest_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. properties table: add guest portal display fields ──
ALTER TABLE `properties`
  ADD COLUMN IF NOT EXISTS `wifi_network`      VARCHAR(100) DEFAULT NULL AFTER `description`,
  ADD COLUMN IF NOT EXISTS `wifi_password`     VARCHAR(100) DEFAULT NULL AFTER `wifi_network`,
  ADD COLUMN IF NOT EXISTS `google_review_url` VARCHAR(500) DEFAULT NULL AFTER `wifi_password`,
  ADD COLUMN IF NOT EXISTS `airbnb_url`        VARCHAR(500) DEFAULT NULL AFTER `google_review_url`,
  ADD COLUMN IF NOT EXISTS `booking_url`       VARCHAR(500) DEFAULT NULL AFTER `airbnb_url`,
  ADD COLUMN IF NOT EXISTS `whatsapp_number`   VARCHAR(40)  DEFAULT NULL AFTER `booking_url`;

-- ── 3. Inbox tables: message_threads, messages, message_thread_users ──
CREATE TABLE IF NOT EXISTS `message_threads` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `subject`     VARCHAR(255) NOT NULL DEFAULT '(No Subject)',
  `created_by`  INT DEFAULT NULL,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_thread_created` (`created_at`),
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `messages` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `thread_id`  INT NOT NULL,
  `user_id`    INT DEFAULT NULL,
  `body`       TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_msg_thread` (`thread_id`),
  FOREIGN KEY (`thread_id`) REFERENCES `message_threads`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`)   REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `message_thread_users` (
  `thread_id` INT NOT NULL,
  `user_id`   INT NOT NULL,
  PRIMARY KEY (`thread_id`, `user_id`),
  FOREIGN KEY (`thread_id`) REFERENCES `message_threads`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`)   REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 4. Auto-generate guest_hash for any existing confirmed/checked_in bookings ──
UPDATE `bookings`
SET `guest_hash` = LOWER(HEX(RANDOM_BYTES(32)))
WHERE `guest_hash` IS NULL
  AND `status` IN ('confirmed', 'checked_in');

-- ── Done ──
SELECT 'Migration 004 applied successfully.' AS result;
