-- =====================================================================
-- 007: merge tables — several dine-in tables share ONE session (orders,
--      shared cart, service requests and a single combined bill).
-- =====================================================================

-- Which tables belong to a session (the session's own table = 'primary'; joined tables = 'merged')
CREATE TABLE session_tables (
  table_session_id INT UNSIGNED NOT NULL,
  table_id         INT UNSIGNED NOT NULL,
  restaurant_id    INT UNSIGNED NOT NULL,
  role             ENUM('primary','merged') NOT NULL,
  joined_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  left_at          DATETIME NULL,                              -- unmerged / session closed
  PRIMARY KEY (table_session_id, table_id),
  KEY idx_session_tables_table (table_id, left_at),
  CONSTRAINT fk_session_tables_session FOREIGN KEY (table_session_id) REFERENCES table_sessions (id) ON DELETE CASCADE,
  CONSTRAINT fk_session_tables_table   FOREIGN KEY (table_id)         REFERENCES dining_tables (id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A session that was folded into another one (its guests' tokens follow this pointer)
ALTER TABLE table_sessions
  ADD COLUMN merged_into_session_id INT UNSIGNED NULL AFTER status,
  ADD KEY idx_sessions_merged_into (merged_into_session_id);

-- Backfill: every existing session's own table is its primary member
INSERT INTO session_tables (table_session_id, table_id, restaurant_id, role, joined_at, left_at)
SELECT id, table_id, restaurant_id, 'primary', opened_at, closed_at FROM table_sessions;
