-- =============================================
-- Addicting Booking System — Database Schema
-- =============================================

CREATE DATABASE IF NOT EXISTS `addicting_booking` 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

USE `addicting_booking`;

-- ── AVAILABILITY ────────────────────────────

CREATE TABLE `availability_templates` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `day_of_week` TINYINT NOT NULL COMMENT '0=Mon, 6=Sun',
    `start_time` TIME NOT NULL,
    `end_time` TIME NOT NULL,
    `slot_duration` INT NOT NULL DEFAULT 60 COMMENT 'minutes per slot',
    `max_bookings_per_slot` INT NOT NULL DEFAULT 1,
    `visit_type` ENUM('in_person', 'video', 'both') DEFAULT 'both',
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_day_active` (`day_of_week`, `is_active`)
) ENGINE=InnoDB;

CREATE TABLE `availability_overrides` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `date` DATE NOT NULL,
    `start_time` TIME NULL COMMENT 'NULL = full day',
    `end_time` TIME NULL,
    `is_available` BOOLEAN DEFAULT FALSE COMMENT 'FALSE=blocked, TRUE=extra availability',
    `reason` VARCHAR(255) NULL,
    `source` ENUM('manual', 'gcal') DEFAULT 'manual',
    `gcal_event_id` VARCHAR(255) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_date` (`date`),
    INDEX `idx_source` (`source`)
) ENGINE=InnoDB;

-- ── BOOKINGS ────────────────────────────────

CREATE TABLE `bookings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `uuid` CHAR(36) NOT NULL UNIQUE,
    
    -- Scheduling
    `visit_type` ENUM('in_person', 'video') NOT NULL,
    `booking_date` DATE NOT NULL,
    `booking_time` TIME NOT NULL,
    `duration` INT NOT NULL DEFAULT 60 COMMENT 'minutes',
    
    -- Customer
    `company_name` VARCHAR(255) NOT NULL,
    `contact_name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(50) NOT NULL,
    `notes` TEXT NULL,
    
    -- Status
    `status` ENUM('pending', 'confirmed', 'cancelled', 'completed', 'no_show') DEFAULT 'confirmed',
    `cancelled_at` DATETIME NULL,
    `cancellation_reason` VARCHAR(255) NULL,
    
    -- Google Calendar
    `gcal_event_id` VARCHAR(255) NULL,
    `gcal_synced_at` DATETIME NULL,
    
    -- Meta
    `ip_address` VARCHAR(45) NULL,
    `user_agent` VARCHAR(500) NULL,
    `source` ENUM('website', 'admin', 'embed') DEFAULT 'website',
    
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_date_status` (`booking_date`, `status`),
    INDEX `idx_email` (`email`),
    INDEX `idx_uuid` (`uuid`),
    INDEX `idx_gcal` (`gcal_event_id`)
) ENGINE=InnoDB;

CREATE TABLE `gcal_sync_log` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `direction` ENUM('push', 'pull') NOT NULL,
    `action` VARCHAR(50) NOT NULL,
    `booking_id` INT UNSIGNED NULL,
    `gcal_event_id` VARCHAR(255) NULL,
    `status` ENUM('success', 'error') NOT NULL,
    `error_message` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_created` (`created_at`),
    FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ── EMAIL LOG ───────────────────────────────

CREATE TABLE `email_log` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `booking_id` INT UNSIGNED NULL,
    `type` ENUM('confirmation', 'reminder', 'cancellation', 'admin_notification') NOT NULL,
    `recipient` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `status` ENUM('sent', 'failed') NOT NULL,
    `error_message` TEXT NULL,
    `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Default availability: Mon-Fri 09:00-17:00, 60min slots
INSERT INTO `availability_templates` (`day_of_week`, `start_time`, `end_time`, `slot_duration`) VALUES
(0, '09:00:00', '17:00:00', 60),
(1, '09:00:00', '17:00:00', 60),
(2, '09:00:00', '17:00:00', 60),
(3, '09:00:00', '17:00:00', 60),
(4, '09:00:00', '17:00:00', 60);
