Complete Guide: Design & Build a Professional Booking System Database 2025

-- Enhanced services table with JSON configuration and generated columns
CREATE TABLE services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    category ENUM('interior', 'interior_exterior', 'exterior', 'dashcam', 'maintenance') NOT NULL,
    
    -- Time and pricing configuration
    base_duration_minutes INT NOT NULL,
    buffer_time_minutes INT NOT NULL DEFAULT 15,
    base_price DECIMAL(10,2) NOT NULL,
    
    -- Dynamic pricing configuration (JSON)
    pricing_config JSON NOT NULL DEFAULT '{}',
    
    -- Service requirements and constraints (JSON)
    requirements JSON NOT NULL DEFAULT '{}',
    
    -- Staff and resource requirements (JSON)
    resource_requirements JSON NOT NULL DEFAULT '{}',
    
    -- Business rules and constraints (JSON)
    business_rules JSON NOT NULL DEFAULT '{}',
    
    description TEXT,
    instructions TEXT,
    vehicle_compatibility JSON NOT NULL DEFAULT '["universal"]',
    
    -- Generated columns for efficient querying
    min_price DECIMAL(10,2) GENERATED ALWAYS AS (
        CAST(JSON_UNQUOTE(JSON_EXTRACT(pricing_config, '$.min_price')) AS DECIMAL(10,2))
    ) STORED,
    max_price DECIMAL(10,2) GENERATED ALWAYS AS (
        CAST(JSON_UNQUOTE(JSON_EXTRACT(pricing_config, '$.max_price')) AS DECIMAL(10,2))
    ) STORED,
    requires_indoor BOOLEAN GENERATED ALWAYS AS (
        JSON_EXTRACT(requirements, '$.indoor_required') = true
    ) STORED,
    
    -- Standard fields
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes for performance
    INDEX idx_category_active (category, is_active),
    INDEX idx_pricing (min_price, max_price),
    INDEX idx_duration (base_duration_minutes),
    INDEX idx_slug (slug),
    FULLTEXT idx_search (title, description)
);

-- Create sub_services table for add-on services
CREATE TABLE sub_services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    time_minutes INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    
    -- Configuration and requirements (JSON)
    requirements JSON NOT NULL DEFAULT '{}',
    pricing_modifiers JSON NOT NULL DEFAULT '{}',
    
    -- Compatibility and restrictions
    service_compatibility JSON NOT NULL DEFAULT '[]',
    vehicle_restrictions JSON NOT NULL DEFAULT '[]',
    
    -- Business configuration
    max_quantity INT DEFAULT 1,
    requires_approval BOOLEAN DEFAULT FALSE,
    
    is_active BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_active_order (is_active, display_order),
    INDEX idx_price (price),
    FULLTEXT idx_search (title, description)
);

-- Create service_sub_services junction table
CREATE TABLE service_sub_services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT NOT NULL,
    sub_service_id INT NOT NULL,
    
    -- Relationship configuration
    is_recommended BOOLEAN DEFAULT FALSE,
    is_required BOOLEAN DEFAULT FALSE,
    display_order INT DEFAULT 0,
    
    -- Pricing modifications for this combination
    price_modifier_type ENUM('none', 'discount', 'premium', 'fixed') DEFAULT 'none',
    price_modifier_value DECIMAL(10,2) DEFAULT 0.00,
    
    -- Time modifications
    time_modifier_minutes INT DEFAULT 0,
    
    -- Constraints and validations
    max_quantity INT DEFAULT 1,
    requires_conditions JSON DEFAULT '{}',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
    FOREIGN KEY (sub_service_id) REFERENCES sub_services(id) ON DELETE CASCADE,
    UNIQUE KEY unique_service_sub (service_id, sub_service_id),
    
    INDEX idx_service_recommended (service_id, is_recommended),
    INDEX idx_service_required (service_id, is_required)
);

-- Enhanced bookings table with comprehensive state management
CREATE TABLE bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    booking_number VARCHAR(20) NOT NULL UNIQUE,
    
    -- Customer information (can be guest or registered user)
    customer_id INT NULL,
    guest_customer_data JSON NULL,
    
    -- Contact details (always required)
    customer_name VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(20) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    
    -- Service location details
    service_location JSON NOT NULL,
    special_instructions TEXT,
    
    -- Scheduling information
    booking_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    timezone VARCHAR(50) DEFAULT 'UTC',
    
    -- Staff assignment
    primary_staff_id INT NULL,
    additional_staff JSON DEFAULT '[]',
    
    -- Service details
    services_json JSON NOT NULL COMMENT 'Selected services and sub-services',
    vehicle_details JSON NOT NULL,
    
    -- Pricing and payment
    subtotal DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) DEFAULT 0.00,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL,
    payment_status ENUM('pending', 'partial', 'paid', 'refunded') DEFAULT 'pending',
    
    -- Status and workflow
    status ENUM('draft', 'pending', 'confirmed', 'in_progress', 'completed', 'cancelled', 'no_show') 
           DEFAULT 'pending',
    status_history JSON DEFAULT '[]',
    
    -- Cancellation and modifications
    cancellation_reason TEXT NULL,
    cancelled_at TIMESTAMP NULL,
    cancelled_by INT NULL,
    can_reschedule BOOLEAN DEFAULT TRUE,
    modification_count INT DEFAULT 0,
    
    -- Communication and notifications
    notifications_sent JSON DEFAULT '[]',
    customer_notes TEXT,
    internal_notes TEXT,
    
    -- Quality and feedback
    completion_photos JSON DEFAULT '[]',
    customer_rating TINYINT NULL CHECK (customer_rating BETWEEN 1 AND 5),
    customer_feedback TEXT,
    quality_score DECIMAL(3,2) NULL,
    
    -- Timestamps and audit
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    confirmed_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    
    -- Foreign key constraints
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (primary_staff_id) REFERENCES staff(id) ON DELETE SET NULL,
    
    -- Indexes for performance
    INDEX idx_booking_date_status (booking_date, status),
    INDEX idx_customer_email (customer_email),
    INDEX idx_booking_number (booking_number),
    INDEX idx_staff_date (primary_staff_id, booking_date),
    INDEX idx_status_date (status, booking_date),
    INDEX idx_payment_status (payment_status),
    
    -- Constraint to ensure end_time > start_time
    CONSTRAINT chk_booking_time_valid CHECK (start_time < end_time),
    CONSTRAINT chk_booking_date_future CHECK (booking_date >= CURDATE() OR status IN ('completed', 'cancelled'))
);

-- Function to check booking conflicts with advanced logic
DELIMITER $$
CREATE FUNCTION check_booking_conflict(
    check_date DATE,
    check_start_time TIME,
    check_end_time TIME,
    required_staff_ids JSON,
    exclude_booking_id INT
) RETURNS JSON
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE conflict_result JSON DEFAULT '{"has_conflict": false, "conflicts": []}';
    DECLARE conflict_list JSON DEFAULT '[]';
    DECLARE staff_conflict JSON;
    DECLARE time_conflict JSON;
    
    -- Check for staff conflicts
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'type', 'staff_conflict',
            'staff_id', b.primary_staff_id,
            'booking_id', b.id,
            'booking_time', CONCAT(b.start_time, '-', b.end_time),
            'status', b.status
        )
    ) INTO staff_conflict
    FROM bookings b
    WHERE b.booking_date = check_date
      AND b.status IN ('confirmed', 'in_progress')
      AND (b.id != exclude_booking_id OR exclude_booking_id IS NULL)
      AND JSON_CONTAINS(required_staff_ids, CAST(b.primary_staff_id AS JSON))
      AND (
          (check_start_time >= b.start_time AND check_start_time < b.end_time)
          OR (check_end_time > b.start_time AND check_end_time <= b.end_time)
          OR (check_start_time <= b.start_time AND check_end_time >= b.end_time)
      );
    
    -- Check for time slot availability
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'type', 'time_conflict',
            'booking_id', b.id,
            'overlap_start', GREATEST(check_start_time, b.start_time),
            'overlap_end', LEAST(check_end_time, b.end_time),
            'customer', b.customer_name
        )
    ) INTO time_conflict
    FROM bookings b
    WHERE b.booking_date = check_date
      AND b.status IN ('confirmed', 'in_progress')
      AND (b.id != exclude_booking_id OR exclude_booking_id IS NULL)
      AND (
          (check_start_time < b.end_time AND check_end_time > b.start_time)
      );
    
    -- Combine conflicts
    IF staff_conflict IS NOT NULL OR time_conflict IS NOT NULL THEN
        SET conflict_list = JSON_MERGE_PRESERVE(
            IFNULL(staff_conflict, JSON_ARRAY()),
            IFNULL(time_conflict, JSON_ARRAY())
        );
        SET conflict_result = JSON_SET(conflict_result, '$.has_conflict', true, '$.conflicts', conflict_list);
    END IF;
    
    RETURN conflict_result;
END$$
DELIMITER ;

-- Enhanced time slot configuration with dynamic scheduling
CREATE TABLE time_slot_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Time period definition
    day_of_week TINYINT NOT NULL COMMENT '1=Monday, 2=Tuesday, ..., 7=Sunday',
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    
    -- Slot configuration
    slot_duration_minutes INT NOT NULL DEFAULT 60,
    buffer_time_minutes INT NOT NULL DEFAULT 15,
    
    -- Capacity and resource management
    max_concurrent_bookings INT DEFAULT 1,
    max_staff_required INT DEFAULT 1,
    
    -- Pricing and availability modifiers
    price_modifier_percentage DECIMAL(5,2) DEFAULT 0.00,
    availability_priority TINYINT DEFAULT 5 COMMENT '1=lowest, 10=highest',
    
    -- Seasonal and conditional rules
    effective_date_start DATE DEFAULT '2000-01-01',
    effective_date_end DATE DEFAULT '2099-12-31',
    seasonal_rules JSON DEFAULT '{}',
    
    -- Staff and service restrictions
    allowed_staff_ids JSON DEFAULT '[]',
    allowed_service_categories JSON DEFAULT '[]',
    restricted_service_ids JSON DEFAULT '[]',
    
    -- Business rules
    requires_advance_booking_hours INT DEFAULT 24,
    allows_walk_ins BOOLEAN DEFAULT FALSE,
    
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_day_time_period (day_of_week, start_time, end_time, effective_date_start),
    INDEX idx_day_active (day_of_week, is_active),
    INDEX idx_effective_dates (effective_date_start, effective_date_end)
);

-- Enhanced unavailable slots with detailed reason tracking
CREATE TABLE unavailable_slots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Time period definition
    date DATE NOT NULL,
    start_time TIME NULL COMMENT 'NULL for all-day unavailability',
    end_time TIME NULL COMMENT 'NULL for all-day unavailability',
    is_all_day BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Scope and targeting
    affects_all_staff BOOLEAN DEFAULT TRUE,
    affected_staff_ids JSON DEFAULT '[]',
    affected_service_categories JSON DEFAULT '[]',
    
    -- Reason and classification
    reason_category ENUM(
        'maintenance', 'holiday', 'training', 'emergency', 
        'staff_unavailable', 'equipment_issue', 'weather', 'other'
    ) NOT NULL,
    reason_description TEXT,
    
    -- Recurrence support
    is_recurring BOOLEAN DEFAULT FALSE,
    recurrence_pattern JSON DEFAULT '{}',
    recurrence_end_date DATE NULL,
    parent_unavailable_id INT NULL,
    
    -- Impact and alternatives
    allows_rescheduling BOOLEAN DEFAULT TRUE,
    alternative_suggestions JSON DEFAULT '[]',
    compensation_offered JSON DEFAULT '{}',
    
    -- Workflow and approval
    status ENUM('pending', 'approved', 'active', 'expired') DEFAULT 'pending',
    created_by INT NOT NULL,
    approved_by INT NULL,
    approved_at TIMESTAMP NULL,
    
    -- Communication
    customer_notification_sent BOOLEAN DEFAULT FALSE,
    staff_notification_sent BOOLEAN DEFAULT FALSE,
    notification_details JSON DEFAULT '{}',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (parent_unavailable_id) REFERENCES unavailable_slots(id) ON DELETE CASCADE,
    
    INDEX idx_date_status (date, status),
    INDEX idx_date_time (date, start_time, end_time),
    INDEX idx_category_date (reason_category, date),
    INDEX idx_recurring (is_recurring, recurrence_end_date),
    INDEX idx_staff_date (affected_staff_ids(100), date)
);

-- Create functional indexes on JSON fields for better performance
ALTER TABLE services 
ADD INDEX idx_json_vehicle_compatibility ((CAST(vehicle_compatibility AS CHAR(255) ARRAY))),
ADD INDEX idx_json_pricing_range ((
    CAST(JSON_UNQUOTE(JSON_EXTRACT(pricing_config, '$.min_price')) AS DECIMAL(10,2)),
    CAST(JSON_UNQUOTE(JSON_EXTRACT(pricing_config, '$.max_price')) AS DECIMAL(10,2))
)),
ADD INDEX idx_json_requirements ((
    CAST(JSON_UNQUOTE(JSON_EXTRACT(requirements, '$.indoor_required')) AS UNSIGNED)
));

-- Create optimized views for common JSON queries
CREATE VIEW service_pricing_view AS
SELECT 
    s.id,
    s.title,
    s.category,
    s.base_price,
    JSON_UNQUOTE(JSON_EXTRACT(s.pricing_config, '$.min_price')) as min_price,
    JSON_UNQUOTE(JSON_EXTRACT(s.pricing_config, '$.max_price')) as max_price,
    JSON_UNQUOTE(JSON_EXTRACT(s.pricing_config, '$.peak_hour_multiplier')) as peak_multiplier,
    JSON_UNQUOTE(JSON_EXTRACT(s.requirements, '$.indoor_required')) as requires_indoor,
    JSON_EXTRACT(s.vehicle_compatibility, '$') as compatible_vehicles,
    s.is_active
FROM services s
WHERE s.is_active = 1;

-- Optimized stored procedure for complex JSON queries
DELIMITER $$
CREATE PROCEDURE get_compatible_services(
    IN p_vehicle_type VARCHAR(50),
    IN p_indoor_available BOOLEAN,
    IN p_max_budget DECIMAL(10,2),
    IN p_service_date DATE
)
BEGIN
    SELECT 
        spv.id,
        spv.title,
        spv.category,
        spv.base_price,
        spv.min_price,
        spv.max_price,
        -- Calculate dynamic pricing based on date
        CASE 
            WHEN DAYOFWEEK(p_service_date) IN (1,7) THEN spv.base_price * 1.1
            ELSE spv.base_price
        END as calculated_price,
        spv.compatible_vehicles,
        spv.requires_indoor
    FROM service_pricing_view spv
    WHERE spv.is_active = 1
      AND (p_max_budget IS NULL OR spv.min_price <= p_max_budget)
      AND (spv.requires_indoor = 0 OR p_indoor_available = 1)
      AND (
          p_vehicle_type IS NULL 
          OR JSON_CONTAINS(spv.compatible_vehicles, CONCAT('"', p_vehicle_type, '"'))
          OR JSON_CONTAINS(spv.compatible_vehicles, '"universal"')
      )
    ORDER BY calculated_price ASC;
END$$
DELIMITER ;

-- Advanced analytics function for booking insights
DELIMITER $$
CREATE FUNCTION calculate_booking_metrics(
    p_start_date DATE,
    p_end_date DATE,
    p_service_category VARCHAR(50)
) RETURNS JSON
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_result JSON;
    DECLARE v_total_bookings INT DEFAULT 0;
    DECLARE v_total_revenue DECIMAL(10,2) DEFAULT 0.00;
    DECLARE v_avg_booking_value DECIMAL(10,2) DEFAULT 0.00;
    DECLARE v_cancellation_rate DECIMAL(5,2) DEFAULT 0.00;
    DECLARE v_peak_hours JSON;
    DECLARE v_popular_services JSON;
    
    -- Calculate basic metrics
    SELECT 
        COUNT(*),
        SUM(total_amount),
        AVG(total_amount),
        (SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*)) * 100
    INTO v_total_bookings, v_total_revenue, v_avg_booking_value, v_cancellation_rate
    FROM bookings b
    WHERE b.booking_date BETWEEN p_start_date AND p_end_date
      AND (p_service_category IS NULL OR JSON_EXTRACT(b.services_json, '$.main_service.category') = p_service_category)
      AND b.status != 'draft';
    
    -- Calculate peak booking hours
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'hour', booking_hour,
            'booking_count', booking_count,
            'avg_revenue', avg_revenue
        )
    ) INTO v_peak_hours
    FROM (
        SELECT 
            HOUR(start_time) as booking_hour,
            COUNT(*) as booking_count,
            AVG(total_amount) as avg_revenue
        FROM bookings b
        WHERE b.booking_date BETWEEN p_start_date AND p_end_date
          AND b.status IN ('completed', 'confirmed')
          AND (p_service_category IS NULL OR JSON_EXTRACT(b.services_json, '$.main_service.category') = p_service_category)
        GROUP BY HOUR(start_time)
        ORDER BY booking_count DESC
        LIMIT 5
    ) peak_data;
    
    -- Calculate popular services
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'service_title', service_title,
            'booking_count', booking_count,
            'total_revenue', total_revenue,
            'avg_rating', avg_rating
        )
    ) INTO v_popular_services
    FROM (
        SELECT 
            JSON_UNQUOTE(JSON_EXTRACT(services_json, '$.main_service.title')) as service_title,
            COUNT(*) as booking_count,
            SUM(total_amount) as total_revenue,
            AVG(customer_rating) as avg_rating
        FROM bookings b
        WHERE b.booking_date BETWEEN p_start_date AND p_end_date
          AND b.status = 'completed'
          AND (p_service_category IS NULL OR JSON_EXTRACT(b.services_json, '$.main_service.category') = p_service_category)
        GROUP BY JSON_UNQUOTE(JSON_EXTRACT(services_json, '$.main_service.title'))
        ORDER BY booking_count DESC
        LIMIT 10
    ) service_data;
    
    -- Build comprehensive result
    SET v_result = JSON_OBJECT(
        'period', JSON_OBJECT('start_date', p_start_date, 'end_date', p_end_date),
        'category_filter', IFNULL(p_service_category, 'all'),
        'metrics', JSON_OBJECT(
            'total_bookings', v_total_bookings,
            'total_revenue', v_total_revenue,
            'average_booking_value', v_avg_booking_value,
            'cancellation_rate_percent', v_cancellation_rate
        ),
        'peak_hours', IFNULL(v_peak_hours, JSON_ARRAY()),
        'popular_services', IFNULL(v_popular_services, JSON_ARRAY()),
        'generated_at', NOW()
    );
    
    RETURN v_result;
END$$
DELIMITER ;

-- Create secure customer data table with encryption
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Public identifiers
    customer_number VARCHAR(20) NOT NULL UNIQUE,
    display_name VARCHAR(100) NOT NULL,
    
    -- Encrypted PII fields
    encrypted_email VARBINARY(255) NOT NULL,
    encrypted_phone VARBINARY(255) NOT NULL,
    encrypted_address VARBINARY(500),
    
    -- Searchable hashed versions for lookups
    email_hash VARCHAR(64) NOT NULL UNIQUE,
    phone_hash VARCHAR(64) NOT NULL,
    
    -- Customer preferences and metadata
    preferences JSON DEFAULT '{}',
    communication_preferences JSON DEFAULT '{}',
    
    -- Account status and verification
    is_verified BOOLEAN DEFAULT FALSE,
    account_status ENUM('active', 'suspended', 'deleted') DEFAULT 'active',
    verification_token VARCHAR(255),
    
    -- Privacy and consent
    privacy_consent_given BOOLEAN DEFAULT FALSE,
    privacy_consent_date TIMESTAMP NULL,
    marketing_consent BOOLEAN DEFAULT FALSE,
    data_retention_date DATE NULL,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL,
    
    INDEX idx_email_hash (email_hash),
    INDEX idx_phone_hash (phone_hash),
    INDEX idx_customer_number (customer_number),
    INDEX idx_account_status (account_status)
);

-- Encryption/Decryption functions (application level implementation recommended)
DELIMITER $$
CREATE FUNCTION encrypt_pii_data(p_data VARCHAR(500), p_key VARCHAR(255))
RETURNS VARBINARY(500)
DETERMINISTIC
BEGIN
    -- Use AES encryption (in production, use application-level encryption)
    RETURN AES_ENCRYPT(p_data, p_key);
END$$

CREATE FUNCTION decrypt_pii_data(p_encrypted_data VARBINARY(500), p_key VARCHAR(255))
RETURNS VARCHAR(500)
DETERMINISTIC
BEGIN
    -- Decrypt data (in production, use application-level decryption)
    RETURN AES_DECRYPT(p_encrypted_data, p_key);
END$$

CREATE FUNCTION hash_for_lookup(p_data VARCHAR(255))
RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
    -- Create searchable hash for encrypted fields
    RETURN SHA2(CONCAT('lookup_salt_', LOWER(TRIM(p_data))), 256);
END$$
DELIMITER ;

-- Comprehensive audit log table
CREATE TABLE audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    -- Event identification
    event_id VARCHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    table_name VARCHAR(64) NOT NULL,
    record_id VARCHAR(50) NOT NULL,
    
    -- Action details
    action_type ENUM('CREATE', 'UPDATE', 'DELETE', 'LOGIN', 'LOGOUT', 'ACCESS', 'EXPORT') NOT NULL,
    action_category ENUM('data', 'security', 'system', 'user_action') NOT NULL,
    
    -- Change tracking
    old_values JSON,
    new_values JSON,
    changed_fields JSON,
    
    -- User and session context
    user_id INT,
    user_type ENUM('customer', 'staff', 'admin', 'system') NOT NULL,
    session_id VARCHAR(255),
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    -- Request context
    request_method VARCHAR(10),
    request_url VARCHAR(500),
    api_endpoint VARCHAR(255),
    request_id VARCHAR(36),
    
    -- Security context
    requires_review BOOLEAN DEFAULT FALSE,
    risk_level ENUM('low', 'medium', 'high', 'critical') DEFAULT 'low',
    compliance_tags JSON DEFAULT '[]',
    
    -- Temporal information
    occurred_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    business_date DATE NOT NULL,
    
    -- Metadata
    application_version VARCHAR(50),
    environment ENUM('development', 'staging', 'production') DEFAULT 'production',
    additional_metadata JSON DEFAULT '{}',
    
    INDEX idx_table_record (table_name, record_id),
    INDEX idx_user_action (user_id, action_type, occurred_at),
    INDEX idx_occurred_at (occurred_at),
    INDEX idx_risk_level (risk_level, requires_review),
    INDEX idx_business_date (business_date),
    INDEX idx_event_id (event_id)
);

-- Audit trigger for bookings table
DELIMITER $$
CREATE TRIGGER audit_bookings_changes
AFTER UPDATE ON bookings
FOR EACH ROW
BEGIN
    DECLARE v_changed_fields JSON DEFAULT '[]';
    DECLARE v_risk_level ENUM('low', 'medium', 'high', 'critical') DEFAULT 'low';
    DECLARE v_requires_review BOOLEAN DEFAULT FALSE;
    
    -- Determine changed fields
    IF OLD.status != NEW.status THEN
        SET v_changed_fields = JSON_ARRAY_APPEND(v_changed_fields, '$', 'status');
        IF NEW.status = 'cancelled' THEN
            SET v_risk_level = 'medium';
            SET v_requires_review = TRUE;
        END IF;
    END IF;
    
    IF OLD.total_amount != NEW.total_amount THEN
        SET v_changed_fields = JSON_ARRAY_APPEND(v_changed_fields, '$', 'total_amount');
        SET v_risk_level = 'high';
        SET v_requires_review = TRUE;
    END IF;
    
    IF OLD.booking_date != NEW.booking_date OR OLD.start_time != NEW.start_time THEN
        SET v_changed_fields = JSON_ARRAY_APPEND(v_changed_fields, '$', 'schedule');
        SET v_risk_level = 'medium';
    END IF;
    
    -- Log the change
    INSERT INTO audit_log (
        table_name,
        record_id,
        action_type,
        action_category,
        old_values,
        new_values,
        changed_fields,
        user_id,
        user_type,
        risk_level,
        requires_review,
        business_date
    ) VALUES (
        'bookings',
        NEW.id,
        'UPDATE',
        'data',
        JSON_OBJECT(
            'status', OLD.status,
            'total_amount', OLD.total_amount,
            'booking_date', OLD.booking_date,
            'start_time', OLD.start_time
        ),
        JSON_OBJECT(
            'status', NEW.status,
            'total_amount', NEW.total_amount,
            'booking_date', NEW.booking_date,
            'start_time', NEW.start_time
        ),
        v_changed_fields,
        @current_user_id,
        @current_user_type,
        v_risk_level,
        v_requires_review,
        CURDATE()
    );
END$$
DELIMITER ;

-- Composite indexes for common query patterns
CREATE INDEX idx_booking_search_composite ON bookings (
    booking_date, status, primary_staff_id, customer_email
);

-- Covering index for booking list queries
CREATE INDEX idx_booking_list_covering ON bookings (
    status, booking_date
) INCLUDE (
    id, booking_number, customer_name, start_time, end_time, total_amount
);

-- Partial indexes for active records only
CREATE INDEX idx_active_services_partial ON services (category, base_price) 
WHERE is_active = TRUE;

CREATE INDEX idx_future_bookings_partial ON bookings (booking_date, start_time)
WHERE booking_date >= CURDATE() AND status IN ('pending', 'confirmed');

-- JSON functional indexes for performance
CREATE INDEX idx_json_service_requirements ON services (
    (CAST(JSON_EXTRACT(requirements, '$.indoor_required') AS UNSIGNED)),
    (CAST(JSON_EXTRACT(pricing_config, '$.min_price') AS DECIMAL(10,2)))
);

-- Staff availability optimization
CREATE INDEX idx_staff_schedule_lookup ON staff_schedules (
    staff_id, effective_date, end_date
) WHERE is_active = TRUE;

-- Time slot configuration optimization
CREATE INDEX idx_timeslot_day_active ON time_slot_config (
    day_of_week, effective_date_start, effective_date_end
) WHERE is_active = TRUE;

-- High-performance availability checking query
WITH staff_availability AS (
    SELECT DISTINCT
        ss.staff_id,
        ss.hourly_rate,
        JSON_EXTRACT(ss.schedule_pattern, CONCAT('$[', DAYOFWEEK(?) - 1, ']')) as day_schedule
    FROM staff_schedules ss
    WHERE ss.is_active = TRUE
      AND ? BETWEEN ss.effective_date AND IFNULL(ss.end_date, '2099-12-31')
      AND JSON_EXTRACT(ss.schedule_pattern, CONCAT('$[', DAYOFWEEK(?) - 1, '].available')) = true
),
time_slots AS (
    SELECT 
        tsc.start_time,
        tsc.end_time,
        tsc.price_modifier_percentage,
        tsc.max_concurrent_bookings
    FROM time_slot_config tsc
    WHERE tsc.day_of_week = DAYOFWEEK(?)
      AND tsc.is_active = TRUE
      AND ? BETWEEN tsc.effective_date_start AND tsc.effective_date_end
),
existing_bookings AS (
    SELECT 
        b.primary_staff_id,
        b.start_time,
        b.end_time,
        COUNT(*) as concurrent_count
    FROM bookings b
    WHERE b.booking_date = ?
      AND b.status IN ('confirmed', 'in_progress')
    GROUP BY b.primary_staff_id, b.start_time, b.end_time
)
SELECT 
    sa.staff_id,
    ts.start_time,
    ts.end_time,
    sa.hourly_rate,
    ts.price_modifier_percentage,
    CASE 
        WHEN eb.concurrent_count IS NULL THEN ts.max_concurrent_bookings
        ELSE ts.max_concurrent_bookings - eb.concurrent_count
    END as available_slots
FROM staff_availability sa
CROSS JOIN time_slots ts
LEFT JOIN existing_bookings eb ON (
    eb.primary_staff_id = sa.staff_id 
    AND eb.start_time = ts.start_time
)
WHERE NOT EXISTS (
    SELECT 1 FROM unavailable_slots us
    WHERE us.date = ?
      AND us.status = 'active'
      AND (us.affects_all_staff = TRUE OR JSON_CONTAINS(us.affected_staff_ids, CAST(sa.staff_id AS JSON)))
      AND (us.is_all_day = TRUE OR (us.start_time <= ts.start_time AND us.end_time >= ts.end_time))
)
AND (eb.concurrent_count IS NULL OR eb.concurrent_count < ts.max_concurrent_bookings)
ORDER BY ts.start_time, sa.hourly_rate;

-- Test Suite 1: Data Integrity Validation
-- Test 1: Booking time conflicts
SELECT 
    'Time Conflict Test' as test_name,
    CASE 
        WHEN COUNT(*) = 0 THEN 'PASS'
        ELSE CONCAT('FAIL - Found ', COUNT(*), ' conflicts')
    END as result
FROM bookings b1
JOIN bookings b2 ON (
    b1.booking_date = b2.booking_date
    AND b1.primary_staff_id = b2.primary_staff_id
    AND b1.id != b2.id
    AND b1.status IN ('confirmed', 'in_progress')
    AND b2.status IN ('confirmed', 'in_progress')
    AND (
        (b1.start_time < b2.end_time AND b1.end_time > b2.start_time)
    )
);

-- Test 2: Business rule validation
SELECT 
    'Business Rules Test' as test_name,
    CASE 
        WHEN COUNT(*) = 0 THEN 'PASS'
        ELSE CONCAT('FAIL - Found ', COUNT(*), ' violations')
    END as result
FROM bookings b
WHERE (
    b.booking_date < CURDATE() AND b.status = 'pending'
) OR (
    b.start_time >= b.end_time
) OR (
    b.total_amount <= 0 AND b.status != 'cancelled'
);

-- Test 3: Foreign key integrity
SELECT 
    'Foreign Key Test' as test_name,
    CASE 
        WHEN COUNT(*) = 0 THEN 'PASS'
        ELSE CONCAT('FAIL - Found ', COUNT(*), ' orphaned records')
    END as result
FROM bookings b
LEFT JOIN customers c ON b.customer_id = c.id
LEFT JOIN staff s ON b.primary_staff_id = s.id
WHERE (b.customer_id IS NOT NULL AND c.id IS NULL)
   OR (b.primary_staff_id IS NOT NULL AND s.id IS NULL);

-- Test 4: JSON schema validation
SELECT 
    'JSON Schema Test' as test_name,
    CASE 
        WHEN COUNT(*) = 0 THEN 'PASS'
        ELSE CONCAT('FAIL - Found ', COUNT(*), ' invalid JSON')
    END as result
FROM services s
WHERE NOT JSON_VALID(s.pricing_config)
   OR NOT JSON_VALID(s.requirements)
   OR NOT JSON_VALID(s.business_rules);

-- Create comprehensive analytics views for business intelligence
CREATE VIEW booking_analytics_daily AS
SELECT 
    DATE(b.booking_date) as business_date,
    COUNT(*) as total_bookings,
    COUNT(CASE WHEN b.status = 'completed' THEN 1 END) as completed_bookings,
    COUNT(CASE WHEN b.status = 'cancelled' THEN 1 END) as cancelled_bookings,
    COUNT(CASE WHEN b.status = 'no_show' THEN 1 END) as no_show_bookings,
    SUM(CASE WHEN b.status = 'completed' THEN b.total_amount ELSE 0 END) as revenue,
    AVG(CASE WHEN b.status = 'completed' THEN b.total_amount END) as avg_booking_value,
    AVG(CASE WHEN b.customer_rating IS NOT NULL THEN b.customer_rating END) as avg_rating,
    COUNT(DISTINCT b.customer_email) as unique_customers,
    COUNT(DISTINCT b.primary_staff_id) as staff_utilized
FROM bookings b
WHERE b.booking_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY DATE(b.booking_date)
ORDER BY business_date DESC;

-- Staff performance analytics
CREATE VIEW staff_performance_analytics AS
SELECT 
    s.id as staff_id,
    s.name as staff_name,
    COUNT(b.id) as total_bookings,
    COUNT(CASE WHEN b.status = 'completed' THEN 1 END) as completed_bookings,
    SUM(CASE WHEN b.status = 'completed' THEN b.total_amount ELSE 0 END) as revenue_generated,
    AVG(CASE WHEN b.customer_rating IS NOT NULL THEN b.customer_rating END) as avg_customer_rating,
    COUNT(CASE WHEN b.status = 'cancelled' THEN 1 END) as cancellations,
    AVG(CASE WHEN b.status = 'completed' 
        THEN TIMESTAMPDIFF(MINUTE, b.start_time, b.end_time) END) as avg_service_duration,
    (COUNT(CASE WHEN b.status = 'completed' THEN 1 END) / COUNT(b.id)) * 100 as completion_rate
FROM staff s
LEFT JOIN bookings b ON s.id = b.primary_staff_id 
    AND b.booking_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY s.id, s.name
ORDER BY revenue_generated DESC;

-- Service popularity and profitability
CREATE VIEW service_analytics AS
SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(b.services_json, '$.main_service.title')) as service_name,
    JSON_UNQUOTE(JSON_EXTRACT(b.services_json, '$.main_service.category')) as service_category,
    COUNT(*) as booking_count,
    SUM(b.total_amount) as total_revenue,
    AVG(b.total_amount) as avg_price,
    AVG(b.customer_rating) as avg_rating,
    COUNT(CASE WHEN b.status = 'cancelled' THEN 1 END) as cancellation_count,
    (COUNT(CASE WHEN b.status = 'cancelled' THEN 1 END) / COUNT(*)) * 100 as cancellation_rate
FROM bookings b
WHERE b.booking_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND JSON_EXTRACT(b.services_json, '$.main_service.title') IS NOT NULL
GROUP BY 
    JSON_UNQUOTE(JSON_EXTRACT(b.services_json, '$.main_service.title')),
    JSON_UNQUOTE(JSON_EXTRACT(b.services_json, '$.main_service.category'))
ORDER BY total_revenue DESC;

#!/bin/bash
# Comprehensive backup script for booking system database

# Configuration
DB_NAME="booking_system"
DB_USER="booking_user"
DB_PASSWORD="secure_booking_password"
BACKUP_DIR="/backups/booking_system"
S3_BUCKET="company-database-backups"
RETENTION_DAYS=30

# Create backup directory
mkdir -p $BACKUP_DIR

# Generate timestamp
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Full database backup
echo "Starting full database backup..."
mysqldump --single-transaction --routines --triggers \
  --user=$DB_USER --password=$DB_PASSWORD \
  --databases $DB_NAME > $BACKUP_DIR/full_backup_$TIMESTAMP.sql

# Verify backup integrity
if mysql --user=$DB_USER --password=$DB_PASSWORD -e "use $DB_NAME; SELECT 1;" &> /dev/null; then
    echo "Database connection verified"
    
    # Test backup file
    if [ -s "$BACKUP_DIR/full_backup_$TIMESTAMP.sql" ]; then
        echo "Backup file created successfully"
        
        # Compress backup
        gzip $BACKUP_DIR/full_backup_$TIMESTAMP.sql
        
        # Upload to cloud storage
        aws s3 cp $BACKUP_DIR/full_backup_$TIMESTAMP.sql.gz \
          s3://$S3_BUCKET/daily/$(date +%Y/%m/%d)/
        
        # Create point-in-time recovery backup
        mysql --user=$DB_USER --password=$DB_PASSWORD \
          -e "FLUSH LOGS; SHOW MASTER STATUS;" > $BACKUP_DIR/binlog_position_$TIMESTAMP.txt
        
        echo "Backup completed successfully at $(date)"
    else
        echo "ERROR: Backup file is empty or missing"
        exit 1
    fi
else
    echo "ERROR: Cannot connect to database"
    exit 1
fi

# Cleanup old backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
find $BACKUP_DIR -name "*.txt" -mtime +$RETENTION_DAYS -delete

echo "Backup process completed"