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

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

USE `addicting_booking`;

-- ── ADMINS ──────────────────────────────────

CREATE TABLE `admins` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `role` ENUM('super_admin', 'admin') DEFAULT 'admin',
    `last_login` DATETIME NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── SETTINGS ────────────────────────────────

CREATE TABLE `settings` (
    `key` VARCHAR(100) PRIMARY KEY,
    `value` TEXT NOT NULL,
    `type` ENUM('string', 'int', 'bool', 'json') DEFAULT 'string',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── 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;

-- ── GOOGLE CALENDAR ─────────────────────────

CREATE TABLE `gcal_tokens` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `admin_id` INT UNSIGNED NOT NULL,
    `calendar_id` VARCHAR(255) NOT NULL,
    `access_token` TEXT NOT NULL,
    `refresh_token` TEXT NOT NULL,
    `token_expires_at` DATETIME NOT NULL,
    `sync_token` VARCHAR(255) NULL,
    `last_sync_at` DATETIME NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`admin_id`) REFERENCES `admins`(`id`) ON DELETE CASCADE
) 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 DATA
-- =============================================

-- Default admin (password: admin123 — CHANGE THIS!)
INSERT INTO `admins` (`email`, `password_hash`, `name`, `role`) VALUES
('admin@addicting.shop', '$2y$10$YourHashHere', 'Admin', 'super_admin');

-- Default settings
INSERT INTO `settings` (`key`, `value`, `type`) VALUES
('app_name', 'Addicting Warehouse Booking', 'string'),
('timezone', 'Europe/Rome', 'string'),
('booking_lead_time', '24', 'int'),
('booking_max_advance', '60', 'int'),
('cancellation_deadline', '24', 'int'),
('min_order_in_person', '500', 'int'),
('min_order_video', '300', 'int'),
('admin_email', 'info@addicting.shop', 'string'),
('confirmation_enabled', '1', 'bool'),
('reminder_enabled', '1', 'bool'),
('reminder_hours_before', '24', 'int'),
('gcal_sync_enabled', '0', 'bool'),
('brand_primary_color', '#1A1A1A', 'string'),
('brand_accent_color', '#C4A35A', 'string');

-- 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);
