CREATE TABLE IF NOT EXISTS reservation_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    slot_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    duration_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 90,
    turnover_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 15,
    hold_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 5,
    minimum_party_size SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    maximum_party_size SMALLINT UNSIGNED NOT NULL DEFAULT 20,
    advance_days SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    max_combined_tables TINYINT UNSIGNED NOT NULL DEFAULT 3,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_reservation_settings_restaurant (restaurant_id),
    CONSTRAINT fk_reservation_settings_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
    CONSTRAINT chk_reservation_settings_slot CHECK (slot_minutes BETWEEN 5 AND 240),
    CONSTRAINT chk_reservation_settings_duration CHECK (duration_minutes BETWEEN 15 AND 720),
    CONSTRAINT chk_reservation_settings_party CHECK (minimum_party_size BETWEEN 1 AND maximum_party_size),
    CONSTRAINT chk_reservation_settings_combined CHECK (max_combined_tables BETWEEN 1 AND 6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restaurant_reservations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    reservation_number VARCHAR(40) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    room_session_id BIGINT UNSIGNED NULL,
    customer_session_id BIGINT UNSIGNED NULL,
    guest_name VARCHAR(160) NULL,
    guest_phone VARCHAR(50) NULL,
    guest_email VARCHAR(190) NULL,
    guest_count SMALLINT UNSIGNED NOT NULL,
    start_at DATETIME NOT NULL,
    end_at DATETIME NOT NULL,
    status ENUM('HOLD','PENDING','CONFIRMED','SEATED','COMPLETED','CANCELLED','NO_SHOW','EXPIRED') NOT NULL DEFAULT 'CONFIRMED',
    source ENUM('ROOM_QR','PUBLIC','STAFF','API') NOT NULL DEFAULT 'ROOM_QR',
    notes VARCHAR(500) NULL,
    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_reservation_public_id (public_id),
    UNIQUE KEY uq_reservation_number (hotel_id,reservation_number),
    INDEX idx_reservation_restaurant_window (restaurant_id,status,start_at,end_at),
    INDEX idx_reservation_customer (customer_session_id,created_at),
    INDEX idx_reservation_room (room_session_id,created_at),
    CONSTRAINT fk_reservation_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE RESTRICT,
    CONSTRAINT fk_reservation_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE RESTRICT,
    CONSTRAINT fk_reservation_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE SET NULL,
    CONSTRAINT fk_reservation_customer_session FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL,
    CONSTRAINT fk_reservation_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT chk_reservation_guest_count CHECK (guest_count BETWEEN 1 AND 200),
    CONSTRAINT chk_reservation_window CHECK (end_at > start_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reservation_holds (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    hotel_id BIGINT UNSIGNED NOT NULL,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    room_session_id BIGINT UNSIGNED NULL,
    customer_session_id BIGINT UNSIGNED NULL,
    guest_count SMALLINT UNSIGNED NOT NULL,
    start_at DATETIME NOT NULL,
    end_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    status ENUM('ACTIVE','CONFIRMED','EXPIRED','CANCELLED') NOT NULL DEFAULT 'ACTIVE',
    confirmed_reservation_id 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_reservation_hold_public (public_id),
    UNIQUE KEY uq_reservation_hold_confirmed (confirmed_reservation_id),
    INDEX idx_reservation_hold_window (restaurant_id,status,start_at,end_at,expires_at),
    INDEX idx_reservation_hold_customer (customer_session_id,status,expires_at),
    CONSTRAINT fk_reservation_hold_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
    CONSTRAINT fk_reservation_hold_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
    CONSTRAINT fk_reservation_hold_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_reservation_hold_customer_session FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_reservation_hold_confirmed FOREIGN KEY (confirmed_reservation_id) REFERENCES restaurant_reservations(id) ON DELETE SET NULL,
    CONSTRAINT chk_reservation_hold_window CHECK (end_at > start_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reservation_hold_tables (
    reservation_hold_id BIGINT UNSIGNED NOT NULL,
    table_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (reservation_hold_id,table_id),
    INDEX idx_reservation_hold_table_lookup (table_id,reservation_hold_id),
    CONSTRAINT fk_reservation_hold_tables_hold FOREIGN KEY (reservation_hold_id) REFERENCES reservation_holds(id) ON DELETE CASCADE,
    CONSTRAINT fk_reservation_hold_tables_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reservation_tables (
    reservation_id BIGINT UNSIGNED NOT NULL,
    table_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (reservation_id,table_id),
    INDEX idx_reservation_table_lookup (table_id,reservation_id),
    CONSTRAINT fk_reservation_tables_reservation FOREIGN KEY (reservation_id) REFERENCES restaurant_reservations(id) ON DELETE CASCADE,
    CONSTRAINT fk_reservation_tables_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reservation_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reservation_id BIGINT UNSIGNED NOT NULL,
    old_status VARCHAR(40) NULL,
    new_status VARCHAR(40) NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    customer_session_id BIGINT UNSIGNED NULL,
    notes VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_reservation_history (reservation_id,created_at),
    CONSTRAINT fk_reservation_history_reservation FOREIGN KEY (reservation_id) REFERENCES restaurant_reservations(id) ON DELETE CASCADE,
    CONSTRAINT fk_reservation_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_reservation_history_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @reservation_fk_exists := (SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=DATABASE() AND CONSTRAINT_NAME='fk_orders_reservation');
SET @reservation_fk_sql := IF(@reservation_fk_exists=0,'ALTER TABLE orders ADD CONSTRAINT fk_orders_reservation FOREIGN KEY (reservation_id) REFERENCES restaurant_reservations(id) ON DELETE SET NULL','SELECT 1');
PREPARE reservation_fk_stmt FROM @reservation_fk_sql;
EXECUTE reservation_fk_stmt;
DEALLOCATE PREPARE reservation_fk_stmt;

INSERT INTO reservation_settings (restaurant_id,enabled,slot_minutes,duration_minutes,turnover_minutes,hold_minutes,minimum_party_size,maximum_party_size,advance_days,max_combined_tables)
SELECT r.id,1,30,90,15,5,1,20,30,3 FROM restaurants r WHERE r.type IN ('RESTAURANT','CAFE')
ON DUPLICATE KEY UPDATE restaurant_id=VALUES(restaurant_id);

INSERT INTO permissions (code,name) VALUES
('reservations.view','View restaurant reservations'),
('reservations.manage','Manage restaurant reservations'),
('reservations.settings','Configure reservation settings')
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','RESTAURANT_MANAGER')
  AND p.code IN ('reservations.view','reservations.manage','reservations.settings');

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 ('WAITER','ROOM_SERVICE_MANAGER','READ_ONLY_MANAGER')
  AND p.code='reservations.view';

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