-- =====================================================================
--  STOCKWELL — Inventory Management ERP
--  MySQL schema. Multi-company (multi-tenant) via company_id on every
--  business table. Auth via users + user_companies membership.
--  Run this once on an empty database (e.g. `stockwell`).
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
--  AUTH / TENANCY
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(120) NOT NULL,
  email         VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS companies (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(160) NOT NULL,
  fy         VARCHAR(40)  NOT NULL DEFAULT 'FY 2025-26',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- which users can access which companies
CREATE TABLE IF NOT EXISTS user_companies (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT NOT NULL,
  company_id INT NOT NULL,
  role       VARCHAR(40) NOT NULL DEFAULT 'owner',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_company (user_id, company_id),
  CONSTRAINT fk_uc_user    FOREIGN KEY (user_id)    REFERENCES users(id)     ON DELETE CASCADE,
  CONSTRAINT fk_uc_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
--  MASTERS (per company)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS units (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  name       VARCHAR(80) NOT NULL,
  symbol     VARCHAR(20) NOT NULL,
  KEY idx_units_company (company_id),
  CONSTRAINT fk_units_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS categories (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  name       VARCHAR(120) NOT NULL,
  KEY idx_categories_company (company_id),
  CONSTRAINT fk_categories_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS warehouses (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  name       VARCHAR(120) NOT NULL,
  code       VARCHAR(40)  NOT NULL DEFAULT '',
  location   VARCHAR(190) NOT NULL DEFAULT '',
  KEY idx_warehouses_company (company_id),
  CONSTRAINT fk_warehouses_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS suppliers (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  name       VARCHAR(160) NOT NULL,
  contact    VARCHAR(120) NOT NULL DEFAULT '',
  phone      VARCHAR(60)  NOT NULL DEFAULT '',
  email      VARCHAR(190) NOT NULL DEFAULT '',
  gstin      VARCHAR(40)  NOT NULL DEFAULT '',
  lead_days  INT          NOT NULL DEFAULT 0,
  KEY idx_suppliers_company (company_id),
  CONSTRAINT fk_suppliers_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS items (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  company_id  INT NOT NULL,
  sku         VARCHAR(80)  NOT NULL DEFAULT '',
  name        VARCHAR(190) NOT NULL,
  barcode     VARCHAR(80)  NOT NULL DEFAULT '',
  category_id INT NULL,
  unit_id     INT NULL,
  hsn         VARCHAR(40)  NOT NULL DEFAULT '',
  gst         DECIMAL(6,2) NOT NULL DEFAULT 0,
  reorder     DECIMAL(14,3) NOT NULL DEFAULT 0,
  sale        DECIMAL(14,2) NOT NULL DEFAULT 0,
  supplier_id INT NULL,
  KEY idx_items_company (company_id),
  CONSTRAINT fk_items_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- opening stock per item, per warehouse
CREATE TABLE IF NOT EXISTS item_opening (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  item_id    INT NOT NULL,
  wh_id      INT NOT NULL,
  qty        DECIMAL(14,3) NOT NULL DEFAULT 0,
  rate       DECIMAL(14,2) NOT NULL DEFAULT 0,
  KEY idx_open_company (company_id),
  KEY idx_open_item (item_id),
  CONSTRAINT fk_open_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_open_item    FOREIGN KEY (item_id)    REFERENCES items(id)     ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
--  TRANSACTIONS (per company)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS movements (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  item_id    INT NOT NULL,
  wh_id      INT NOT NULL,
  `date`     DATE NOT NULL,
  direction  ENUM('IN','OUT') NOT NULL,
  qty        DECIMAL(14,3) NOT NULL DEFAULT 0,
  rate       DECIMAL(14,2) NOT NULL DEFAULT 0,
  type       VARCHAR(20) NOT NULL,            -- GRN | ISSUE | ADJUST | TRANSFER_IN | TRANSFER_OUT
  ref        VARCHAR(80) NOT NULL DEFAULT '',
  remarks    VARCHAR(255) NOT NULL DEFAULT '',
  KEY idx_mv_company (company_id),
  KEY idx_mv_item (item_id),
  CONSTRAINT fk_mv_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  company_id  INT NOT NULL,
  no          VARCHAR(40) NOT NULL,
  `date`      DATE NOT NULL,
  supplier_id INT NULL,
  status      VARCHAR(20) NOT NULL DEFAULT 'Open',
  expected    DATE NULL,
  KEY idx_po_company (company_id),
  CONSTRAINT fk_po_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_order_lines (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  po_id     INT NOT NULL,
  item_id   INT NOT NULL,
  qty       DECIMAL(14,3) NOT NULL DEFAULT 0,
  rate      DECIMAL(14,2) NOT NULL DEFAULT 0,
  received  DECIMAL(14,3) NOT NULL DEFAULT 0,
  KEY idx_pol_po (po_id),
  CONSTRAINT fk_pol_po FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_bills (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  company_id  INT NOT NULL,
  no          VARCHAR(40) NOT NULL,
  `date`      DATE NOT NULL,
  po_id       INT NULL,
  supplier_id INT NULL,
  status      VARCHAR(20) NOT NULL DEFAULT 'Posted',
  KEY idx_bill_company (company_id),
  CONSTRAINT fk_bill_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_bill_lines (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  bill_id INT NOT NULL,
  item_id INT NOT NULL,
  qty     DECIMAL(14,3) NOT NULL DEFAULT 0,
  rate    DECIMAL(14,2) NOT NULL DEFAULT 0,
  gst     DECIMAL(6,2)  NOT NULL DEFAULT 0,
  wh_id   INT NULL,                            -- NULL = held (not stocked)
  KEY idx_bl_bill (bill_id),
  CONSTRAINT fk_bl_bill FOREIGN KEY (bill_id) REFERENCES purchase_bills(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;