-- v1.17 Opening Stock Module
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS opening_docs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  doc_no VARCHAR(30) NOT NULL,
  doc_date DATE NOT NULL,
  warehouse_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'DRAFT', -- DRAFT, POSTED, CANCELLED
  notes VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  posted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_opening_doc_no (doc_no),
  KEY idx_opening_docs_wh (warehouse_id),
  KEY idx_opening_docs_date (doc_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS opening_doc_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  doc_id BIGINT UNSIGNED NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NULL,
  qty DECIMAL(18,6) NOT NULL DEFAULT 0,
  cost DECIMAL(18,6) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_opening_lines_doc (doc_id),
  KEY idx_opening_lines_item (item_id),
  CONSTRAINT fk_opening_lines_doc FOREIGN KEY (doc_id) REFERENCES opening_docs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Helpful index to speed up "already opened" checks:
CREATE INDEX idx_stock_moves_opening ON stock_moves (warehouse_id, item_id, ref_type);