-- Apply once to an existing Pet Shop database before using Spa Booking.
-- day_of_week follows PHP DateTime::format('w'): 0 = Sunday, 6 = Saturday.

CREATE TABLE IF NOT EXISTS service_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    description TEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_service_categories_active_sort (is_active, sort_order, name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS services (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NULL,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    short_description VARCHAR(500) NULL,
    description TEXT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0,
    duration_minutes SMALLINT UNSIGNED NOT NULL,
    featured_image VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_services_category FOREIGN KEY (category_id)
        REFERENCES service_categories(id) ON DELETE SET NULL,
    CHECK (price >= 0),
    CHECK (duration_minutes > 0),
    INDEX idx_services_active_sort (is_active, sort_order, name),
    INDEX idx_services_category_active_sort (category_id, is_active, sort_order)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS business_hours (
    id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    day_of_week TINYINT UNSIGNED NOT NULL,
    open_time TIME NULL,
    close_time TIME NULL,
    is_open TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_business_hours_day (day_of_week),
    CHECK (day_of_week BETWEEN 0 AND 6),
    CHECK ((is_open = 0 AND open_time IS NULL AND close_time IS NULL)
        OR (is_open = 1 AND open_time IS NOT NULL AND close_time IS NOT NULL
            AND open_time < close_time))
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS blocked_slots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    blocked_date DATE NOT NULL,
    start_time TIME NULL,
    end_time TIME NULL,
    reason VARCHAR(500) NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_blocked_slots_creator FOREIGN KEY (created_by)
        REFERENCES users(id) ON DELETE SET NULL,
    CHECK ((start_time IS NULL AND end_time IS NULL)
        OR (start_time IS NOT NULL AND end_time IS NOT NULL
            AND start_time < end_time)),
    INDEX idx_blocked_slots_date_time (blocked_date, start_time, end_time)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bookings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    booking_code VARCHAR(40) NOT NULL UNIQUE,
    customer_id BIGINT UNSIGNED NOT NULL,
    service_id BIGINT UNSIGNED NOT NULL,
    service_name_snapshot VARCHAR(200) NOT NULL,
    service_price_snapshot DECIMAL(12,2) NOT NULL,
    duration_minutes_snapshot SMALLINT UNSIGNED NOT NULL,
    pet_name VARCHAR(120) NOT NULL,
    pet_type ENUM('dog', 'cat', 'other') NOT NULL,
    pet_breed VARCHAR(120) NULL,
    pet_weight DECIMAL(6,2) NULL,
    pet_note VARCHAR(1000) NULL,
    booking_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    customer_note VARCHAR(1000) NULL,
    owner_note VARCHAR(1000) NULL,
    status ENUM('pending', 'confirmed', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
    confirmed_at DATETIME NULL,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_bookings_customer FOREIGN KEY (customer_id)
        REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_bookings_service FOREIGN KEY (service_id)
        REFERENCES services(id) ON DELETE RESTRICT,
    CHECK (service_price_snapshot >= 0),
    CHECK (duration_minutes_snapshot > 0),
    CHECK (end_time > start_time),
    INDEX idx_bookings_customer_date (customer_id, booking_date, created_at),
    INDEX idx_bookings_date_status_time (booking_date, status, start_time, end_time),
    INDEX idx_bookings_status_date (status, booking_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS booking_status_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    booking_id BIGINT UNSIGNED NOT NULL,
    actor_id BIGINT UNSIGNED NULL,
    old_status VARCHAR(30) NULL,
    new_status VARCHAR(30) NOT NULL,
    note VARCHAR(1000) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_booking_status_logs_booking FOREIGN KEY (booking_id)
        REFERENCES bookings(id) ON DELETE CASCADE,
    CONSTRAINT fk_booking_status_logs_actor FOREIGN KEY (actor_id)
        REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_booking_status_logs_booking_created (booking_id, created_at)
) ENGINE=InnoDB;

INSERT IGNORE INTO business_hours (day_of_week, open_time, close_time, is_open)
VALUES
    (0, NULL, NULL, 0),
    (1, '08:00:00', '18:00:00', 1),
    (2, '08:00:00', '18:00:00', 1),
    (3, '08:00:00', '18:00:00', 1),
    (4, '08:00:00', '18:00:00', 1),
    (5, '08:00:00', '18:00:00', 1),
    (6, '08:00:00', '18:00:00', 1);