-- Stock Management Starter (PHP 8.x) - Average Cost + GST split
-- Import into a fresh DB (e.g. stock_mgmt)

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS stock_ledger;
DROP TABLE IF EXISTS stock_summary;
DROP TABLE IF EXISTS sales_items;
DROP TABLE IF EXISTS sales;
DROP TABLE IF EXISTS purchase_items;
DROP TABLE IF EXISTS purchases;
DROP TABLE IF EXISTS masters_items;
DROP TABLE IF EXISTS masters_parties;
DROP TABLE IF EXISTS company_settings;

CREATE TABLE company_settings (
  id INT PRIMARY KEY,
  company_name VARCHAR(200) NOT NULL DEFAULT 'My Company',
  gstin VARCHAR(20) NOT NULL DEFAULT '',
  state_code VARCHAR(2) NOT NULL DEFAULT '09',
  address VARCHAR(255) NOT NULL DEFAULT '',
  default_tax_mode ENUM('INCLUSIVE','EXCLUSIVE') NOT NULL DEFAULT 'EXCLUSIVE',
  rounding_mode ENUM('NEAREST_1','NEAREST_0_05','NEAREST_0_01') NOT NULL DEFAULT 'NEAREST_0_01',
  enable_cst TINYINT(1) NOT NULL DEFAULT 0,
  cst_percent_default DECIMAL(5,2) NOT NULL DEFAULT 0,
  invoice_prefix VARCHAR(20) NOT NULL DEFAULT 'INV-',
  invoice_padding INT NOT NULL DEFAULT 6,
  invoice_next INT NOT NULL DEFAULT 1,
  b2c_invoice_type ENUM('TAX_INVOICE','BILL_OF_SUPPLY') NOT NULL DEFAULT 'TAX_INVOICE',
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO company_settings(id, company_name, gstin, state_code, default_tax_mode, rounding_mode, enable_cst, cst_percent_default, invoice_prefix, invoice_padding, invoice_next, b2c_invoice_type, created_at, updated_at)
VALUES (1,'My Company','','09','EXCLUSIVE','NEAREST_0_01',0,0,'INV-',6,1,'TAX_INVOICE',NOW(),NOW());

CREATE TABLE masters_parties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  party_type ENUM('CUSTOMER','SUPPLIER','BOTH') NOT NULL DEFAULT 'CUSTOMER',
  party_name VARCHAR(200) NOT NULL,
  gstin VARCHAR(20) NULL,
  state_code VARCHAR(2) NOT NULL,
  address VARCHAR(255) NULL,
  mobile VARCHAR(20) NULL,
  email VARCHAR(120) NULL,
  credit_limit DECIMAL(12,2) NOT NULL DEFAULT 0,
  opening_balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  opening_balance_type ENUM('DR','CR') NOT NULL DEFAULT 'DR',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  KEY idx_party_name (party_name),
  KEY idx_party_type (party_type),
  KEY idx_state_code (state_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE masters_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_name VARCHAR(200) NOT NULL,
  brand VARCHAR(100) NULL,
  unit VARCHAR(20) NOT NULL DEFAULT 'pcs',
  hsn_sac VARCHAR(30) NULL,
  gst_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
  default_purchase_rate DECIMAL(12,4) NOT NULL DEFAULT 0,
  default_sale_rate DECIMAL(12,4) NOT NULL DEFAULT 0,
  mrp DECIMAL(12,4) NOT NULL DEFAULT 0,
  min_stock_qty DECIMAL(14,4) NOT NULL DEFAULT 0,
  stock_tracking_mode ENUM('NONE','BATCH','EXPIRY','BATCH_EXPIRY') NOT NULL DEFAULT 'NONE',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  KEY idx_item_name (item_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE purchases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  purchase_no VARCHAR(30) NOT NULL,
  supplier_invoice_no VARCHAR(50) NULL,
  supplier_id INT NOT NULL,
  invoice_date DATE NOT NULL,
  tax_type ENUM('INTRA','INTER') NOT NULL,
  tax_mode ENUM('INCLUSIVE','EXCLUSIVE') NOT NULL,
  freight_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  freight_taxable TINYINT(1) NOT NULL DEFAULT 0,
  other_charges DECIMAL(14,2) NOT NULL DEFAULT 0,
  other_charges_taxable TINYINT(1) NOT NULL DEFAULT 0,
  header_discount DECIMAL(14,2) NOT NULL DEFAULT 0,
  taxable_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  tax_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  grand_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  KEY idx_purchase_date (invoice_date),
  CONSTRAINT fk_pur_supplier FOREIGN KEY (supplier_id) REFERENCES masters_parties(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE purchase_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  purchase_id INT NOT NULL,
  item_id INT NOT NULL,
  qty DECIMAL(14,4) NOT NULL,
  discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  base_rate DECIMAL(14,6) NOT NULL,
  gst_percent DECIMAL(5,2) NOT NULL,
  cgst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  sgst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  igst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  cst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  taxable_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  CONSTRAINT fk_pur_item_pur FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
  CONSTRAINT fk_pur_item_item FOREIGN KEY (item_id) REFERENCES masters_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sales (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(30) NOT NULL,
  invoice_date DATE NOT NULL,
  invoice_type ENUM('B2B','B2C') NOT NULL DEFAULT 'B2C',
  customer_id INT NULL,
  gstin VARCHAR(20) NULL,
  place_state_code VARCHAR(2) NOT NULL,
  place_of_supply VARCHAR(80) NULL,
  tax_type ENUM('INTRA','INTER') NOT NULL,
  tax_mode ENUM('INCLUSIVE','EXCLUSIVE') NOT NULL,
  freight_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  freight_taxable TINYINT(1) NOT NULL DEFAULT 0,
  other_charges DECIMAL(14,2) NOT NULL DEFAULT 0,
  other_charges_taxable TINYINT(1) NOT NULL DEFAULT 0,
  header_discount DECIMAL(14,2) NOT NULL DEFAULT 0,
  taxable_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  tax_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  grand_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  UNIQUE KEY uniq_invoice (invoice_no),
  KEY idx_sales_date (invoice_date),
  CONSTRAINT fk_sales_customer FOREIGN KEY (customer_id) REFERENCES masters_parties(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sales_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sales_id INT NOT NULL,
  item_id INT NOT NULL,
  qty DECIMAL(14,4) NOT NULL,
  discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  base_rate DECIMAL(14,6) NOT NULL,
  gst_percent DECIMAL(5,2) NOT NULL,
  cgst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  sgst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  igst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  cst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  taxable_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  issue_rate DECIMAL(14,6) NOT NULL DEFAULT 0,
  cogs_value DECIMAL(14,2) NOT NULL DEFAULT 0,
  CONSTRAINT fk_sales_item_sales FOREIGN KEY (sales_id) REFERENCES sales(id) ON DELETE CASCADE,
  CONSTRAINT fk_sales_item_item FOREIGN KEY (item_id) REFERENCES masters_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE purchase_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  purchase_id INT NOT NULL,
  pay_date DATE NOT NULL,
  mode ENUM('CASH','UPI','BANK','CREDIT') NOT NULL DEFAULT 'CREDIT',
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  reference_no VARCHAR(50) NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME NULL,
  KEY idx_pp_purchase (purchase_id),
  KEY idx_pp_date (pay_date),
  CONSTRAINT fk_pp_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sales_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sales_id INT NOT NULL,
  pay_date DATE NOT NULL,
  mode ENUM('CASH','UPI','BANK','CREDIT') NOT NULL DEFAULT 'CREDIT',
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  reference_no VARCHAR(50) NULL,
  note VARCHAR(255) NULL,
  is_cancelled TINYINT(1) NOT NULL DEFAULT 0,
  cancelled_at DATETIME NULL,
  created_at DATETIME NULL,
  KEY idx_sp_sales (sales_id),
  KEY idx_sp_date (pay_date),
  CONSTRAINT fk_sp_sales FOREIGN KEY (sales_id) REFERENCES sales(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE receipts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  party_id INT NULL,
  receipt_date DATE NOT NULL,
  mode ENUM('CASH','UPI','BANK') NOT NULL DEFAULT 'CASH',
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  reference_no VARCHAR(50) NULL,
  note VARCHAR(255) NULL,
  is_cancelled TINYINT(1) NOT NULL DEFAULT 0,
  cancelled_at DATETIME NULL,
  created_at DATETIME NULL,
  KEY idx_rc_party_date (party_id, receipt_date),
  CONSTRAINT fk_rc_party FOREIGN KEY (party_id) REFERENCES masters_parties(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE receipt_allocations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  receipt_id INT NOT NULL,
  sales_id INT NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  KEY idx_ra_receipt (receipt_id),
  KEY idx_ra_sales (sales_id),
  CONSTRAINT fk_ra_receipt FOREIGN KEY (receipt_id) REFERENCES receipts(id) ON DELETE CASCADE,
  CONSTRAINT fk_ra_sales FOREIGN KEY (sales_id) REFERENCES sales(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE credit_notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  credit_no VARCHAR(30) NOT NULL,
  credit_date DATE NOT NULL,
  sales_id INT NOT NULL,
  customer_id INT NULL,
  taxable_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  tax_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  grand_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  note VARCHAR(255) NULL,
  is_cancelled TINYINT(1) NOT NULL DEFAULT 0,
  cancelled_at DATETIME NULL,
  created_at DATETIME NULL,
  UNIQUE KEY uniq_credit (credit_no),
  KEY idx_cn_date (credit_date),
  CONSTRAINT fk_cn_sales FOREIGN KEY (sales_id) REFERENCES sales(id) ON DELETE CASCADE,
  CONSTRAINT fk_cn_customer FOREIGN KEY (customer_id) REFERENCES masters_parties(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE credit_note_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  credit_id INT NOT NULL,
  sales_item_id INT NOT NULL,
  item_id INT NOT NULL,
  qty DECIMAL(14,4) NOT NULL,
  base_rate DECIMAL(14,6) NOT NULL,
  gst_percent DECIMAL(5,2) NOT NULL,
  cgst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  sgst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  igst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  cst_amt DECIMAL(14,2) NOT NULL DEFAULT 0,
  taxable_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  issue_rate DECIMAL(14,6) NOT NULL DEFAULT 0,
  cogs_reversal DECIMAL(14,2) NOT NULL DEFAULT 0,
  CONSTRAINT fk_cni_credit FOREIGN KEY (credit_id) REFERENCES credit_notes(id) ON DELETE CASCADE,
  CONSTRAINT fk_cni_sales_item FOREIGN KEY (sales_item_id) REFERENCES sales_items(id),
  CONSTRAINT fk_cni_item FOREIGN KEY (item_id) REFERENCES masters_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE stock_summary (
  item_id INT PRIMARY KEY,
  qty_on_hand DECIMAL(14,4) NOT NULL DEFAULT 0,
  avg_rate DECIMAL(14,6) NOT NULL DEFAULT 0,
  stock_value DECIMAL(14,2) NOT NULL DEFAULT 0,
  updated_at DATETIME NULL,
  CONSTRAINT fk_ss_item FOREIGN KEY (item_id) REFERENCES masters_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE stock_ledger (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  txn_date DATE NOT NULL,
  ref_type VARCHAR(20) NOT NULL,
  ref_id INT NOT NULL,
  ref_no VARCHAR(30) NOT NULL,
  note VARCHAR(255) NULL,
  item_id INT NOT NULL,
  in_qty DECIMAL(14,4) NOT NULL DEFAULT 0,
  out_qty DECIMAL(14,4) NOT NULL DEFAULT 0,
  rate_used DECIMAL(14,6) NOT NULL DEFAULT 0,
  value_in DECIMAL(14,2) NOT NULL DEFAULT 0,
  value_out DECIMAL(14,2) NOT NULL DEFAULT 0,
  balance_qty_after DECIMAL(14,4) NOT NULL DEFAULT 0,
  balance_value_after DECIMAL(14,2) NOT NULL DEFAULT 0,
  avg_rate_after DECIMAL(14,6) NOT NULL DEFAULT 0,
  KEY idx_led_item_date (item_id, txn_date),
  KEY idx_led_ref (ref_type, ref_id),
  CONSTRAINT fk_led_item FOREIGN KEY (item_id) REFERENCES masters_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS=1;
