-- HOSTIVA V4 — STR Operating System (additive, safe on existing v3 DB)
-- Run: mysql -u root -p hostiva < database/migrations_002_v4_str_os.sql

CREATE TABLE IF NOT EXISTS `system_events` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `event_type` VARCHAR(80) NOT NULL,
  `entity_type` VARCHAR(50) DEFAULT NULL,
  `entity_id` INT DEFAULT NULL,
  `payload` JSON DEFAULT NULL,
  `user_id` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_evt_type` (`event_type`),
  KEY `idx_evt_created` (`created_at`),
  KEY `idx_evt_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `event_queue` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `queue` VARCHAR(50) DEFAULT 'default',
  `event_type` VARCHAR(80) NOT NULL,
  `payload` JSON NOT NULL,
  `status` ENUM('pending','processing','done','failed') DEFAULT 'pending',
  `attempts` INT DEFAULT 0,
  `max_attempts` INT DEFAULT 5,
  `next_retry_at` DATETIME DEFAULT NULL,
  `last_error` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `processed_at` DATETIME DEFAULT NULL,
  KEY `idx_eq_status` (`status`,`next_retry_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `notification_inbox` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `title` VARCHAR(190) NOT NULL,
  `body` TEXT,
  `link` VARCHAR(255) DEFAULT NULL,
  `category` VARCHAR(50) DEFAULT 'ops',
  `read_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_ni_user` (`user_id`,`read_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `calendar_blocks` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT NOT NULL,
  `block_type` ENUM('booking','maintenance','cleaning','owner_block','ota_block') NOT NULL,
  `ref_id` INT DEFAULT NULL,
  `title` VARCHAR(190) DEFAULT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `color` VARCHAR(20) DEFAULT NULL,
  `meta` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_cb_property_dates` (`property_id`,`start_date`,`end_date`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ota_connections` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `channel_id` INT DEFAULT NULL,
  `property_id` INT DEFAULT NULL,
  `provider` ENUM('airbnb','booking_com','vrbo','expedia','agoda','hospitable','guesty','other') NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `external_listing_id` VARCHAR(120) DEFAULT NULL,
  `oauth_access_token` TEXT,
  `oauth_refresh_token` TEXT,
  `token_expires_at` DATETIME DEFAULT NULL,
  `webhook_secret` VARCHAR(64) DEFAULT NULL,
  `sync_enabled` TINYINT(1) DEFAULT 0,
  `last_sync_at` DATETIME DEFAULT NULL,
  `status` ENUM('active','inactive','error','pending') DEFAULT 'pending',
  `settings` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_ota_provider` (`provider`),
  KEY `idx_ota_property` (`property_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ota_sync_queue` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ota_connection_id` INT NOT NULL,
  `direction` ENUM('inbound','outbound') NOT NULL,
  `action` VARCHAR(50) NOT NULL,
  `payload` JSON NOT NULL,
  `status` ENUM('pending','processing','done','failed') DEFAULT 'pending',
  `attempts` INT DEFAULT 0,
  `next_retry_at` DATETIME DEFAULT NULL,
  `last_error` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_osq_status` (`status`,`next_retry_at`),
  FOREIGN KEY (`ota_connection_id`) REFERENCES `ota_connections`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ota_sync_log` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `ota_connection_id` INT DEFAULT NULL,
  `booking_id` INT DEFAULT NULL,
  `direction` VARCHAR(20) DEFAULT NULL,
  `action` VARCHAR(50) NOT NULL,
  `status` ENUM('success','failed','conflict') NOT NULL,
  `message` TEXT,
  `payload` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_osl_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `message_threads` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `thread_type` ENUM('staff','guest','maintenance','ops') DEFAULT 'staff',
  `subject` VARCHAR(190) DEFAULT NULL,
  `property_id` INT DEFAULT NULL,
  `booking_id` INT DEFAULT NULL,
  `created_by` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_mt_type` (`thread_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `message_thread_users` (
  `thread_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `last_read_at` DATETIME DEFAULT 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;

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

CREATE TABLE IF NOT EXISTS `workflow_definitions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(120) NOT NULL,
  `trigger_event` VARCHAR(80) NOT NULL,
  `actions` JSON NOT NULL,
  `enabled` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `workflow_runs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `workflow_id` INT NOT NULL,
  `trigger_payload` JSON DEFAULT NULL,
  `status` ENUM('pending','done','failed') DEFAULT 'pending',
  `log` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`workflow_id`) REFERENCES `workflow_definitions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ai_insights` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `insight_type` VARCHAR(50) NOT NULL,
  `property_id` INT DEFAULT NULL,
  `title` VARCHAR(190) NOT NULL,
  `summary` TEXT NOT NULL,
  `score` DECIMAL(5,2) DEFAULT NULL,
  `meta` JSON DEFAULT NULL,
  `valid_until` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_ai_type` (`insight_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `webrtc_rooms` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `room_code` VARCHAR(32) NOT NULL UNIQUE,
  `title` VARCHAR(120) DEFAULT NULL,
  `host_user_id` INT DEFAULT NULL,
  `status` ENUM('open','ended') DEFAULT 'open',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_wr_code` (`room_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `owner_statements` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT NOT NULL,
  `period_start` DATE NOT NULL,
  `period_end` DATE NOT NULL,
  `gross_revenue` DECIMAL(12,2) DEFAULT 0,
  `expenses` DECIMAL(12,2) DEFAULT 0,
  `commission` DECIMAL(12,2) DEFAULT 0,
  `net_payout` DECIMAL(12,2) DEFAULT 0,
  `currency` VARCHAR(8) DEFAULT 'USD',
  `status` ENUM('draft','final') DEFAULT 'draft',
  `meta` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_os_property` (`property_id`,`period_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `permissions` (`code`, `module`, `label`) VALUES
  ('command.view', 'command', 'View command center'),
  ('calendar.view', 'calendar', 'View calendar'),
  ('calendar.manage', 'calendar', 'Manage calendar'),
  ('ota.view', 'ota', 'View OTA sync'),
  ('ota.manage', 'ota', 'Manage OTA connections'),
  ('inbox.view', 'inbox', 'View inbox & chat'),
  ('inbox.manage', 'inbox', 'Manage messaging'),
  ('automations.view', 'automations', 'View automations'),
  ('automations.manage', 'automations', 'Manage automations'),
  ('ai.view', 'ai', 'View AI insights'),
  ('finance.advanced', 'finance', 'Advanced finance & owner statements')
ON DUPLICATE KEY UPDATE label=VALUES(label);

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, id FROM `permissions` WHERE `code` LIKE 'command.%' OR `code` LIKE 'calendar.%' OR `code` LIKE 'ota.%' OR `code` LIKE 'inbox.%' OR `code` LIKE 'automations.%' OR `code` LIKE 'ai.%' OR `code`='finance.advanced';

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 2, id FROM `permissions` WHERE `code` IN ('command.view','calendar.view','calendar.manage','ota.view','ota.manage','inbox.view','inbox.manage','automations.view','ai.view','finance.advanced');

INSERT INTO `workflow_definitions` (`name`, `trigger_event`, `actions`, `enabled`) VALUES
  ('New booking → notify ops', 'booking.created', '[{"type":"notify","title":"New booking"},{"type":"queue_sync","direction":"outbound"}]', 1),
  ('Checkout → cleaning ticket', 'booking.checked_out', '[{"type":"create_ticket","category":"cleaning"}]', 1)
ON DUPLICATE KEY UPDATE name=VALUES(name);
