-- Restaurant POS foundation tables: areas, tables, sessions, and table events

CREATE TABLE IF NOT EXISTS restaurant_areas (
  id TEXT PRIMARY KEY,
  area_code TEXT,
  name TEXT NOT NULL,
  display_order INTEGER NOT NULL DEFAULT 0,
  color_hex TEXT,
  inactive BOOLEAN NOT NULL DEFAULT FALSE,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS restaurant_tables (
  id TEXT PRIMARY KEY,
  table_code TEXT,
  name TEXT NOT NULL,
  area_id TEXT,
  seats_count INTEGER NOT NULL DEFAULT 4,
  status TEXT NOT NULL DEFAULT 'available',
  inactive BOOLEAN NOT NULL DEFAULT FALSE,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_restaurant_tables_area FOREIGN KEY (area_id) REFERENCES restaurant_areas(id) ON DELETE SET NULL,
  CONSTRAINT chk_restaurant_tables_status CHECK (status IN ('available', 'occupied', 'reserved', 'cleaning', 'out_of_service'))
);

CREATE TABLE IF NOT EXISTS restaurant_order_sessions (
  id TEXT PRIMARY KEY,
  session_number TEXT NOT NULL,
  channel TEXT NOT NULL DEFAULT 'dine_in',
  area_id TEXT,
  table_id TEXT,
  invoice_id TEXT,
  guest_count INTEGER NOT NULL DEFAULT 1,
  status TEXT NOT NULL DEFAULT 'open',
  started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  closed_at TIMESTAMPTZ,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_restaurant_sessions_area FOREIGN KEY (area_id) REFERENCES restaurant_areas(id) ON DELETE SET NULL,
  CONSTRAINT fk_restaurant_sessions_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE SET NULL,
  CONSTRAINT fk_restaurant_sessions_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
  CONSTRAINT chk_restaurant_sessions_channel CHECK (channel IN ('dine_in', 'takeaway', 'delivery')),
  CONSTRAINT chk_restaurant_sessions_status CHECK (status IN ('open', 'in_preparation', 'ready', 'served', 'partially_paid', 'closed', 'cancelled'))
);

CREATE TABLE IF NOT EXISTS restaurant_table_events (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  table_id TEXT,
  event_type TEXT NOT NULL,
  event_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  actor_name TEXT,
  actor_id TEXT,
  reason TEXT,
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_restaurant_table_events_session FOREIGN KEY (session_id) REFERENCES restaurant_order_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_restaurant_table_events_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE SET NULL,
  CONSTRAINT chk_restaurant_table_events_type CHECK (event_type IN ('session_opened', 'table_assigned', 'table_merged', 'item_transferred', 'bill_split', 'partial_checkout', 'session_closed', 'session_cancelled'))
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_restaurant_areas_name ON restaurant_areas(name);
CREATE UNIQUE INDEX IF NOT EXISTS uq_restaurant_tables_name ON restaurant_tables(name);
CREATE UNIQUE INDEX IF NOT EXISTS uq_restaurant_sessions_number ON restaurant_order_sessions(session_number);

CREATE INDEX IF NOT EXISTS idx_restaurant_tables_area_status ON restaurant_tables(area_id, status);
CREATE INDEX IF NOT EXISTS idx_restaurant_sessions_table_status ON restaurant_order_sessions(table_id, status);
CREATE INDEX IF NOT EXISTS idx_restaurant_sessions_channel_status ON restaurant_order_sessions(channel, status);
CREATE INDEX IF NOT EXISTS idx_restaurant_table_events_session_at ON restaurant_table_events(session_id, event_at DESC);