-- Phase 11: guest waiting experience, promotions, events and engagement analytics
SET @phase11_has_serving_time := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='order_items' AND COLUMN_NAME='serving_time');
SET @phase11_add_serving_time := IF(@phase11_has_serving_time=0,'ALTER TABLE order_items ADD COLUMN serving_time SMALLINT UNSIGNED NULL AFTER preparation_time','SELECT 1');
PREPARE phase11_stmt FROM @phase11_add_serving_time;
EXECUTE phase11_stmt;
DEALLOCATE PREPARE phase11_stmt;
UPDATE order_items oi JOIN menu_items mi ON mi.id=oi.menu_item_id SET oi.serving_time=mi.serving_time WHERE oi.serving_time IS NULL;

CREATE TABLE IF NOT EXISTS promotions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    restaurant_id BIGINT UNSIGNED NULL,
    menu_item_id BIGINT UNSIGNED NULL,
    title VARCHAR(180) NOT NULL,
    subtitle VARCHAR(220) NULL,
    description TEXT NULL,
    promotion_type ENUM('OFFER','DISCOUNT','SPECIAL_DISH','WEEKEND_SPECIAL','WEEKDAY_SPECIAL') NOT NULL DEFAULT 'OFFER',
    discount_type ENUM('NONE','PERCENT','FIXED') NOT NULL DEFAULT 'NONE',
    discount_value DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    audience ENUM('ALL','ROOM_GUESTS','DINE_IN','ROOM_SERVICE') NOT NULL DEFAULT 'ALL',
    start_at DATETIME NULL,
    end_at DATETIME NULL,
    weekday_mask TINYINT UNSIGNED NOT NULL DEFAULT 0,
    time_from TIME NULL,
    time_to TIME NULL,
    minimum_order_total DECIMAL(12,2) NULL,
    priority SMALLINT NOT NULL DEFAULT 100,
    status ENUM('DRAFT','ACTIVE','INACTIVE') NOT NULL DEFAULT 'DRAFT',
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_promotion_public (public_id),
    INDEX idx_promotion_active (hotel_id,status,start_at,end_at,priority),
    INDEX idx_promotion_restaurant (restaurant_id,status),
    CONSTRAINT fk_promotion_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_promotion_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE SET NULL,
    CONSTRAINT fk_promotion_menu_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE SET NULL,
    CONSTRAINT fk_promotion_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hotel_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    restaurant_id BIGINT UNSIGNED NULL,
    title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    location VARCHAR(220) NULL,
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NULL,
    booking_opens_at DATETIME NULL,
    booking_closes_at DATETIME NULL,
    priority SMALLINT NOT NULL DEFAULT 100,
    status ENUM('DRAFT','ACTIVE','CANCELLED','COMPLETED') NOT NULL DEFAULT 'DRAFT',
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_hotel_event_public (public_id),
    INDEX idx_hotel_event_upcoming (hotel_id,status,starts_at,priority),
    INDEX idx_hotel_event_restaurant (restaurant_id,starts_at),
    CONSTRAINT fk_hotel_event_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_hotel_event_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE SET NULL,
    CONSTRAINT fk_hotel_event_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS engagement_cards (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    promotion_id BIGINT UNSIGNED NULL,
    event_id BIGINT UNSIGNED NULL,
    restaurant_id BIGINT UNSIGNED NULL,
    title VARCHAR(180) NULL,
    body VARCHAR(500) NULL,
    audience ENUM('ALL','ROOM_GUESTS','DINE_IN','ROOM_SERVICE') NOT NULL DEFAULT 'ALL',
    min_wait_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    max_wait_minutes SMALLINT UNSIGNED NULL,
    cta_label VARCHAR(80) NULL,
    cta_type ENUM('NONE','MENU_ITEM','RESERVATION','LOCAL_URL') NOT NULL DEFAULT 'NONE',
    cta_url VARCHAR(500) NULL,
    display_order SMALLINT NOT NULL DEFAULT 100,
    status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_engagement_card_public (public_id),
    INDEX idx_engagement_card_active (hotel_id,status,display_order),
    CONSTRAINT fk_engagement_card_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_card_promotion FOREIGN KEY (promotion_id) REFERENCES promotions(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_card_event FOREIGN KEY (event_id) REFERENCES hotel_events(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_card_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS engagement_impressions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    card_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    customer_session_id BIGINT UNSIGNED NOT NULL,
    remaining_minutes SMALLINT UNSIGNED NULL,
    first_shown_at DATETIME NOT NULL,
    last_shown_at DATETIME NOT NULL,
    show_count INT UNSIGNED NOT NULL DEFAULT 1,
    UNIQUE KEY uq_engagement_impression_public (public_id),
    UNIQUE KEY uq_engagement_impression_context (card_id,order_id,customer_session_id),
    INDEX idx_engagement_impression_hotel (hotel_id,first_shown_at),
    CONSTRAINT fk_engagement_impression_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_impression_card FOREIGN KEY (card_id) REFERENCES engagement_cards(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_impression_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_impression_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS engagement_clicks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    impression_id BIGINT UNSIGNED NOT NULL,
    customer_session_id BIGINT UNSIGNED NOT NULL,
    action VARCHAR(80) NULL,
    target_url VARCHAR(500) NULL,
    clicked_at DATETIME NOT NULL,
    UNIQUE KEY uq_engagement_click_public (public_id),
    INDEX idx_engagement_click_hotel (hotel_id,clicked_at),
    INDEX idx_engagement_click_impression (impression_id,clicked_at),
    CONSTRAINT fk_engagement_click_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_click_impression FOREIGN KEY (impression_id) REFERENCES engagement_impressions(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_click_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS engagement_conversions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    impression_id BIGINT UNSIGNED NOT NULL,
    customer_session_id BIGINT UNSIGNED NOT NULL,
    conversion_type ENUM('ADD_TO_CART','RESERVATION_CONFIRMED') NOT NULL,
    resource_public_id VARCHAR(64) NULL,
    converted_at DATETIME NOT NULL,
    UNIQUE KEY uq_engagement_conversion_public (public_id),
    UNIQUE KEY uq_engagement_conversion_once (impression_id,conversion_type,resource_public_id),
    INDEX idx_engagement_conversion_hotel (hotel_id,converted_at),
    CONSTRAINT fk_engagement_conversion_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_conversion_impression FOREIGN KEY (impression_id) REFERENCES engagement_impressions(id) ON DELETE CASCADE,
    CONSTRAINT fk_engagement_conversion_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO permissions (code,name) VALUES
('promotions.view','View promotions, hotel events and engagement analytics'),
('promotions.manage','Manage promotions, hotel events and waiting-time cards')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN') AND p.code IN ('promotions.view','promotions.manage');

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code IN ('RESTAURANT_MANAGER','ROOM_SERVICE_MANAGER') AND p.code IN ('promotions.view','promotions.manage');

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code='READ_ONLY_MANAGER' AND p.code='promotions.view';