CREATE TABLE IF NOT EXISTS booking_sync_feeds (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    property_id INT UNSIGNED NOT NULL,
    source ENUM('booking', 'airbnb') NOT NULL DEFAULT 'booking',
    feed_url TEXT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_synced_at DATETIME NULL,
    last_status ENUM('never', 'success', 'error') NOT NULL DEFAULT 'never',
    last_message TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_booking_sync_property_source (property_id, source),
    CONSTRAINT fk_booking_sync_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE booking_sync_feeds
    ADD COLUMN IF NOT EXISTS source ENUM('booking', 'airbnb') NOT NULL DEFAULT 'booking' AFTER property_id;

ALTER TABLE booking_sync_feeds
    DROP INDEX IF EXISTS uq_booking_sync_property,
    ADD UNIQUE INDEX IF NOT EXISTS uq_booking_sync_property_source (property_id, source);

ALTER TABLE reservations
    ADD COLUMN IF NOT EXISTS external_source VARCHAR(40) NULL AFTER source,
    ADD COLUMN IF NOT EXISTS external_uid VARCHAR(190) NULL AFTER external_source,
    ADD INDEX IF NOT EXISTS idx_reservations_external (external_source, external_uid);

INSERT INTO app_settings (setting_key, setting_value)
VALUES ('booking_sync_token', '')
ON DUPLICATE KEY UPDATE setting_key = setting_key;