-- ============================================================================
-- NetStock Dual-Sync Inventory System - MySQL Schema (Clean, No Demo Data)
-- Compatible with cPanel phpMyAdmin (MySQL 5.7+ / 8.0+ / MariaDB 10.3+)
-- ============================================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

START TRANSACTION;

-- ----------------------------------------------------------------------------
-- Table 1: `products`
-- Mirrors Cloud Firestore `products` collection 1-to-1.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `firestore_id` VARCHAR(128) NULL,
  `sku` VARCHAR(64) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(100) NOT NULL DEFAULT 'General',
  `size` VARCHAR(100) NULL DEFAULT NULL,
  `buy_price` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  `stock_qty` INT NOT NULL DEFAULT 0,
  `price` DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT 'Sale price (maps to Firestore salePrice)',
  `sync_pending` TINYINT(1) NOT NULL DEFAULT 0,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_products_sku` (`sku`),
  UNIQUE KEY `uq_products_firestore_id` (`firestore_id`),
  KEY `idx_products_category` (`category`),
  KEY `idx_products_stock` (`stock_qty`),
  KEY `idx_products_sync_pending` (`sync_pending`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- Table 2: `stock_logs`
-- Audit trail for every stock movement (online_order, offline_sale, restock).
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` INT UNSIGNED NOT NULL,
  `action_type` ENUM('online_order', 'offline_sale', 'restock') NOT NULL,
  `qty_changed` INT NOT NULL COMMENT 'Positive for restock, negative for deductions',
  `remaining_stock` INT NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_stock_logs_product_time` (`product_id`, `timestamp`),
  KEY `idx_stock_logs_action` (`action_type`),
  CONSTRAINT `fk_stock_logs_product`
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- Table 3: `orders`
-- Records both website (API) and offline (POS) orders.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_name` VARCHAR(150) NOT NULL DEFAULT 'Walk-in Customer',
  `phone` VARCHAR(40) NOT NULL DEFAULT '',
  `address` TEXT NULL,
  `product_id` INT UNSIGNED NOT NULL,
  `qty` INT UNSIGNED NOT NULL,
  `total_amount` DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  `source` ENUM('website', 'offline') NOT NULL DEFAULT 'website',
  `status` VARCHAR(40) NOT NULL DEFAULT 'confirmed',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_orders_product` (`product_id`),
  KEY `idx_orders_source_created` (`source`, `created_at`),
  KEY `idx_orders_status` (`status`),
  CONSTRAINT `fk_orders_product`
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- Table 4: `sync_queue`
-- Queues Firestore writes whenever Firestore is temporarily unreachable
-- so MySQL operations never fail and sync catches up cleanly.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sync_queue` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity_type` VARCHAR(40) NOT NULL DEFAULT 'product',
  `entity_id` INT UNSIGNED NULL,
  `firestore_id` VARCHAR(128) NULL,
  `sku` VARCHAR(64) NULL,
  `action` ENUM('upsert', 'delete') NOT NULL DEFAULT 'upsert',
  `payload` LONGTEXT NULL,
  `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_error` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sync_queue_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;