-- php-backend/cloud_schema.sql
-- Run this script in your cPanel phpMyAdmin to create the necessary tables for synchronization.

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

-- --------------------------------------------------------

--
-- Table structure for table `Products`
--

CREATE TABLE `Products` (
  `GlobalId` char(36) NOT NULL,
  `Barcode` varchar(255) DEFAULT NULL,
  `SKU` varchar(255) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Category` varchar(255) DEFAULT NULL,
  `Brand` varchar(255) DEFAULT NULL,
  `UnitId` int(11) DEFAULT NULL,
  `PackQuantity` int(11) NOT NULL DEFAULT 1,
  `CostPrice` decimal(18,2) NOT NULL DEFAULT 0.00,
  `MRP` decimal(18,2) NOT NULL DEFAULT 0.00,
  `SellingPrice` decimal(18,2) NOT NULL DEFAULT 0.00,
  `GstRate` decimal(18,2) NOT NULL DEFAULT 0.00,
  `StockQuantity` int(11) NOT NULL DEFAULT 0,
  `MinimumStockLevel` int(11) NOT NULL DEFAULT 0,
  `ExpiryDate` datetime DEFAULT NULL,
  `IsActive` tinyint(1) NOT NULL DEFAULT 1,
  `LastModified` datetime(3) NOT NULL,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `Customers`
--

CREATE TABLE `Customers` (
  `GlobalId` char(36) NOT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Phone` varchar(50) DEFAULT NULL,
  `Email` varchar(255) DEFAULT NULL,
  `Address` text DEFAULT NULL,
  `JoinDate` datetime DEFAULT NULL,
  `LoyaltyPoints` int(11) NOT NULL DEFAULT 0,
  `CreditBalance` decimal(18,2) NOT NULL DEFAULT 0.00,
  `AdvanceBalance` decimal(18,2) NOT NULL DEFAULT 0.00,
  `GoldBalance` decimal(18,2) NOT NULL DEFAULT 0.00,
  `TotalBills` int(11) NOT NULL DEFAULT 0,
  `TotalPurchase` decimal(18,2) NOT NULL DEFAULT 0.00,
  `LastPurchaseDate` datetime DEFAULT NULL,
  `LastModified` datetime(3) NOT NULL,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `Sales`
--

CREATE TABLE `Sales` (
  `GlobalId` char(36) NOT NULL,
  `InvoiceNumber` varchar(100) DEFAULT NULL,
  `Date` datetime NOT NULL,
  `CustomerGlobalId` char(36) DEFAULT NULL,
  `UserId` int(11) DEFAULT NULL,
  `SubTotal` decimal(18,2) NOT NULL DEFAULT 0.00,
  `TaxAmount` decimal(18,2) NOT NULL DEFAULT 0.00,
  `DiscountAmount` decimal(18,2) NOT NULL DEFAULT 0.00,
  `TotalAmount` decimal(18,2) NOT NULL DEFAULT 0.00,
  `PointsRedeemed` int(11) NOT NULL DEFAULT 0,
  `RedemptionValue` decimal(18,2) NOT NULL DEFAULT 0.00,
  `PointsEarned` int(11) NOT NULL DEFAULT 0,
  `PaymentMode` varchar(50) DEFAULT 'Cash',
  `CashAmount` decimal(18,2) NOT NULL DEFAULT 0.00,
  `UPIAmount` decimal(18,2) NOT NULL DEFAULT 0.00,
  `CardAmount` decimal(18,2) NOT NULL DEFAULT 0.00,
  `BankAmount` decimal(18,2) NOT NULL DEFAULT 0.00,
  `CreditAmount` decimal(18,2) NOT NULL DEFAULT 0.00,
  `LastModified` datetime(3) NOT NULL,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `Suppliers`
--

CREATE TABLE `Suppliers` (
  `GlobalId` char(36) NOT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `ContactPerson` varchar(255) DEFAULT NULL,
  `Phone` varchar(50) DEFAULT NULL,
  `Email` varchar(255) DEFAULT NULL,
  `GSTIN` varchar(50) DEFAULT NULL,
  `Address` text DEFAULT NULL,
  `LastModified` datetime(3) NOT NULL,
  `IsDeleted` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

ALTER TABLE `Products`
  ADD PRIMARY KEY (`GlobalId`),
  ADD KEY `idx_lastmodified` (`LastModified`),
  ADD KEY `idx_barcode` (`Barcode`);

ALTER TABLE `Customers`
  ADD PRIMARY KEY (`GlobalId`),
  ADD KEY `idx_lastmodified` (`LastModified`),
  ADD KEY `idx_phone` (`Phone`);

ALTER TABLE `Sales`
  ADD PRIMARY KEY (`GlobalId`),
  ADD KEY `idx_lastmodified` (`LastModified`),
  ADD KEY `idx_date` (`Date`);

ALTER TABLE `Suppliers`
  ADD PRIMARY KEY (`GlobalId`),
  ADD KEY `idx_lastmodified` (`LastModified`);

COMMIT;