-- ============================================================
-- Gbàgede Ọjà Suite — MySQL Schema
-- Run this once in cPanel → phpMyAdmin → SQL tab
-- Database: ozmosisbill_gbagede
-- ============================================================


-- ─── Products ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `products` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`            VARCHAR(255) NOT NULL,
  `barcode`         VARCHAR(100) NULL,
  `category`        VARCHAR(100) NOT NULL DEFAULT 'Uncategorized',
  `unit`            VARCHAR(50)  NOT NULL DEFAULT 'Pieces',
  `current_stock`   DECIMAL(10,2) NOT NULL DEFAULT 0,
  `min_stock_level` DECIMAL(10,2) NOT NULL DEFAULT 10,
  `purchase_price`  DECIMAL(15,2) NOT NULL DEFAULT 0,
  `sale_price`      DECIMAL(15,2) NOT NULL DEFAULT 0,
  `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`name`),
  INDEX (`barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Clients ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `clients` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`          VARCHAR(255) NOT NULL,
  `phone`         VARCHAR(50)  NULL,
  `email`         VARCHAR(255) NULL,
  `address`       TEXT NULL,
  `total_balance` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Invoices ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `invoices` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_number` VARCHAR(100) NOT NULL UNIQUE,
  `client_id`      INT UNSIGNED NULL,
  `customer_name`  VARCHAR(255) NULL DEFAULT 'Walk-in Customer',
  `date`           DATETIME NOT NULL,
  `due_date`       DATETIME NULL,
  `subtotal`       DECIMAL(15,2) NOT NULL DEFAULT 0,
  `tax`            DECIMAL(15,2) NOT NULL DEFAULT 0,
  `discount`       DECIMAL(15,2) NOT NULL DEFAULT 0,
  `total`          DECIMAL(15,2) NOT NULL DEFAULT 0,
  `paid_amount`    DECIMAL(15,2) NOT NULL DEFAULT 0,
  `status`         VARCHAR(50) NOT NULL DEFAULT 'Paid',
  `payment_method` VARCHAR(100) NOT NULL DEFAULT 'Cash',
  `type`           VARCHAR(100) NOT NULL DEFAULT 'Invoice',
  `created_by`     VARCHAR(100) NULL,
  `is_synced`      TINYINT(1) NOT NULL DEFAULT 0,
  `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`date`),
  INDEX (`client_id`),
  INDEX (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Invoice Items ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `invoice_items` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_id`  INT UNSIGNED NOT NULL,
  `product_id`  INT UNSIGNED NULL,
  `name`        VARCHAR(255) NOT NULL,
  `quantity`    DECIMAL(10,2) NOT NULL DEFAULT 1,
  `unit_price`  DECIMAL(15,2) NOT NULL DEFAULT 0,
  `total`       DECIMAL(15,2) NOT NULL DEFAULT 0,
  INDEX (`invoice_id`),
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Transactions ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `transactions` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id`      INT UNSIGNED NULL,
  `invoice_id`     INT UNSIGNED NULL,
  `type`           VARCHAR(50) NOT NULL DEFAULT 'RECEIPT',
  `payment_method` VARCHAR(100) NULL DEFAULT 'Cash',
  `amount`         DECIMAL(15,2) NOT NULL DEFAULT 0,
  `date`           DATETIME NOT NULL,
  `description`    TEXT NULL,
  `is_synced`      TINYINT(1) NOT NULL DEFAULT 0,
  `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (`date`),
  INDEX (`type`),
  INDEX (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Inventory Logs ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `inventory_logs` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `product_id`     INT UNSIGNED NOT NULL,
  `action`         VARCHAR(50) NOT NULL,
  `quantity`       DECIMAL(10,2) NOT NULL DEFAULT 0,
  `previous_stock` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `new_stock`      DECIMAL(10,2) NOT NULL DEFAULT 0,
  `date`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reference`      VARCHAR(255) NULL,
  INDEX (`product_id`),
  INDEX (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Setup / Lookup Tables ────────────────────────────────────
CREATE TABLE IF NOT EXISTS `units` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`         VARCHAR(100) NOT NULL,
  `abbreviation` VARCHAR(20)  NULL,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `units` (name, abbreviation) VALUES
  ('Pieces','pcs'),('Bags','bag'),('Tonnes','ton'),
  ('Litres','ltr'),('Metres','mtr'),('Cartons','ctn'),('Rolls','roll');

CREATE TABLE IF NOT EXISTS `item_groups` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`        VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `item_subgroups` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`        VARCHAR(255) NOT NULL,
  `group_id`    INT UNSIGNED NOT NULL,
  `description` TEXT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `vendors` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`         VARCHAR(255) NOT NULL,
  `phone`        VARCHAR(50) NULL,
  `email`        VARCHAR(255) NULL,
  `address`      TEXT NULL,
  `vendor_type`  VARCHAR(100) NULL,
  `tax_id`       VARCHAR(100) NULL,
  `total_owed`   DECIMAL(15,2) NOT NULL DEFAULT 0,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `manufacturers` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`           VARCHAR(255) NOT NULL,
  `phone`          VARCHAR(50) NULL,
  `email`          VARCHAR(255) NULL,
  `website`        VARCHAR(255) NULL,
  `country`        VARCHAR(100) NULL,
  `contact_person` VARCHAR(255) NULL,
  `notes`          TEXT NULL,
  `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `payment_methods` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`           VARCHAR(100) NOT NULL,
  `type`           VARCHAR(50) NULL,
  `account_number` VARCHAR(100) NULL,
  `bank_name`      VARCHAR(100) NULL,
  `is_active`      TINYINT(1) NOT NULL DEFAULT 1,
  `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `payment_methods` (name, type, is_active) VALUES
  ('Cash','Cash',1),('Card','Card',1),('Bank Transfer','Transfer',1);

CREATE TABLE IF NOT EXISTS `company` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(255) NULL,
  `address`    TEXT NULL,
  `phone`      VARCHAR(50) NULL,
  `email`      VARCHAR(255) NULL,
  `website`    VARCHAR(255) NULL,
  `rc_number`  VARCHAR(100) NULL,
  `tax_id`     VARCHAR(100) NULL,
  `logo`       TEXT NULL,
  `currency`   VARCHAR(10) NOT NULL DEFAULT 'NGN',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Table structure for table `demo_requests`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `demo_requests` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `full_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `business_name` VARCHAR(100),
  `business_size` VARCHAR(50),
  `preferred_date` DATE NOT NULL,
  `preferred_time` VARCHAR(20) NOT NULL,
  `message` TEXT,
  `status` ENUM('Pending', 'Contacted', 'Completed', 'Cancelled') DEFAULT 'Pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

