-- =====================================================================
--  TECHDR Inventory — MySQL schema
--  Target: MySQL 5.7+ / 8.x / MariaDB 10.4+  (cPanel friendly)
--  Charset: utf8mb4 / utf8mb4_unicode_ci
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- users
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`           VARCHAR(120)    NOT NULL,
  `email`          VARCHAR(190)    NOT NULL,
  `password`       VARCHAR(255)    NOT NULL,
  `phone`          VARCHAR(32)     NULL,
  `role`           ENUM('super_admin','manager') NOT NULL DEFAULT 'manager',
  `status`         ENUM('active','inactive')   NOT NULL DEFAULT 'active',
  `avatar_color`   VARCHAR(20)     NULL,
  `token_version`  INT UNSIGNED    NOT NULL DEFAULT 1,
  `last_login_at`  DATETIME        NULL,
  `last_login_ip`  VARCHAR(45)     NULL,
  `created_at`     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`     DATETIME        NULL,
  -- Guarantees AT MOST ONE non-deleted super_admin (NULL = unconstrained)
  `super_admin_key` TINYINT GENERATED ALWAYS AS (
      (CASE WHEN `role` = 'super_admin' AND `deleted_at` IS NULL THEN 1 ELSE NULL END)
  ) STORED,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_email`  (`email`),
  UNIQUE KEY `uq_super_admin`  (`super_admin_key`),
  KEY `idx_users_role_status` (`role`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- categories
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(120) NOT NULL,
  `slug`        VARCHAR(150) NOT NULL,
  `description` VARCHAR(500) NULL,
  `icon`        VARCHAR(60)  NULL,
  `color`       VARCHAR(20)  NULL,
  `sort_order`  INT NOT NULL DEFAULT 0,
  `is_active`   TINYINT(1) NOT NULL DEFAULT 1,
  `created_by`  BIGINT UNSIGNED NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`  DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_categories_slug` (`slug`),
  KEY `idx_categories_active` (`is_active`, `sort_order`),
  CONSTRAINT `fk_categories_user` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- products
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id`   BIGINT UNSIGNED NULL,
  `name`          VARCHAR(191) NOT NULL,
  `sku`           VARCHAR(64)  NULL,
  `brand`         VARCHAR(120) NULL,
  `description`   TEXT         NULL,
  `notes`         VARCHAR(500) NULL,
  `cost_price`    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `sale_price`    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `quantity`      INT          NOT NULL DEFAULT 0,
  `reorder_level` INT          NOT NULL DEFAULT 5,
  `unit`          VARCHAR(24)  NOT NULL DEFAULT 'pcs',
  `location`      VARCHAR(80)  NULL,
  `image_url`     VARCHAR(255) NULL,
  `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
  `created_by`    BIGINT UNSIGNED NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`    DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_products_sku` (`sku`),
  KEY `idx_products_category` (`category_id`),
  KEY `idx_products_active`   (`is_active`, `deleted_at`),
  KEY `idx_products_name`     (`name`),
  KEY `idx_products_qty`      (`quantity`),
  CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_products_user`     FOREIGN KEY (`created_by`)   REFERENCES `users`(`id`)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- inventory_movements  (append-only stock ledger)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `inventory_movements`;
CREATE TABLE `inventory_movements` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id`     BIGINT UNSIGNED NOT NULL,
  `type`           ENUM('opening','in','out','adjustment','sale','sale_return','purchase_return') NOT NULL,
  `quantity`       INT NOT NULL COMMENT 'signed: + increases stock, - decreases stock',
  `balance_after`  INT NOT NULL,
  `unit_cost`      DECIMAL(12,2) NULL,
  `reason`         VARCHAR(255) NULL,
  `reference`      VARCHAR(64)  NULL COMMENT 'e.g. sale:1042',
  `user_id`        BIGINT UNSIGNED NULL,
  `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_inv_product` (`product_id`, `created_at`),
  KEY `idx_inv_type`    (`type`),
  KEY `idx_inv_created` (`created_at`),
  KEY `idx_inv_user`    (`user_id`),
  CONSTRAINT `fk_inv_product` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inv_user`    FOREIGN KEY (`user_id`)    REFERENCES `users`(`id`)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- sales  +  sale_items
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `sale_items`;
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_no`      VARCHAR(32) NOT NULL,
  `customer_name`   VARCHAR(160) NULL,
  `customer_phone`  VARCHAR(32)  NULL,
  `subtotal`        DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `discount_type`   ENUM('fixed','percent') NOT NULL DEFAULT 'fixed',
  `discount_value`  DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `tax_rate`        DECIMAL(6,2)  NOT NULL DEFAULT 0.00,
  `tax_amount`      DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `shipping_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total`           DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `total_cost`      DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT 'snapshot COGS for profit analytics',
  `paid`            DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `change_due`      DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `payment_method`  ENUM('cash','card','easypaisa','bank_transfer','other') NOT NULL DEFAULT 'cash',
  `status`          ENUM('completed','cancelled','refunded') NOT NULL DEFAULT 'completed',
  `note`            VARCHAR(500) NULL,
  `user_id`         BIGINT UNSIGNED NULL,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`      DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sales_invoice` (`invoice_no`),
  KEY `idx_sales_created` (`created_at`),
  KEY `idx_sales_status`   (`status`, `deleted_at`),
  KEY `idx_sales_user`     (`user_id`),
  CONSTRAINT `fk_sales_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sale_items` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `sale_id`      BIGINT UNSIGNED NOT NULL,
  `product_id`   BIGINT UNSIGNED NULL,
  `product_name` VARCHAR(191) NOT NULL,
  `sku`          VARCHAR(64)  NULL,
  `quantity`     INT NOT NULL,
  `unit_price`   DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `unit_cost`    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount`     DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `line_total`   DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_items_sale`    (`sale_id`),
  KEY `idx_items_product` (`product_id`),
  CONSTRAINT `fk_items_sale`    FOREIGN KEY (`sale_id`)    REFERENCES `sales`(`id`)   ON DELETE CASCADE,
  CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- expense_categories  +  expenses
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `expenses`;
DROP TABLE IF EXISTS `expense_categories`;
CREATE TABLE `expense_categories` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`       VARCHAR(120) NOT NULL,
  `slug`       VARCHAR(150) NOT NULL,
  `color`      VARCHAR(20)  NULL,
  `is_active`  TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_exp_cat_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `expenses` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id`    BIGINT UNSIGNED NULL,
  `title`          VARCHAR(191) NOT NULL,
  `description`    VARCHAR(500) NULL,
  `amount`         DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `expense_date`   DATE NOT NULL,
  `payment_method` ENUM('cash','card','easypaisa','bank_transfer','other') NOT NULL DEFAULT 'cash',
  `reference`      VARCHAR(120) NULL,
  `vendor`         VARCHAR(160) NULL,
  `user_id`        BIGINT UNSIGNED NULL,
  `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`     DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_exp_date`     (`expense_date`, `deleted_at`),
  KEY `idx_exp_category` (`category_id`),
  KEY `idx_exp_user`     (`user_id`),
  CONSTRAINT `fk_exp_category` FOREIGN KEY (`category_id`) REFERENCES `expense_categories`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exp_user`     FOREIGN KEY (`user_id`)        REFERENCES `users`(`id`)           ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- audit_logs  (who did what, when, from where)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`     BIGINT UNSIGNED NULL,
  `user_name`   VARCHAR(120) NULL,
  `user_role`   VARCHAR(24)  NULL,
  `action`      VARCHAR(64)  NOT NULL,
  `entity_type` VARCHAR(64)  NULL,
  `entity_id`   BIGINT UNSIGNED NULL,
  `description` VARCHAR(500) NULL,
  `meta`        JSON         NULL,
  `level`       ENUM('info','warning','critical') NOT NULL DEFAULT 'info',
  `ip_address`  VARCHAR(45)  NULL,
  `user_agent`  VARCHAR(255) NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_audit_created` (`created_at`),
  KEY `idx_audit_user`    (`user_id`, `created_at`),
  KEY `idx_audit_action`  (`action`),
  KEY `idx_audit_entity`  (`entity_type`, `entity_id`),
  KEY `idx_audit_level`   (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- settings  (key/value — business name, currency, tax defaults…)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `skey`       VARCHAR(80)  NOT NULL,
  `svalue`     TEXT         NULL,
  `sgroup`     VARCHAR(40)  NOT NULL DEFAULT 'general',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_settings_key` (`skey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;