-- ============================================================================
-- Migration: Create unified OTA to PMS matching table
-- Date: 2026-01-15
-- Author: System
-- Purpose: Central table to link all OTA transactions (Airbnb, Agoda, Booking,
--          Casitamx) to PMS reservations (Hostify, Cloudbeds)
-- ============================================================================

-- Drop table if exists (for testing - comment out for production)
-- DROP TABLE IF EXISTS ota_pms_match;

CREATE TABLE IF NOT EXISTS ota_pms_match (
    -- Primary Key
    match_id CHAR(36) NOT NULL PRIMARY KEY COMMENT 'UUID - unique match identifier',

    -- ========================================================================
    -- OTA Source (Where the money came from)
    -- ========================================================================
    ota_source ENUM('airbnb', 'agoda', 'booking', 'casitamx') NOT NULL
        COMMENT 'OTA platform name',

    ota_transaction_id CHAR(36) NOT NULL
        COMMENT 'FK to airbnb_transaction_id, agoda_transaction_id, etc.',

    ota_confirmation_code VARCHAR(100) NULL
        COMMENT 'Confirmation code from OTA (NULL for Casitamx)',

    ota_amount DECIMAL(10,2) NULL
        COMMENT 'Transaction amount from OTA',

    ota_check_in DATE NULL
        COMMENT 'Check-in date from OTA transaction',

    ota_check_out DATE NULL
        COMMENT 'Check-out date from OTA transaction',

    ota_guest_name VARCHAR(255) NULL
        COMMENT 'Guest name from OTA transaction',

    -- ========================================================================
    -- PMS Destination (Where the reservation is managed)
    -- ========================================================================
    pms_source ENUM('hostify', 'cloudbeds') NULL
        COMMENT 'PMS platform name - NULL if orphaned transaction',

    pms_reserva_id CHAR(36) NULL
        COMMENT 'FK to hostify_reserva_id or cloudbeds_reserva_id',

    pms_confirmation_code VARCHAR(100) NULL
        COMMENT 'Confirmation code from PMS (confirmation_code or reservation_number)',

    pms_check_in DATE NULL
        COMMENT 'Check-in date from PMS reservation',

    pms_check_out DATE NULL
        COMMENT 'Check-out date from PMS reservation',

    -- ========================================================================
    -- Matching Metadata
    -- ========================================================================
    match_type ENUM('automatic', 'manual', 'fuzzy') NOT NULL DEFAULT 'automatic'
        COMMENT 'How the match was created: automatic=code match, manual=user, fuzzy=name+dates',

    match_confidence DECIMAL(5,2) NULL
        COMMENT 'Match confidence percentage 0-100 (used for fuzzy matches)',

    match_method VARCHAR(100) NULL
        COMMENT 'Method used: confirmation_code, guest_name_dates, manual_link, etc.',

    matched_at TIMESTAMP NULL
        COMMENT 'When the match was created',

    matched_by VARCHAR(32) NULL
        COMMENT 'User ID who created the match (NULL for automatic)',

    -- ========================================================================
    -- Status & Notes
    -- ========================================================================
    status ENUM('active', 'disputed', 'void', 'verified') NOT NULL DEFAULT 'active'
        COMMENT 'active=normal, disputed=low confidence, void=deleted, verified=manually confirmed',

    notes TEXT NULL
        COMMENT 'Additional notes about the match',

    -- ========================================================================
    -- Standard Audit Fields
    -- ========================================================================
    alta_db TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        COMMENT 'Record creation timestamp',

    alta_por VARCHAR(32) DEFAULT 'system'
        COMMENT 'User who created the record',

    ultimo_cambio TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
        COMMENT 'Last modification timestamp',

    ultimo_cambio_por VARCHAR(32) NULL
        COMMENT 'User who last modified the record',

    -- ========================================================================
    -- Indexes for Performance
    -- ========================================================================
    KEY idx_ota_source_id (ota_source, ota_transaction_id)
        COMMENT 'Fast lookup by OTA platform + transaction',

    KEY idx_pms_source_id (pms_source, pms_reserva_id)
        COMMENT 'Fast lookup by PMS platform + reservation',

    KEY idx_ota_confirmation (ota_confirmation_code)
        COMMENT 'Search by OTA confirmation code',

    KEY idx_pms_confirmation (pms_confirmation_code)
        COMMENT 'Search by PMS confirmation code',

    KEY idx_dates (ota_check_in, ota_check_out)
        COMMENT 'Date range searches',

    KEY idx_status (status)
        COMMENT 'Filter by match status',

    KEY idx_match_type (match_type)
        COMMENT 'Filter by match type',

    KEY idx_matched_at (matched_at)
        COMMENT 'Recent matches timeline',

    -- ========================================================================
    -- Constraints
    -- ========================================================================
    UNIQUE KEY unique_ota_transaction (ota_source, ota_transaction_id)
        COMMENT 'One OTA transaction can only match to one PMS reservation'

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Unified OTA to PMS matching table - links all OTA transactions to PMS reservations';

-- ============================================================================
-- Verification Queries (run after migration)
-- ============================================================================

-- SELECT 'Table created successfully!' AS status;
-- SHOW CREATE TABLE ota_pms_match;
-- SELECT COUNT(*) as initial_count FROM ota_pms_match;
