-- =====================================================================
-- 006: visual floor plans — floors per location, decor elements, and a
--      position / size / shape / rotation for every placed table.
-- Coordinates are canvas units (top-left origin) inside the floor's width × height.
-- =====================================================================

CREATE TABLE floors (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id INT UNSIGNED NOT NULL,
  location_id   INT UNSIGNED NOT NULL,
  name          VARCHAR(80)  NOT NULL,                     -- "Ground floor", "First floor", "Rooftop"
  sort_order    INT NOT NULL DEFAULT 0,
  width         SMALLINT UNSIGNED NOT NULL DEFAULT 1200,   -- canvas units
  height        SMALLINT UNSIGNED NOT NULL DEFAULT 800,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_floors_location_name (location_id, name),
  KEY idx_floors_restaurant (restaurant_id, location_id, sort_order),
  CONSTRAINT fk_floors_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_floors_location   FOREIGN KEY (location_id)   REFERENCES locations (id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Non-table shapes drawn on a floor (walls, doors, counters, kitchen, washroom, plants, text labels…)
CREATE TABLE floor_elements (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id INT UNSIGNED NOT NULL,
  floor_id      INT UNSIGNED NOT NULL,
  kind          ENUM('wall','door','window','counter','bar','kitchen','washroom','stairs','plant','pillar','label','zone') NOT NULL,
  label         VARCHAR(60)  NULL,
  x             INT NOT NULL,
  y             INT NOT NULL,
  width         INT UNSIGNED NOT NULL,
  height        INT UNSIGNED NOT NULL,
  rotation      SMALLINT NOT NULL DEFAULT 0,               -- degrees 0..359
  color         VARCHAR(20)  NULL,                         -- hex override, NULL = kind default
  sort_order    INT NOT NULL DEFAULT 0,                    -- draw order (higher = on top)
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_floor_elements_floor (floor_id),
  CONSTRAINT fk_floor_elements_floor FOREIGN KEY (floor_id) REFERENCES floors (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A table is "placed" when it has a floor and layout values
ALTER TABLE dining_tables
  ADD COLUMN floor_id        INT UNSIGNED NULL AFTER location_id,
  ADD COLUMN layout_x        INT NULL AFTER sort_order,
  ADD COLUMN layout_y        INT NULL AFTER layout_x,
  ADD COLUMN layout_width    INT UNSIGNED NULL AFTER layout_y,
  ADD COLUMN layout_height   INT UNSIGNED NULL AFTER layout_width,
  ADD COLUMN layout_shape    ENUM('square','rectangle','round') NULL AFTER layout_height,
  ADD COLUMN layout_rotation SMALLINT NOT NULL DEFAULT 0 AFTER layout_shape,
  ADD KEY idx_tables_floor (floor_id),
  ADD CONSTRAINT fk_tables_floor FOREIGN KEY (floor_id) REFERENCES floors (id) ON DELETE SET NULL;
