-- Beds24 POC — Schema MySQL
-- Tables prefixees beds24_ pour cohabiter avec le schema Property Manager existant
-- A executer une fois sur la DB OVH (comjkvokwoshahar) via phpMyAdmin ou setup_db.php

CREATE TABLE IF NOT EXISTS beds24_bookings (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    -- Identifiants Beds24
    beds24_id BIGINT UNSIGNED NOT NULL,
    beds24_property_id INT UNSIGNED NOT NULL,
    beds24_room_id INT UNSIGNED DEFAULT NULL,

    -- Status (new, confirmed, request, cancelled, black)
    status VARCHAR(32) NOT NULL,

    -- Source / canal (Booking, Airbnb, Expedia, direct, etc.)
    channel VARCHAR(64) DEFAULT NULL,           -- Beds24 champ "channel" normalise
    api_source VARCHAR(64) DEFAULT NULL,        -- Beds24 apiSource brut (ex: "Booking.com")
    api_source_id INT UNSIGNED DEFAULT NULL,    -- Beds24 apiSourceId brut
    api_reference VARCHAR(64) DEFAULT NULL,     -- numero de confirmation cote OTA (ex: Booking.com)
    referer VARCHAR(255) DEFAULT NULL,          -- Beds24 referer brut

    -- Dates
    arrival DATE NOT NULL,
    departure DATE NOT NULL,

    -- Occupation
    num_adults INT UNSIGNED DEFAULT NULL,
    num_children INT UNSIGNED DEFAULT NULL,

    -- Client
    guest_first_name VARCHAR(255) DEFAULT NULL,
    guest_last_name VARCHAR(255) DEFAULT NULL,
    guest_email VARCHAR(255) DEFAULT NULL,
    guest_phone VARCHAR(64) DEFAULT NULL,
    guest_country VARCHAR(8) DEFAULT NULL,
    guest_language VARCHAR(8) DEFAULT NULL,

    -- Argent
    price DECIMAL(10,2) DEFAULT NULL,
    deposit DECIMAL(10,2) DEFAULT NULL,
    currency VARCHAR(8) DEFAULT NULL,

    -- Horodatage Beds24
    booking_time DATETIME DEFAULT NULL,
    modified_time DATETIME DEFAULT NULL,
    cancel_time DATETIME DEFAULT NULL,

    -- Texte libre
    notes TEXT DEFAULT NULL,
    message TEXT DEFAULT NULL,

    -- Reponse Beds24 complete (filet de securite pour les champs qu'on n'a pas mappes)
    raw_json JSON NOT NULL,

    -- Horodatage local
    fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uniq_beds24_id (beds24_id),
    KEY idx_property (beds24_property_id),
    KEY idx_status (status),
    KEY idx_arrival (arrival),
    KEY idx_channel (channel),
    KEY idx_modified (modified_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Journal des synchronisations (utile pour debug + cron monitoring)
CREATE TABLE IF NOT EXISTS beds24_sync_log (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finished_at DATETIME DEFAULT NULL,
    bookings_fetched INT UNSIGNED DEFAULT 0,
    bookings_inserted INT UNSIGNED DEFAULT 0,
    bookings_updated INT UNSIGNED DEFAULT 0,
    bookings_unchanged INT UNSIGNED DEFAULT 0,
    error_message TEXT DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_started (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
