CREATE TABLE IF NOT EXISTS branches (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(120) NOT NULL,
  area          VARCHAR(120) NOT NULL,
  address       VARCHAR(255) NOT NULL,
  phone         VARCHAR(20)  NOT NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bookings (
  id                   INT AUTO_INCREMENT PRIMARY KEY,
  booking_ref          VARCHAR(20) NOT NULL UNIQUE,       -- e.g. OS-2026-0001
  customer_name        VARCHAR(120) NOT NULL,
  phone                VARCHAR(20)  NOT NULL,
  email                VARCHAR(150) NULL,
  branch_id            INT NOT NULL,
  booking_mode         ENUM('dine_in','takeaway') NOT NULL DEFAULT 'dine_in',
  pickup_date          DATE NOT NULL,
  pickup_slot          VARCHAR(40) NOT NULL,               -- e.g. "11:00 AM - 12:00 PM"
  pack_type            ENUM('kids','adult','takeaway') NOT NULL DEFAULT 'adult',
  quantity             INT NOT NULL DEFAULT 1,
  price_per_plate      DECIMAL(10,2) NOT NULL,
  amount_total          DECIMAL(10,2) NOT NULL,             -- price_per_plate * quantity
  notes                VARCHAR(255) NULL,
  razorpay_order_id    VARCHAR(64) NULL,
  razorpay_payment_id  VARCHAR(64) NULL,
  razorpay_signature   VARCHAR(128) NULL,
  payment_status       ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
  created_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_booking_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- If you already ran this schema before this update, migrate an existing table with:
-- ALTER TABLE bookings
--   ADD COLUMN booking_mode ENUM('dine_in','takeaway') NOT NULL DEFAULT 'dine_in' AFTER branch_id,
--   ADD COLUMN pack_type ENUM('kids','adult','takeaway') NOT NULL DEFAULT 'adult' AFTER pickup_slot;

-- Seed branches (edit to match your client's real locations)
INSERT INTO branches (name, area, address, phone) VALUES
('Sri Madhuram - Tirunelveli', 'Tirunelveli',
  'Palayamkottai (Opp SBI Hospital), Tirunelveli', '9944832020'),
('Sri Madhuram - Tenkasi', 'Tenkasi',
  'Old Theatre Road, Courtallam Road, Tenkasi', '9384816680')
ON DUPLICATE KEY UPDATE name = VALUES(name);