-- ============================================================
-- NaijaHospitality OS — Full Database Schema
-- Eworldlab International | MySQL 8.0+
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
SET time_zone = '+01:00';

-- ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS setup_tokens (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  token      VARCHAR(64) NOT NULL UNIQUE,
  email      VARCHAR(150) NOT NULL,
  plan       ENUM('starter','professional','enterprise') DEFAULT 'starter',
  used       TINYINT(1) DEFAULT 0,
  used_at    DATETIME,
  expires_at DATETIME NOT NULL,
  created_by INT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clients (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(150) NOT NULL,
  slug          VARCHAR(80)  NOT NULL UNIQUE,
  email         VARCHAR(150) NOT NULL UNIQUE,
  phone         VARCHAR(20),
  address       TEXT,
  state         ENUM('Lagos','FCT','Rivers','Kano','Oyo','Enugu','Delta','Kwara','Others') DEFAULT 'Lagos',
  lga           VARCHAR(80),
  rc_number     VARCHAR(30),
  tin           VARCHAR(20),
  logo_path     VARCHAR(255),
  plan          ENUM('starter','professional','enterprise') DEFAULT 'starter',
  is_active     TINYINT(1) DEFAULT 1,
  trial_ends_at DATE,
  ndpr_accepted TINYINT(1) DEFAULT 0,
  ndpr_date     DATETIME,
  ndpr_signatory VARCHAR(150),
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS properties (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id           INT UNSIGNED NOT NULL,
  name                VARCHAR(150) NOT NULL,
  type                ENUM('hotel','bar','restaurant','canteen','mixed') NOT NULL,
  address             TEXT,
  state               VARCHAR(50),
  lga                 VARCHAR(80),
  phone               VARCHAR(20),
  email               VARCHAR(150),
  hotel_star          TINYINT DEFAULT NULL,
  ntdc_number         VARCHAR(50),
  modules             JSON NOT NULL,
  levy_config         JSON,
  vat_enabled         TINYINT(1) DEFAULT 1,
  service_charge_pct  DECIMAL(5,2) DEFAULT 10.00,
  timezone            VARCHAR(50) DEFAULT 'Africa/Lagos',
  currency_code       CHAR(3) DEFAULT 'NGN',
  receipt_footer      TEXT,
  is_active           TINYINT(1) DEFAULT 1,
  created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  INDEX idx_client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_settings (
  id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id             INT UNSIGNED NOT NULL,
  property_id           INT UNSIGNED NOT NULL,
  paystack_public_key   VARCHAR(100),
  paystack_secret_key   VARCHAR(100),
  monnify_api_key       VARCHAR(100),
  monnify_secret_key    VARCHAR(100),
  monnify_contract_code VARCHAR(100),
  momo_subscription_key VARCHAR(100),
  momo_api_user         VARCHAR(100),
  momo_api_key          VARCHAR(100),
  bank_account_name     VARCHAR(150),
  bank_account_number   VARCHAR(20),
  bank_name             VARCHAR(100),
  created_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_property (property_id),
  FOREIGN KEY (client_id)   REFERENCES clients(id),
  FOREIGN KEY (property_id) REFERENCES properties(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id       INT UNSIGNED NOT NULL,
  property_id     INT UNSIGNED,
  full_name       VARCHAR(150) NOT NULL,
  email           VARCHAR(150) NOT NULL,
  phone           VARCHAR(20),
  password_hash   VARCHAR(255) NOT NULL,
  role            ENUM('super_admin','client_owner','property_manager','front_desk',
                       'bartender','waiter','housekeeper','accountant','kitchen') NOT NULL,
  is_active       TINYINT(1) DEFAULT 1,
  last_login      DATETIME,
  login_attempts  TINYINT DEFAULT 0,
  locked_until    DATETIME,
  ndpr_consent    TINYINT(1) DEFAULT 0,
  ndpr_consent_at DATETIME,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_email_client (email, client_id),
  FOREIGN KEY (client_id)   REFERENCES clients(id),
  FOREIGN KEY (property_id) REFERENCES properties(id),
  INDEX idx_client_role (client_id, role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS guests (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id        INT UNSIGNED NOT NULL,
  full_name        VARCHAR(150) NOT NULL,
  email            VARCHAR(150),
  phone            VARCHAR(20) NOT NULL,
  id_type          ENUM('nin','voters_card','drivers_license','passport','others'),
  id_number        VARCHAR(100),
  id_image_path    VARCHAR(255),
  nationality      VARCHAR(80) DEFAULT 'Nigerian',
  dob              DATE,
  gender           ENUM('male','female','other'),
  address          TEXT,
  city             VARCHAR(80),
  state            VARCHAR(80),
  vip              TINYINT(1) DEFAULT 0,
  blacklisted      TINYINT(1) DEFAULT 0,
  blacklist_reason TEXT,
  ndpr_consent     TINYINT(1) DEFAULT 0,
  ndpr_consent_at  DATETIME,
  ndpr_consent_ip  VARCHAR(45),
  marketing_optin  TINYINT(1) DEFAULT 0,
  visit_count      INT UNSIGNED DEFAULT 0,
  total_spent      DECIMAL(14,2) DEFAULT 0,
  notes            TEXT,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id),
  INDEX idx_phone_client (phone, client_id),
  INDEX idx_client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS room_types (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id     INT UNSIGNED NOT NULL,
  property_id   INT UNSIGNED NOT NULL,
  name          VARCHAR(100) NOT NULL,
  description   TEXT,
  base_rate     DECIMAL(12,2) NOT NULL,
  weekend_rate  DECIMAL(12,2),
  max_occupancy TINYINT DEFAULT 2,
  amenities     JSON,
  image_path    VARCHAR(255),
  sort_order    TINYINT DEFAULT 0,
  is_active     TINYINT(1) DEFAULT 1,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_prop (property_id, client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rooms (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id      INT UNSIGNED NOT NULL,
  property_id    INT UNSIGNED NOT NULL,
  room_type_id   INT UNSIGNED NOT NULL,
  room_number    VARCHAR(20) NOT NULL,
  floor          TINYINT,
  wing           VARCHAR(30),
  status         ENUM('available','occupied','dirty','maintenance','blocked') DEFAULT 'available',
  hk_status      ENUM('clean','dirty','in_progress','inspected') DEFAULT 'clean',
  hk_assigned_to INT UNSIGNED,
  notes          TEXT,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_room (property_id, room_number),
  FOREIGN KEY (room_type_id) REFERENCES room_types(id),
  FOREIGN KEY (client_id)    REFERENCES clients(id),
  FOREIGN KEY (property_id)  REFERENCES properties(id),
  INDEX idx_status (property_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bookings (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id        INT UNSIGNED NOT NULL,
  property_id      INT UNSIGNED NOT NULL,
  room_id          INT UNSIGNED NOT NULL,
  guest_id         INT UNSIGNED NOT NULL,
  booking_ref      VARCHAR(25) NOT NULL UNIQUE,
  check_in_date    DATE NOT NULL,
  check_out_date   DATE NOT NULL,
  actual_check_in  DATETIME,
  actual_check_out DATETIME,
  adults           TINYINT DEFAULT 1,
  children         TINYINT DEFAULT 0,
  rate_per_night   DECIMAL(12,2) NOT NULL,
  total_nights     TINYINT UNSIGNED,
  status           ENUM('pending','confirmed','checked_in','checked_out','cancelled','no_show') DEFAULT 'pending',
  source           ENUM('walk_in','phone','online','agent','corporate') DEFAULT 'walk_in',
  corporate_name   VARCHAR(150),
  special_requests TEXT,
  deposit_amount   DECIMAL(12,2) DEFAULT 0,
  deposit_paid     TINYINT(1) DEFAULT 0,
  checked_in_by    INT UNSIGNED,
  checked_out_by   INT UNSIGNED,
  created_by       INT UNSIGNED,
  cancel_reason    TEXT,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (room_id)   REFERENCES rooms(id),
  FOREIGN KEY (guest_id)  REFERENCES guests(id),
  FOREIGN KEY (client_id) REFERENCES clients(id),
  INDEX idx_dates  (property_id, check_in_date, check_out_date),
  INDEX idx_status (client_id, status),
  INDEX idx_guest  (guest_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dining_tables (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id        INT UNSIGNED NOT NULL,
  property_id      INT UNSIGNED NOT NULL,
  table_number     VARCHAR(20) NOT NULL,
  section          VARCHAR(50),
  capacity         TINYINT DEFAULT 4,
  status           ENUM('available','occupied','reserved','cleaning') DEFAULT 'available',
  current_order_id INT UNSIGNED,
  notes            TEXT,
  UNIQUE KEY uq_table (property_id, table_number),
  INDEX idx_status (property_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS menu_categories (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id   INT UNSIGNED NOT NULL,
  property_id INT UNSIGNED NOT NULL,
  name        VARCHAR(100) NOT NULL,
  module      ENUM('restaurant','bar','canteen','room_service') DEFAULT 'restaurant',
  sort_order  TINYINT DEFAULT 0,
  is_active   TINYINT(1) DEFAULT 1,
  INDEX idx_prop (property_id, client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS menu_items (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id        INT UNSIGNED NOT NULL,
  property_id      INT UNSIGNED NOT NULL,
  category_id      INT UNSIGNED NOT NULL,
  name             VARCHAR(150) NOT NULL,
  description      TEXT,
  price            DECIMAL(12,2) NOT NULL,
  cost_price       DECIMAL(12,2),
  image_path       VARCHAR(255),
  is_age_restricted TINYINT(1) DEFAULT 0,
  min_age          TINYINT DEFAULT 18,
  prep_time_mins   TINYINT DEFAULT 10,
  is_available     TINYINT(1) DEFAULT 1,
  is_active        TINYINT(1) DEFAULT 1,
  sort_order       SMALLINT DEFAULT 0,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES menu_categories(id),
  INDEX idx_prop_cat (property_id, category_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_items (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id         INT UNSIGNED NOT NULL,
  property_id       INT UNSIGNED NOT NULL,
  name              VARCHAR(150) NOT NULL,
  sku               VARCHAR(50),
  category          VARCHAR(80),
  unit              VARCHAR(20) DEFAULT 'pcs',
  current_qty       DECIMAL(10,3) DEFAULT 0,
  reorder_level     DECIMAL(10,3) DEFAULT 5,
  cost_price        DECIMAL(12,2),
  is_age_restricted TINYINT(1) DEFAULT 0,
  notes             TEXT,
  updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sku_prop (property_id, sku),
  INDEX idx_prop (property_id, client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_movements (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id   INT UNSIGNED NOT NULL,
  property_id INT UNSIGNED NOT NULL,
  item_id     INT UNSIGNED NOT NULL,
  type        ENUM('purchase','sale','adjustment','wastage','transfer') NOT NULL,
  qty         DECIMAL(10,3) NOT NULL,
  unit_cost   DECIMAL(12,2),
  reference   VARCHAR(50),
  notes       TEXT,
  created_by  INT UNSIGNED,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (item_id) REFERENCES stock_items(id),
  INDEX idx_item_date (item_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id    INT UNSIGNED NOT NULL,
  property_id  INT UNSIGNED NOT NULL,
  order_ref    VARCHAR(25) UNIQUE,
  order_type   ENUM('dine_in','takeaway','room_service','bar_tab','canteen') NOT NULL,
  guest_id     INT UNSIGNED,
  booking_id   INT UNSIGNED,
  table_id     INT UNSIGNED,
  waiter_id    INT UNSIGNED,
  status       ENUM('open','sent_to_kitchen','ready','served','billed','cancelled') DEFAULT 'open',
  priority     ENUM('normal','rush') DEFAULT 'normal',
  notes        TEXT,
  cancel_reason TEXT,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_prop_status (property_id, status),
  INDEX idx_booking     (booking_id),
  INDEX idx_table       (table_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id       INT UNSIGNED NOT NULL,
  client_id      INT UNSIGNED NOT NULL,
  menu_item_id   INT UNSIGNED,
  name           VARCHAR(150) NOT NULL,
  qty            DECIMAL(8,2) NOT NULL,
  unit_price     DECIMAL(12,2) NOT NULL,
  discount_pct   DECIMAL(5,2) DEFAULT 0,
  modifiers      JSON,
  notes          VARCHAR(255),
  kitchen_status ENUM('pending','in_progress','ready','served') DEFAULT 'pending',
  kitchen_time   DATETIME,
  ready_time     DATETIME,
  served_time    DATETIME,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  INDEX idx_order   (order_id),
  INDEX idx_kitchen (order_id, kitchen_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id       INT UNSIGNED NOT NULL,
  property_id     INT UNSIGNED NOT NULL,
  invoice_number  VARCHAR(30) UNIQUE,
  guest_id        INT UNSIGNED,
  booking_id      INT UNSIGNED,
  subtotal        DECIMAL(14,2) DEFAULT 0,
  discount_amount DECIMAL(14,2) DEFAULT 0,
  discount_reason VARCHAR(255),
  service_charge  DECIMAL(14,2) DEFAULT 0,
  vat_amount      DECIMAL(14,2) DEFAULT 0,
  levy_amount     DECIMAL(14,2) DEFAULT 0,
  total_amount    DECIMAL(14,2) DEFAULT 0,
  amount_paid     DECIMAL(14,2) DEFAULT 0,
  balance_due     DECIMAL(14,2) DEFAULT 0,
  currency        CHAR(3) DEFAULT 'NGN',
  status          ENUM('draft','issued','partially_paid','paid','cancelled','refunded') DEFAULT 'draft',
  due_date        DATE,
  paid_at         DATETIME,
  notes           TEXT,
  created_by      INT UNSIGNED,
  approved_by     INT UNSIGNED,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_prop   (property_id, client_id),
  INDEX idx_status (client_id, status),
  INDEX idx_guest  (guest_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_items (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id  INT UNSIGNED NOT NULL,
  client_id   INT UNSIGNED NOT NULL,
  source_type ENUM('booking','order','manual') NOT NULL,
  source_id   INT UNSIGNED,
  description VARCHAR(255) NOT NULL,
  qty         DECIMAL(8,2) DEFAULT 1,
  unit_price  DECIMAL(12,2) NOT NULL,
  total       DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id        INT UNSIGNED NOT NULL,
  property_id      INT UNSIGNED NOT NULL,
  invoice_id       INT UNSIGNED NOT NULL,
  amount           DECIMAL(14,2) NOT NULL,
  method           ENUM('cash','paystack','monnify','mtn_momo','airtel_smartcash','bank_transfer','pos_terminal','complimentary') NOT NULL,
  gateway_ref      VARCHAR(150),
  gateway_status   VARCHAR(30),
  gateway_response JSON,
  verified         TINYINT(1) DEFAULT 0,
  received_by      INT UNSIGNED,
  paid_at          DATETIME,
  notes            TEXT,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id),
  INDEX idx_gateway_ref (gateway_ref),
  INDEX idx_client      (client_id),
  INDEX idx_invoice     (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS housekeeping_log (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id   INT UNSIGNED NOT NULL,
  property_id INT UNSIGNED NOT NULL,
  room_id     INT UNSIGNED NOT NULL,
  staff_id    INT UNSIGNED,
  action      ENUM('start_cleaning','finish_cleaning','inspection_pass','inspection_fail','maintenance_request') NOT NULL,
  notes       TEXT,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_room (room_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ndpr_audit_log (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id    INT UNSIGNED NOT NULL,
  user_id      INT UNSIGNED,
  subject_type ENUM('guest','staff','system'),
  subject_id   INT UNSIGNED,
  action       ENUM('view','export','delete','consent_given','consent_revoked','breach_reported','data_rectified','dsar_request') NOT NULL,
  ip_address   VARCHAR(45),
  user_agent   TEXT,
  notes        TEXT,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_client (client_id),
  INDEX idx_action (action, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sms_log (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id  INT UNSIGNED NOT NULL,
  phone      VARCHAR(20) NOT NULL,
  message    TEXT NOT NULL,
  provider   ENUM('termii','bulksms') DEFAULT 'termii',
  status     ENUM('sent','failed','pending') DEFAULT 'pending',
  response   TEXT,
  sent_at    DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_client (client_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Sample data ───────────────────────────────────────────
INSERT INTO clients (name,slug,email,phone,address,state,rc_number,tin,ndpr_accepted,ndpr_date,ndpr_signatory) VALUES
  ('Grand Continental Hotel','grandcontinental','admin@grandcontinental.ng','08012345678','15 Victoria Island, Lagos','Lagos','RC1234567','TIN12345678',1,NOW(),'John Doe');

INSERT INTO properties (client_id,name,type,address,state,lga,phone,modules,vat_enabled,service_charge_pct,ntdc_number) VALUES
  (1,'Grand Continental - Main','mixed','15 Victoria Island','Lagos','Lagos Island','08012345678','["hotel","bar","restaurant"]',1,10.00,'NTDC/LAG/2024/001');

-- Password: Admin@1234 (bcrypt hash — regenerate in production)
INSERT INTO users (client_id,property_id,full_name,email,phone,password_hash,role,ndpr_consent,ndpr_consent_at) VALUES
  (1,NULL,'Chukwuemeka Obi','admin@grandcontinental.ng','08012345678','$2y$12$placeholder.hash.replace.in.production.AAAA','client_owner',1,NOW()),
  (1,1,'Amina Yusuf','frontdesk@grandcontinental.ng','08098765432','$2y$12$placeholder.hash.replace.in.production.BBBB','front_desk',1,NOW()),
  (1,1,'Taiwo Adeleke','kitchen@grandcontinental.ng','07011223344','$2y$12$placeholder.hash.replace.in.production.CCCC','kitchen',1,NOW());

INSERT INTO room_types (client_id,property_id,name,description,base_rate,weekend_rate,max_occupancy,amenities) VALUES
  (1,1,'Standard Room','Comfortable room with en-suite',35000.00,40000.00,2,'["WiFi","TV","AC","Hot Water","Breakfast"]'),
  (1,1,'Deluxe Room','Spacious room with city view',55000.00,65000.00,2,'["WiFi","Smart TV","AC","Minibar","Breakfast","City View"]'),
  (1,1,'Executive Suite','Luxury suite with kitchenette',95000.00,110000.00,3,'["WiFi","Smart TV","AC","Minibar","Kitchenette","Breakfast","Lounge Access"]');

INSERT INTO rooms (client_id,property_id,room_type_id,room_number,floor,status,hk_status) VALUES
  (1,1,1,'101',1,'available','clean'),(1,1,1,'102',1,'available','clean'),
  (1,1,1,'103',1,'occupied','dirty'),(1,1,2,'201',2,'available','clean'),
  (1,1,2,'202',2,'available','inspected'),(1,1,3,'301',3,'available','clean');

INSERT INTO dining_tables (client_id,property_id,table_number,section,capacity,status) VALUES
  (1,1,'T1','Main Hall',4,'available'),(1,1,'T2','Main Hall',4,'available'),
  (1,1,'T3','Outdoor',2,'available'),(1,1,'VIP1','VIP Lounge',8,'available');

INSERT INTO menu_categories (client_id,property_id,name,module) VALUES
  (1,1,'Starters','restaurant'),(1,1,'Main Course','restaurant'),
  (1,1,'Desserts','restaurant'),(1,1,'Cocktails','bar'),
  (1,1,'Beers & Spirits','bar'),(1,1,'Soft Drinks','bar');

INSERT INTO menu_items (client_id,property_id,category_id,name,price,cost_price,is_age_restricted,prep_time_mins) VALUES
  (1,1,1,'Peppered Snail',3500.00,1200.00,0,15),(1,1,1,'Suya Platter',4500.00,1500.00,0,20),
  (1,1,2,'Jollof Rice & Chicken',5500.00,1800.00,0,25),(1,1,2,'Egusi Soup & Pounded Yam',6000.00,2000.00,0,30),
  (1,1,2,'Catfish Pepper Soup',7500.00,2500.00,0,20),(1,1,3,'Chin-Chin & Ice Cream',2500.00,800.00,0,5),
  (1,1,4,'Chapman',2500.00,600.00,0,5),(1,1,4,'Pina Colada',4500.00,1200.00,1,8),
  (1,1,5,'Star Beer (Bottle)',1500.00,600.00,1,2),(1,1,5,'Hennessy VS 50cl',18000.00,9000.00,1,2),
  (1,1,6,'Coca-Cola',500.00,200.00,0,1),(1,1,6,'Malta Guinness',700.00,250.00,0,1);

INSERT INTO guests (client_id,full_name,email,phone,id_type,id_number,nationality,ndpr_consent,ndpr_consent_at,marketing_optin) VALUES
  (1,'Olumide Fashola','olumide@email.com','08023456789','nin','12345678901','Nigerian',1,NOW(),1),
  (1,'Ngozi Okafor','ngozi@company.ng','07034567890','passport','A12345678','Nigerian',1,NOW(),0);

SET FOREIGN_KEY_CHECKS = 1;