-- OurOutlet Food Database Backup
-- Created: 2026-09-03 21:53:59 Asia/Karachi
-- Database: irsazivy_blends_n_bites
-- Mode: STRUCTURE ONLY

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- --------------------------------------------------------
-- Table: customers
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(190) NOT NULL,
  `phone` varchar(40) NOT NULL,
  `address` text DEFAULT NULL,
  `precise_address` text DEFAULT NULL,
  `location_lat` decimal(10,6) DEFAULT NULL,
  `location_lng` decimal(10,6) DEFAULT NULL,
  `maps_url` text DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `remember_token` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_customers_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_brand_page_passwords
DROP TABLE IF EXISTS `qd_brand_page_passwords`;
CREATE TABLE `qd_brand_page_passwords` (
  `brand_id` int(11) NOT NULL,
  `page_key` varchar(80) NOT NULL,
  `password_value` varchar(255) NOT NULL,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`brand_id`,`page_key`),
  UNIQUE KEY `uq_brand_page_password` (`brand_id`,`page_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_brands
DROP TABLE IF EXISTS `qd_brands`;
CREATE TABLE `qd_brands` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `business_name` varchar(190) NOT NULL,
  `whatsapp_number` varchar(50) NOT NULL,
  `login_password` varchar(190) NOT NULL DEFAULT 'admin',
  `logo_path` varchar(255) DEFAULT NULL,
  `slug` varchar(190) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `address` text DEFAULT NULL,
  `complain_number` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_brand_slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_categories
DROP TABLE IF EXISTS `qd_categories`;
CREATE TABLE `qd_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `category_name` varchar(190) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_category_sort` (`sort_order`),
  KEY `idx_category_brand` (`brand_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_customer_cancel_alerts
DROP TABLE IF EXISTS `qd_customer_cancel_alerts`;
CREATE TABLE `qd_customer_cancel_alerts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) NOT NULL DEFAULT 0,
  `order_id` int(11) NOT NULL,
  `cancel_reason` varchar(500) NOT NULL DEFAULT '',
  `cancelled_at` datetime NOT NULL,
  `kds_acknowledged_at` datetime DEFAULT NULL,
  `kds_acknowledged_by` varchar(190) DEFAULT NULL,
  `bill_acknowledged_at` datetime DEFAULT NULL,
  `bill_acknowledged_by` varchar(190) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_qd_customer_cancel_order` (`brand_id`,`order_id`),
  KEY `idx_qd_customer_cancel_kds` (`brand_id`,`kds_acknowledged_at`,`cancelled_at`),
  KEY `idx_qd_customer_cancel_bill` (`brand_id`,`bill_acknowledged_at`,`cancelled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_customer_locations
DROP TABLE IF EXISTS `qd_customer_locations`;
CREATE TABLE `qd_customer_locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `customer_name` varchar(190) DEFAULT NULL,
  `phone` varchar(50) NOT NULL,
  `location_lat` decimal(10,6) DEFAULT NULL,
  `location_lng` decimal(10,6) DEFAULT NULL,
  `maps_url` text DEFAULT NULL,
  `source` varchar(50) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_qd_customer_location_phone` (`phone`),
  KEY `idx_customer_location_customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_customer_payments
DROP TABLE IF EXISTS `qd_customer_payments`;
CREATE TABLE `qd_customer_payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `customer_name` varchar(190) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `order_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `amount_paid` decimal(12,2) NOT NULL DEFAULT 0.00,
  `amount_due` decimal(12,2) NOT NULL DEFAULT 0.00,
  `payment_type` varchar(20) NOT NULL DEFAULT 'received',
  `payment_method` varchar(20) DEFAULT NULL,
  `payment_recorded_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_customer_payments_customer` (`customer_id`),
  KEY `idx_customer_payments_phone` (`phone`),
  KEY `idx_customer_payments_order` (`order_id`),
  KEY `idx_customer_payments_recorded` (`payment_recorded_at`),
  KEY `idx_customer_payments_method` (`payment_method`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_daily_expenses
DROP TABLE IF EXISTS `qd_daily_expenses`;
CREATE TABLE `qd_daily_expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `expense_date` date NOT NULL,
  `expenses_json` mediumtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_daily_brand_date` (`brand_id`,`expense_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_items
DROP TABLE IF EXISTS `qd_items`;
CREATE TABLE `qd_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `category_id` int(11) NOT NULL DEFAULT 0,
  `item_name` varchar(190) NOT NULL,
  `item_type` varchar(40) NOT NULL DEFAULT 'single',
  `item_description` text DEFAULT NULL,
  `item_discount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `base_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `image_path` varchar(255) DEFAULT NULL,
  `is_available` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `inventory_quantity` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cost_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `stock_monitor_enabled` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_item_category` (`category_id`),
  KEY `idx_item_brand` (`brand_id`),
  KEY `idx_item_available` (`is_available`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_monthly_expenses
DROP TABLE IF EXISTS `qd_monthly_expenses`;
CREATE TABLE `qd_monthly_expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `expense_month` char(7) NOT NULL,
  `rent_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `bill_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `extra_expenses_json` mediumtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `rent_paid_at` datetime DEFAULT NULL,
  `bill_paid_at` datetime DEFAULT NULL,
  `rent_added_at` datetime DEFAULT NULL,
  `bill_added_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_expense_brand_month` (`brand_id`,`expense_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_order_items
DROP TABLE IF EXISTS `qd_order_items`;
CREATE TABLE `qd_order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `variant_name` varchar(255) DEFAULT NULL,
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `line_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_order_modifications
DROP TABLE IF EXISTS `qd_order_modifications`;
CREATE TABLE `qd_order_modifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) NOT NULL DEFAULT 0,
  `order_id` int(11) NOT NULL,
  `source` varchar(50) NOT NULL DEFAULT 'pos_bill',
  `items_json` longtext NOT NULL,
  `added_subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_by_name` varchar(190) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `acknowledged_at` datetime DEFAULT NULL,
  `acknowledged_by` varchar(190) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_qd_mod_order` (`order_id`),
  KEY `idx_qd_mod_brand_pending` (`brand_id`,`acknowledged_at`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_orders
DROP TABLE IF EXISTS `qd_orders`;
CREATE TABLE `qd_orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `source` varchar(60) NOT NULL DEFAULT 'customer_page',
  `created_by_name` varchar(190) DEFAULT NULL,
  `order_type` varchar(30) NOT NULL DEFAULT 'delivery',
  `status` varchar(40) NOT NULL DEFAULT 'placed',
  `customer_name` varchar(190) NOT NULL,
  `phone_number` varchar(40) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `precise_address` text DEFAULT NULL,
  `location_lat` decimal(10,6) DEFAULT NULL,
  `location_lng` decimal(10,6) DEFAULT NULL,
  `maps_url` text DEFAULT NULL,
  `kitchen_note` varchar(500) DEFAULT NULL,
  `subtotal_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `delivery_charge` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `table_number` int(11) DEFAULT NULL,
  `payment_method` varchar(40) DEFAULT NULL,
  `gst_percent` decimal(10,2) NOT NULL DEFAULT 0.00,
  `gst_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `service_charge_percent` decimal(10,2) NOT NULL DEFAULT 0.00,
  `service_charge_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `raw_whatsapp_text` mediumtext DEFAULT NULL,
  `rider_id` int(11) DEFAULT NULL,
  `cancel_reason` text DEFAULT NULL,
  `cancelled_by` varchar(40) DEFAULT NULL,
  `inventory_deducted` tinyint(1) NOT NULL DEFAULT 0,
  `accepted_at` datetime DEFAULT NULL,
  `processing_at` datetime DEFAULT NULL,
  `ready_at` datetime DEFAULT NULL,
  `picked_up_at` datetime DEFAULT NULL,
  `delivered_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `auto_accepted_at` datetime DEFAULT NULL,
  `auto_completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_order_type` (`order_type`),
  KEY `idx_source` (`source`),
  KEY `idx_created` (`created_at`),
  KEY `idx_brand` (`brand_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_promotions
DROP TABLE IF EXISTS `qd_promotions`;
CREATE TABLE `qd_promotions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) NOT NULL DEFAULT 0,
  `promo_type` varchar(30) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 0,
  `desktop_image` varchar(255) DEFAULT NULL,
  `mobile_image` varchar(255) DEFAULT NULL,
  `link_url` varchar(500) DEFAULT NULL,
  `marquee_text` text DEFAULT NULL,
  `popup_delay` int(11) NOT NULL DEFAULT 2,
  `popup_duration` int(11) NOT NULL DEFAULT 6,
  `marquee_speed` int(11) NOT NULL DEFAULT 18,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_brand_type` (`brand_id`,`promo_type`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table: qd_recipe_ingredients
DROP TABLE IF EXISTS `qd_recipe_ingredients`;
CREATE TABLE `qd_recipe_ingredients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scope` varchar(10) NOT NULL DEFAULT 'item',
  `ref_id` int(11) NOT NULL DEFAULT 0,
  `ingredient_name` varchar(190) NOT NULL DEFAULT '',
  `unit` varchar(20) NOT NULL DEFAULT 'piece',
  `quantity_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `makes_count` decimal(12,2) NOT NULL DEFAULT 0.00,
  `available_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `brand_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_recipe_ref` (`scope`,`ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table: qd_riders
DROP TABLE IF EXISTS `qd_riders`;
CREATE TABLE `qd_riders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(190) NOT NULL,
  `phone` varchar(40) NOT NULL,
  `id_card_number` varchar(80) DEFAULT NULL,
  `bike_name` varchar(120) DEFAULT NULL,
  `bike_model` varchar(120) DEFAULT NULL,
  `management_notes` text DEFAULT NULL,
  `password_hash` varchar(255) NOT NULL,
  `remember_token` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `approval_status` varchar(24) NOT NULL DEFAULT 'approved',
  `approved_at` datetime DEFAULT NULL,
  `declined_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `live_location_lat` decimal(10,6) DEFAULT NULL,
  `live_location_lng` decimal(10,6) DEFAULT NULL,
  `live_location_accuracy` decimal(10,2) DEFAULT NULL,
  `live_location_label` varchar(255) DEFAULT NULL,
  `live_location_maps_url` text DEFAULT NULL,
  `live_location_at` datetime DEFAULT NULL,
  `staff_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rider_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_salary_payments
DROP TABLE IF EXISTS `qd_salary_payments`;
CREATE TABLE `qd_salary_payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `staff_id` int(11) NOT NULL,
  `pay_month` char(7) NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `paid_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_salary_staff_month` (`staff_id`,`pay_month`),
  KEY `idx_salary_brand_month` (`brand_id`,`pay_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_settings
DROP TABLE IF EXISTS `qd_settings`;
CREATE TABLE `qd_settings` (
  `setting_key` varchar(190) NOT NULL,
  `setting_value` mediumtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_staff_due_payments
DROP TABLE IF EXISTS `qd_staff_due_payments`;
CREATE TABLE `qd_staff_due_payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `staff_id` int(11) NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `payment_type` varchar(20) DEFAULT NULL,
  `created_by` varchar(190) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_staff_due_payments_staff` (`staff_id`),
  KEY `idx_staff_due_payments_brand` (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_staff_expense_staff
DROP TABLE IF EXISTS `qd_staff_expense_staff`;
CREATE TABLE `qd_staff_expense_staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `staff_name` varchar(190) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_staff_expense_staff_expense` (`expense_id`),
  KEY `idx_staff_expense_staff_staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_staff_expenses
DROP TABLE IF EXISTS `qd_staff_expenses`;
CREATE TABLE `qd_staff_expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `title` varchar(190) NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `expense_category` varchar(20) NOT NULL DEFAULT 'personal',
  `created_by` varchar(190) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_staff_expenses_brand` (`brand_id`),
  KEY `idx_staff_expenses_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_staff_members
DROP TABLE IF EXISTS `qd_staff_members`;
CREATE TABLE `qd_staff_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `name` varchar(190) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `position` varchar(150) DEFAULT NULL,
  `salary_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `staff_password` varchar(190) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_staff_name` (`name`),
  KEY `idx_staff_phone` (`phone`),
  KEY `idx_staff_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_staff_order_items
DROP TABLE IF EXISTS `qd_staff_order_items`;
CREATE TABLE `qd_staff_order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `item_name` varchar(190) NOT NULL,
  `variant_name` varchar(190) DEFAULT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `line_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_staff_order_items_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_staff_order_staff
DROP TABLE IF EXISTS `qd_staff_order_staff`;
CREATE TABLE `qd_staff_order_staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `staff_name` varchar(190) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_staff_order_staff_order` (`order_id`),
  KEY `idx_staff_order_staff_staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_staff_orders
DROP TABLE IF EXISTS `qd_staff_orders`;
CREATE TABLE `qd_staff_orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `items_count` int(11) NOT NULL DEFAULT 0,
  `expense_category` varchar(20) NOT NULL DEFAULT 'personal',
  `created_by` varchar(190) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_staff_orders_brand` (`brand_id`),
  KEY `idx_staff_orders_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_staff_page_access
DROP TABLE IF EXISTS `qd_staff_page_access`;
CREATE TABLE `qd_staff_page_access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `page_key` varchar(80) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_staff_page_access` (`brand_id`,`staff_id`,`page_key`),
  KEY `idx_staff_access_brand_page` (`brand_id`,`page_key`),
  KEY `idx_staff_access_staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_storefront_category_images
DROP TABLE IF EXISTS `qd_storefront_category_images`;
CREATE TABLE `qd_storefront_category_images` (
  `brand_id` int(11) NOT NULL DEFAULT 0,
  `category_id` int(11) NOT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`brand_id`,`category_id`),
  KEY `idx_sf_category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_storefront_content
DROP TABLE IF EXISTS `qd_storefront_content`;
CREATE TABLE `qd_storefront_content` (
  `brand_id` int(11) NOT NULL DEFAULT 0,
  `home_kicker` varchar(120) DEFAULT NULL,
  `home_title` varchar(220) DEFAULT NULL,
  `home_text` text DEFAULT NULL,
  `home_intro_active` tinyint(1) NOT NULL DEFAULT 1,
  `show_logo` tinyint(1) NOT NULL DEFAULT 1,
  `nav_home` varchar(80) DEFAULT NULL,
  `nav_menu` varchar(80) DEFAULT NULL,
  `nav_about` varchar(80) DEFAULT NULL,
  `nav_contact` varchar(80) DEFAULT NULL,
  `help_phone` varchar(80) DEFAULT NULL,
  `instagram_url` varchar(500) DEFAULT NULL,
  `facebook_url` varchar(500) DEFAULT NULL,
  `tiktok_url` varchar(500) DEFAULT NULL,
  `category_title` varchar(220) DEFAULT NULL,
  `about_title` varchar(220) DEFAULT NULL,
  `about_text` text DEFAULT NULL,
  `contact_title` varchar(220) DEFAULT NULL,
  `contact_text` text DEFAULT NULL,
  `contact_phone` varchar(80) DEFAULT NULL,
  `contact_email` varchar(190) DEFAULT NULL,
  `contact_address` text DEFAULT NULL,
  `contact_hours` varchar(255) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_storefront_side_banners
DROP TABLE IF EXISTS `qd_storefront_side_banners`;
CREATE TABLE `qd_storefront_side_banners` (
  `brand_id` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `left_image` varchar(255) DEFAULT NULL,
  `right_image` varchar(255) DEFAULT NULL,
  `left_link` varchar(500) DEFAULT NULL,
  `right_link` varchar(500) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_supplier_transactions
DROP TABLE IF EXISTS `qd_supplier_transactions`;
CREATE TABLE `qd_supplier_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `supplier_id` int(11) NOT NULL,
  `type` varchar(10) NOT NULL DEFAULT 'purchase',
  `purchase_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `payment_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `note` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_supplier_txn_supplier` (`supplier_id`),
  KEY `idx_supplier_txn_brand` (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_suppliers
DROP TABLE IF EXISTS `qd_suppliers`;
CREATE TABLE `qd_suppliers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `name` varchar(190) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `supplier_title` varchar(150) DEFAULT NULL,
  `goods_supplied` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_supplier_name` (`name`),
  KEY `idx_supplier_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_tax_settings
DROP TABLE IF EXISTS `qd_tax_settings`;
CREATE TABLE `qd_tax_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `setting_key` varchar(120) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tax_setting_brand_key` (`brand_id`,`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table: qd_variants
DROP TABLE IF EXISTS `qd_variants`;
CREATE TABLE `qd_variants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT NULL,
  `item_id` int(11) NOT NULL DEFAULT 0,
  `variant_name` varchar(190) NOT NULL,
  `variant_group` varchar(50) NOT NULL DEFAULT 'variation',
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_available` tinyint(1) NOT NULL DEFAULT 1,
  `inventory_quantity` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cost_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `stock_monitor_enabled` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_variant_item` (`item_id`),
  KEY `idx_variant_brand` (`brand_id`),
  KEY `idx_variant_available` (`is_available`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
-- Backup complete.
