-- ============================================================
-- PET SHOP DATABASE SCHEMA
-- Source of truth for a clean MySQL 8.x installation.
-- Scope: legacy Pet Shop tables plus the active Pet Spa Booking module.
-- ============================================================

-- ============================================================
-- USERS
-- ============================================================

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    phone VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(190) NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'owner', 'customer') NOT NULL DEFAULT 'customer',
    avatar VARCHAR(255) NULL,
    status ENUM('active', 'locked') NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_role (role),
    INDEX idx_users_status (status),
    INDEX idx_users_role_created (role, created_at)
) ENGINE=InnoDB;

CREATE TABLE remember_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL UNIQUE,
    expires_at DATETIME NOT NULL,
    last_used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_remember_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_remember_tokens_user_expiry (user_id, expires_at)
) ENGINE=InnoDB;

-- ============================================================
-- ROLE PERMISSIONS (ACTIVE PET SPA CUSTOMER / OWNER AREAS)
-- ============================================================

CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    permission_key VARCHAR(100) NOT NULL UNIQUE,
    permission_name VARCHAR(150) NOT NULL,
    module VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role ENUM('customer', 'owner') NOT NULL,
    permission_key VARCHAR(100) NOT NULL,
    allowed TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_role_permissions_role_key (role, permission_key),
    CONSTRAINT fk_role_permissions_permission
        FOREIGN KEY (permission_key) REFERENCES permissions(permission_key)
        ON DELETE CASCADE,
    INDEX idx_role_permissions_role_allowed (role, allowed)
) ENGINE=InnoDB;

INSERT INTO permissions (permission_key, permission_name, module) VALUES
('customer.dashboard', 'Tổng quan khách hàng', 'customer'),
('customer.booking.create', 'Đặt lịch', 'customer'),
('customer.booking.view', 'Lịch hẹn của tôi', 'customer'),
('customer.notification.view', 'Thông báo', 'customer'),
('customer.profile.manage', 'Hồ sơ và mật khẩu', 'customer'),
('owner.dashboard', 'Tổng quan Owner', 'owner'),
('owner.booking.create', 'Đặt lịch', 'owner'),
('owner.booking.manage', 'Quản lý lịch hẹn', 'owner'),
('owner.service.manage', 'Quản lý dịch vụ', 'owner'),
('owner.service_category.manage', 'Danh mục dịch vụ', 'owner'),
('owner.business_hours.manage', 'Giờ phục vụ', 'owner'),
('owner.customer.view', 'Khách hàng', 'owner'),
('owner.post.manage', 'Quản lý bài viết', 'owner'),
('owner.post_category.manage', 'Danh mục bài viết', 'owner'),
('owner.gallery.manage', 'Thư viện', 'owner'),
('owner.contact.manage', 'Liên hệ', 'owner'),
('owner.notification.view', 'Thông báo Owner', 'owner'),
('owner.settings.manage', 'Cài đặt', 'owner');

INSERT INTO role_permissions (role, permission_key, allowed)
SELECT
    CASE WHEN p.module = 'customer' THEN 'customer' ELSE 'owner' END,
    p.permission_key,
    1
FROM permissions p;

CREATE TABLE customer_addresses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    recipient_name VARCHAR(120) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address_line VARCHAR(255) NOT NULL,
    ward VARCHAR(100) NULL,
    district VARCHAR(100) NULL,
    province VARCHAR(100) NULL,
    is_default TINYINT(1) 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_customer_addresses_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,
    INDEX idx_customer_addresses_user (user_id),
    INDEX idx_customer_addresses_user_default_id (user_id, is_default, id)
) ENGINE=InnoDB;

CREATE TABLE password_resets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_password_resets_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,
    INDEX idx_password_resets_user (user_id)
) ENGINE=InnoDB;

-- ============================================================
-- CATALOG
-- ============================================================

CREATE TABLE product_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id INT UNSIGNED NULL,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    description TEXT NULL,
    image VARCHAR(255) 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,
    CONSTRAINT fk_product_categories_parent
        FOREIGN KEY (parent_id) REFERENCES product_categories(id)
        ON DELETE SET NULL,
    INDEX idx_product_categories_parent (parent_id),
    INDEX idx_product_categories_active (is_active),
    INDEX idx_product_categories_active_sort_name (is_active, sort_order, name)
) ENGINE=InnoDB;

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    sku VARCHAR(80) NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    short_description VARCHAR(500) NULL,
    description LONGTEXT NULL,
    brand VARCHAR(120) NULL,
    pet_target ENUM('dog', 'cat', 'all') NOT NULL DEFAULT 'all',
    price DECIMAL(12,2) NOT NULL DEFAULT 0,
    sale_price DECIMAL(12,2) NULL,
    stock_quantity INT UNSIGNED NOT NULL DEFAULT 0,
    sold_quantity INT UNSIGNED NOT NULL DEFAULT 0,
    featured_image VARCHAR(255) NULL,
    status ENUM('active', 'hidden', 'out_of_stock')
        NOT NULL DEFAULT 'active',
    is_featured TINYINT(1) 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_products_category
        FOREIGN KEY (category_id) REFERENCES product_categories(id)
        ON DELETE RESTRICT,
    CHECK (price >= 0),
    CHECK (sale_price IS NULL OR sale_price >= 0),
    INDEX idx_products_category (category_id),
    INDEX idx_products_status (status),
    INDEX idx_products_featured (is_featured),
    INDEX idx_products_pet_target (pet_target),
    INDEX idx_products_status_created (status, created_at),
    INDEX idx_products_category_status_created (category_id, status, created_at),
    INDEX idx_products_featured_rank (
        status, is_featured, sold_quantity, created_at
    )
) ENGINE=InnoDB;

CREATE TABLE product_images (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_images_product
        FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE CASCADE,
    INDEX idx_product_images_product (product_id),
    INDEX idx_product_images_product_sort_id (product_id, sort_order, id)
) ENGINE=InnoDB;

-- ============================================================
-- CART AND PROMOTIONS
-- ============================================================

CREATE TABLE carts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL UNIQUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_carts_customer
        FOREIGN KEY (customer_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE cart_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cart_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_cart_items_cart
        FOREIGN KEY (cart_id) REFERENCES carts(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_cart_items_product
        FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE CASCADE,
    CHECK (quantity > 0),
    UNIQUE KEY uq_cart_product (cart_id, product_id)
) ENGINE=InnoDB;

CREATE TABLE promotions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(50) NULL UNIQUE,
    description TEXT NULL,
    discount_type ENUM('percent', 'fixed') NOT NULL,
    discount_value DECIMAL(12,2) NOT NULL,
    min_order_value DECIMAL(12,2) NOT NULL DEFAULT 0,
    max_discount DECIMAL(12,2) NULL,
    start_at DATETIME NOT NULL,
    end_at DATETIME NOT NULL,
    usage_limit INT UNSIGNED NULL,
    used_count INT UNSIGNED 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,
    CHECK (discount_value >= 0),
    CHECK (
        (discount_type = 'percent' AND discount_value <= 100)
        OR discount_type = 'fixed'
    ),
    CHECK (end_at > start_at),
    INDEX idx_promotions_created (created_at)
) ENGINE=InnoDB;

-- ============================================================
-- ORDERS
-- ============================================================

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_code VARCHAR(30) NOT NULL UNIQUE,
    customer_id BIGINT UNSIGNED NOT NULL,
    promotion_id BIGINT UNSIGNED NULL,
    recipient_name VARCHAR(120) NOT NULL,
    recipient_phone VARCHAR(20) NOT NULL,
    shipping_address VARCHAR(500) NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    shipping_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    payment_method ENUM('cod', 'bank_transfer') NOT NULL DEFAULT 'cod',
    payment_status ENUM('unpaid', 'paid', 'failed', 'refunded')
        NOT NULL DEFAULT 'unpaid',
    status ENUM(
        'pending',
        'confirmed',
        'packing',
        'shipping',
        'completed',
        'cancelled'
    ) NOT NULL DEFAULT 'pending',
    customer_note TEXT NULL,
    owner_note TEXT NULL,
    confirmed_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_orders_customer
        FOREIGN KEY (customer_id) REFERENCES users(id)
        ON DELETE RESTRICT,
    CONSTRAINT fk_orders_promotion
        FOREIGN KEY (promotion_id) REFERENCES promotions(id)
        ON DELETE SET NULL,
    CHECK (subtotal >= 0),
    CHECK (discount_amount >= 0),
    CHECK (shipping_fee >= 0),
    CHECK (total_amount >= 0),
    INDEX idx_orders_customer (customer_id),
    INDEX idx_orders_status (status),
    INDEX idx_orders_created (created_at),
    INDEX idx_orders_customer_created (customer_id, created_at),
    INDEX idx_orders_customer_status_created (
        customer_id, status, created_at
    ),
    INDEX idx_orders_status_created (status, created_at),
    INDEX idx_orders_status_completed (status, completed_at)
) ENGINE=InnoDB;

CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    sku VARCHAR(80) NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    line_total DECIMAL(12,2) NOT NULL,
    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE RESTRICT,
    CHECK (unit_price >= 0),
    CHECK (quantity > 0),
    CHECK (line_total >= 0),
    INDEX idx_order_items_order (order_id)
) ENGINE=InnoDB;

CREATE TABLE order_status_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    changed_by BIGINT UNSIGNED NULL,
    old_status VARCHAR(30) NULL,
    new_status VARCHAR(30) NOT NULL,
    note VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_order_status_logs_order
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_order_status_logs_user
        FOREIGN KEY (changed_by) REFERENCES users(id)
        ON DELETE SET NULL,
    INDEX idx_order_status_logs_order (order_id),
    INDEX idx_order_status_logs_order_created (order_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE product_reviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    order_item_id BIGINT UNSIGNED NULL,
    rating TINYINT UNSIGNED NOT NULL,
    comment TEXT NULL,
    owner_reply TEXT NULL,
    status ENUM('visible', 'hidden') NOT NULL DEFAULT 'visible',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    replied_at DATETIME NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_reviews_customer
        FOREIGN KEY (customer_id) REFERENCES users(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_product_reviews_product
        FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_product_reviews_order_item
        FOREIGN KEY (order_item_id) REFERENCES order_items(id)
        ON DELETE SET NULL,
    CHECK (rating BETWEEN 1 AND 5),
    INDEX idx_product_reviews_product (product_id),
    INDEX idx_product_reviews_customer (customer_id),
    INDEX idx_product_reviews_customer_created (customer_id, created_at),
    INDEX idx_product_reviews_product_status_created (
        product_id, status, created_at
    ),
    INDEX idx_product_reviews_status_created (status, created_at)
) ENGINE=InnoDB;

-- ============================================================
-- SPA SERVICES AND BOOKINGS
-- ============================================================

CREATE TABLE 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 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;

-- day_of_week follows PHP DateTime::format('w'): 0 = Sunday, 6 = Saturday.
CREATE TABLE 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 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 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 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;

-- ============================================================
-- POSTS AND CONTENT
-- ============================================================

CREATE TABLE post_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    INDEX idx_post_categories_active_name (is_active, name)
) ENGINE=InnoDB;

CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NULL,
    author_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(280) NOT NULL UNIQUE,
    excerpt VARCHAR(500) NULL,
    content LONGTEXT NOT NULL,
    featured_image VARCHAR(255) NULL,
    status ENUM('draft', 'published', 'hidden') NOT NULL DEFAULT 'draft',
    published_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_posts_category
        FOREIGN KEY (category_id) REFERENCES post_categories(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_posts_author
        FOREIGN KEY (author_id) REFERENCES users(id)
        ON DELETE RESTRICT,
    INDEX idx_posts_status_date (status, published_at),
    INDEX idx_posts_category (category_id),
    INDEX idx_posts_status_created (status, created_at),
    INDEX idx_posts_category_status_published (
        category_id, status, published_at
    )
) ENGINE=InnoDB;

CREATE TABLE gallery_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NULL,
    caption VARCHAR(500) NULL,
    image_path VARCHAR(255) NOT NULL,
    category VARCHAR(80) NULL,
    is_visible TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_gallery_visible (is_visible, sort_order),
    INDEX idx_gallery_visible_category_sort (
        is_visible, category, sort_order, created_at
    )
) ENGINE=InnoDB;

CREATE TABLE contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(190) NULL,
    subject VARCHAR(200) NULL,
    message TEXT NOT NULL,
    status ENUM('new', 'reading', 'replied', 'closed')
        NOT NULL DEFAULT 'new',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_contacts_status (status),
    INDEX idx_contacts_created (created_at),
    INDEX idx_contacts_status_created (status, created_at)
) ENGINE=InnoDB;

CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    type VARCHAR(50) NOT NULL,
    title VARCHAR(200) NOT NULL,
    message VARCHAR(1000) NOT NULL,
    link VARCHAR(500) NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    read_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_notifications_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,
    INDEX idx_notifications_user_read (user_id, is_read)
) ENGINE=InnoDB;

-- ============================================================
-- SHOP CONFIGURATION AND AUDIT
-- ============================================================

CREATE TABLE shop_settings (
    id TINYINT UNSIGNED PRIMARY KEY,
    shop_name VARCHAR(150) NOT NULL,
    logo VARCHAR(255) NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(190) NULL,
    address VARCHAR(255) NULL,
    description TEXT NULL,
    map_embed_url TEXT NULL,
    shipping_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
    free_shipping_from DECIMAL(12,2) NULL,
    currency CHAR(3) NOT NULL DEFAULT 'VND',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    CHECK (shipping_fee >= 0),
    CHECK (free_shipping_from IS NULL OR free_shipping_from >= 0)
) ENGINE=InnoDB;

CREATE TABLE social_links (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    platform VARCHAR(50) NOT NULL,
    url VARCHAR(500) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY uq_social_platform (platform)
) ENGINE=InnoDB;

CREATE TABLE activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(80) NULL,
    entity_id BIGINT UNSIGNED NULL,
    details_json JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_activity_logs_actor
        FOREIGN KEY (actor_id) REFERENCES users(id)
        ON DELETE SET NULL,
    INDEX idx_activity_logs_actor (actor_id),
    INDEX idx_activity_logs_entity (entity_type, entity_id),
    INDEX idx_activity_logs_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE system_settings (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL,
    setting_value VARCHAR(255) NOT NULL,
    setting_type ENUM('boolean', 'integer', 'string') NOT NULL DEFAULT 'string',
    updated_by BIGINT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_system_settings_key (setting_key),
    CONSTRAINT fk_system_settings_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(id)
        ON DELETE SET NULL,
    INDEX idx_system_settings_updated_by (updated_by)
) ENGINE=InnoDB;

-- ============================================================
-- MINIMAL DEFAULT DATA
-- ============================================================

INSERT INTO product_categories (name, slug, sort_order)
VALUES
    ('Thức ăn cho chó', 'thuc-an-cho-cho', 1),
    ('Thức ăn cho mèo', 'thuc-an-cho-meo', 2),
    ('Pate & đồ ăn vặt', 'pate-do-an-vat', 3),
    ('Phụ kiện', 'phu-kien', 4),
    ('Đồ chơi', 'do-choi', 5),
    ('Vệ sinh & chăm sóc', 've-sinh-cham-soc', 6),
    ('Chuồng, nệm & balo', 'chuong-nem-balo', 7);

INSERT INTO post_categories (name, slug)
VALUES
    ('Kiến thức thú cưng', 'kien-thuc-thu-cung'),
    ('Chăm sóc chó', 'cham-soc-cho'),
    ('Chăm sóc mèo', 'cham-soc-meo'),
    ('Tin tức cửa hàng', 'tin-tuc-cua-hang'),
    ('Khuyến mãi', 'khuyen-mai');

INSERT INTO shop_settings (
    id,
    shop_name,
    shipping_fee,
    free_shipping_from,
    currency
)
VALUES (
    1,
    'PET SHOP',
    30000,
    500000,
    'VND'
);

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

INSERT INTO system_settings (
    setting_key,
    setting_value,
    setting_type
)
VALUES
    ('registration_enabled', '1', 'boolean'),
    ('maintenance_mode', '0', 'boolean'),
    ('activity_logging_enabled', '1', 'boolean'),
    ('max_upload_mb', '5', 'integer');