-- ============================================================
-- Wix Booking Automation Database Schema
-- Version: 1.0
-- ============================================================

-- Table: bookings
-- Stores all booking records from Wix API
CREATE TABLE IF NOT EXISTS `bookings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `wix_booking_id` VARCHAR(64) NOT NULL UNIQUE COMMENT 'Unique booking ID from Wix',
    `wix_contact_id` VARCHAR(64) DEFAULT NULL COMMENT 'Contact/Member ID from Wix',
    `customer_name` VARCHAR(255) DEFAULT NULL,
    `customer_email` VARCHAR(255) DEFAULT NULL,
    `service_name` VARCHAR(255) DEFAULT NULL,
    `start_time` DATETIME NOT NULL COMMENT 'Booking start time in UTC',
    `end_time` DATETIME NOT NULL COMMENT 'Booking end time in UTC',
    `person_count` INT DEFAULT 1 COMMENT 'Number of participants',
    `booking_status` ENUM('UNKNOWN_STATUS','PENDING_CHECKOUT','RESERVED','PENDING','CONFIRMED','CANCELED','DECLINED','WAITING_LIST') DEFAULT 'CONFIRMED' COMMENT 'Wix booking status',
    `processing_status` ENUM('new','processing','code_generated','email_sent','completed','failed','retry_pending','pending_confirmation') DEFAULT 'new' COMMENT 'Internal processing state',
    `access_code` VARCHAR(32) DEFAULT NULL COMMENT 'Generated Codelocks NetCode with * prefix',
    `access_code_valid_from` DATETIME DEFAULT NULL COMMENT 'Code valid from timestamp',
    `access_code_valid_until` DATETIME DEFAULT NULL COMMENT 'Code expiry timestamp',
    `codelocks_raw_response` JSON DEFAULT NULL COMMENT 'Full Codelocks API response',
    `retry_count` TINYINT UNSIGNED DEFAULT 0 COMMENT 'Number of retry attempts',
    `last_error` TEXT DEFAULT NULL COMMENT 'Last error message if processing failed',
    `wix_updated_at` DATETIME DEFAULT NULL COMMENT 'Last updated timestamp from Wix API',
    `response_from_wix` JSON DEFAULT NULL COMMENT 'Original raw response from Wix API',
    `customer_email_response` JSON DEFAULT NULL COMMENT 'Response from Wix for customer email',
    `admin_email_response` JSON DEFAULT NULL COMMENT 'Response from Wix for admin email',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_processing_status` (`processing_status`),
    INDEX `idx_wix_updated_at` (`wix_updated_at`),
    INDEX `idx_booking_status` (`booking_status`),
    INDEX `idx_start_time` (`start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Main bookings table synchronized from Wix API';

-- Table: booking_custom_fields
-- Stores custom CMS collection variables attached to bookings
CREATE TABLE IF NOT EXISTS `booking_custom_fields` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `booking_id` INT UNSIGNED NOT NULL,
    `field_key` VARCHAR(128) NOT NULL COMMENT 'Custom field name from CMS collection',
    `field_value` TEXT DEFAULT NULL COMMENT 'Custom field value',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `uk_booking_field` (`booking_id`, `field_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Custom fields from Wix CMS collections';

-- Table: sync_state
-- Stores system state like last sync timestamp and cron lock
CREATE TABLE IF NOT EXISTS `sync_state` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key_name` VARCHAR(64) NOT NULL UNIQUE COMMENT 'Config key name',
    `value` TEXT DEFAULT NULL COMMENT 'Config value',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='System configuration and state tracking';

-- Initial sync state data
INSERT INTO `sync_state` (`key_name`, `value`) VALUES 
('last_sync_at', UTC_TIMESTAMP()),
('cron_lock', '0')
ON DUPLICATE KEY UPDATE `value` = `value`;

-- Table: processing_log
-- Detailed audit log of all operations
CREATE TABLE IF NOT EXISTS `processing_log` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `booking_id` INT UNSIGNED DEFAULT NULL COMMENT 'Reference to bookings.id',
    `wix_booking_id` VARCHAR(64) DEFAULT NULL COMMENT 'Wix booking ID for lookup',
    `action` VARCHAR(64) NOT NULL COMMENT 'Action type: fetch_bookings, generate_code, send_email, etc',
    `status` ENUM('success','error','warning','info') NOT NULL DEFAULT 'info',
    `message` TEXT DEFAULT NULL COMMENT 'Log message',
    `request_payload` JSON DEFAULT NULL COMMENT 'Request data (for API calls)',
    `response_payload` JSON DEFAULT NULL COMMENT 'Response data (for API calls)',
    `execution_time_ms` INT UNSIGNED DEFAULT NULL COMMENT 'Operation duration in milliseconds',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_booking_id` (`booking_id`),
    INDEX `idx_wix_booking_id` (`wix_booking_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Audit log for all booking processing operations';

-- ============================================================
-- Views for monitoring and debugging
-- ============================================================

-- View: pending_bookings
-- Shows all bookings that need processing or retry
CREATE OR REPLACE VIEW `pending_bookings` AS
SELECT 
    `id`,
    `wix_booking_id`,
    `customer_name`,
    `service_name`,
    `start_time`,
    `processing_status`,
    `retry_count`,
    `last_error`,
    `created_at`,
    `updated_at`
FROM `bookings`
WHERE `processing_status` IN ('new', 'processing', 'retry_pending')
ORDER BY `created_at` ASC;

-- View: failed_bookings
-- Shows bookings that exhausted retries
CREATE OR REPLACE VIEW `failed_bookings` AS
SELECT 
    `id`,
    `wix_booking_id`,
    `customer_name`,
    `service_name`,
    `start_time`,
    `retry_count`,
    `last_error`,
    `created_at`,
    `updated_at`
FROM `bookings`
WHERE `processing_status` = 'failed'
ORDER BY `updated_at` DESC;

-- View: recent_activity
-- Last 100 log entries for quick debugging
CREATE OR REPLACE VIEW `recent_activity` AS
SELECT 
    `id`,
    `action`,
    `status`,
    `message`,
    `wix_booking_id`,
    `created_at`
FROM `processing_log`
ORDER BY `created_at` DESC
LIMIT 100;