-- Risely — full catalog + partner accounts
-- Prefer running: http://127.0.0.1/localplate/api/install_seed.php
-- Or import this file, then import sql/seed.sql in phpMyAdmin.

CREATE DATABASE IF NOT EXISTS localplate
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE localplate;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS popular_meals;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS restaurants;
DROP TABLE IF EXISTS categories;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  kind VARCHAR(40) NOT NULL,
  places INT UNSIGNED NOT NULL DEFAULT 0,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_categories_name (name),
  UNIQUE KEY uq_categories_kind (kind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE restaurants (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_code VARCHAR(16) NOT NULL,
  restaurant_name VARCHAR(160) NOT NULL,
  location VARCHAR(255) NOT NULL,
  area VARCHAR(255) NOT NULL,
  about_restaurant TEXT NOT NULL,
  opening_hours TEXT NOT NULL,
  open_until VARCHAR(40) DEFAULT NULL,
  pickup_info TEXT NOT NULL,
  delivery_info TEXT NOT NULL,
  whatsapp_phone VARCHAR(32) NOT NULL,
  phone VARCHAR(32) NOT NULL,
  categories JSON NOT NULL,
  primary_category VARCHAR(80) NOT NULL,
  tag VARCHAR(120) NOT NULL DEFAULT '',
  eta VARCHAR(40) NOT NULL DEFAULT '',
  rating DECIMAL(2,1) NOT NULL DEFAULT 4.5,
  reviews INT UNSIGNED NOT NULL DEFAULT 0,
  popular_score INT UNSIGNED NOT NULL DEFAULT 0,
  is_open TINYINT(1) NOT NULL DEFAULT 1,
  cover_image VARCHAR(255) DEFAULT NULL,
  cover_asset VARCHAR(80) DEFAULT NULL,
  address TEXT NULL,
  username VARCHAR(80) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('pending', 'active', 'suspended', 'rejected') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_restaurants_public_code (public_code),
  UNIQUE KEY uq_restaurants_username (username),
  UNIQUE KEY uq_restaurants_whatsapp (whatsapp_phone),
  KEY idx_restaurants_primary_category (primary_category),
  KEY idx_restaurants_popular (popular_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE menu_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_code VARCHAR(32) NOT NULL,
  restaurant_id INT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  description TEXT NOT NULL,
  price INT UNSIGNED NOT NULL,
  menu_category VARCHAR(80) NOT NULL,
  is_popular TINYINT(1) NOT NULL DEFAULT 0,
  is_available TINYINT(1) NOT NULL DEFAULT 1,
  view_count INT UNSIGNED NOT NULL DEFAULT 0,
  image_asset VARCHAR(80) NOT NULL DEFAULT 'card-rice.jpg',
  image_url VARCHAR(255) DEFAULT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_menu_item_code (item_code),
  KEY idx_menu_restaurant (restaurant_id),
  KEY idx_menu_popular (is_popular),
  CONSTRAINT fk_menu_restaurant
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE popular_meals (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  menu_item_id INT UNSIGNED NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_popular_menu_item (menu_item_id),
  CONSTRAINT fk_popular_menu_item
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS buyers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(160) NOT NULL,
  phone VARCHAR(32) NOT NULL,
  location VARCHAR(255) NOT NULL,
  closest_landmark VARCHAR(255) NOT NULL DEFAULT '',
  username VARCHAR(80) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active', 'suspended') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_buyers_username (username),
  UNIQUE KEY uq_buyers_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_code VARCHAR(32) NOT NULL,
  restaurant_id INT UNSIGNED NOT NULL,
  buyer_id INT UNSIGNED DEFAULT NULL,
  customer_full_name VARCHAR(160) NOT NULL,
  customer_phone VARCHAR(32) NOT NULL,
  customer_location VARCHAR(255) NOT NULL,
  customer_landmark VARCHAR(255) NOT NULL DEFAULT '',
  status ENUM('pending','preparing','ready','completed','delivered') NOT NULL DEFAULT 'pending',
  item_count INT UNSIGNED NOT NULL DEFAULT 0,
  total INT UNSIGNED NOT NULL DEFAULT 0,
  restaurant_name VARCHAR(160) NOT NULL DEFAULT '',
  restaurant_image VARCHAR(255) DEFAULT NULL,
  customer_read TINYINT(1) NOT NULL DEFAULT 0,
  partner_read TINYINT(1) NOT NULL DEFAULT 0,
  customer_dismissed TINYINT(1) NOT NULL DEFAULT 0,
  partner_dismissed TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_orders_public_code (public_code),
  KEY idx_orders_restaurant (restaurant_id),
  KEY idx_orders_buyer (buyer_id),
  KEY idx_orders_phone (customer_phone),
  KEY idx_orders_status (status),
  KEY idx_orders_created (created_at)
) 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,
  menu_item_id INT UNSIGNED DEFAULT NULL,
  item_code VARCHAR(32) DEFAULT NULL,
  name VARCHAR(160) NOT NULL,
  qty INT UNSIGNED NOT NULL DEFAULT 1,
  price INT UNSIGNED NOT NULL DEFAULT 0,
  image_url VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_order_items_order (order_id),
  CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS push_devices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_type ENUM('buyer', 'partner') NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  push_token VARCHAR(255) NOT NULL,
  device_type VARCHAR(20) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_push_token (push_token),
  KEY idx_push_account (account_type, account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;