-- =========================================================
-- 096_purchase_order_receipts_phase2.sql
-- Phase 2: purchase order receipts + receiving permissions
-- =========================================================

ALTER TABLE purchase_orders
  MODIFY status ENUM('draft','finalized','partial_receiving','sent','completed') NOT NULL DEFAULT 'draft';

ALTER TABLE purchase_order_items
  MODIFY status ENUM('draft','finalized','partial_received','received_full') NOT NULL DEFAULT 'draft';

CREATE TABLE IF NOT EXISTS purchase_order_receipts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  purchase_order_id BIGINT UNSIGNED NOT NULL,
  receipt_code VARCHAR(40) NOT NULL,
  receipt_date DATE NOT NULL,
  batch_label VARCHAR(160) NOT NULL,
  source_document_label VARCHAR(160) DEFAULT NULL,
  note TEXT DEFAULT NULL,
  status ENUM('draft','posted') NOT NULL DEFAULT 'draft',
  created_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  created_by_name_snapshot VARCHAR(120) DEFAULT NULL,
  posted_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  posted_by_name_snapshot VARCHAR(120) DEFAULT NULL,
  posted_at DATETIME DEFAULT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_purchase_order_receipts_code (receipt_code),
  KEY idx_purchase_order_receipts_order_status_date (purchase_order_id, status, receipt_date),
  CONSTRAINT fk_purchase_order_receipts_order FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_order_receipt_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  receipt_id BIGINT UNSIGNED NOT NULL,
  purchase_order_item_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  product_name_snapshot VARCHAR(180) NOT NULL,
  unit_snapshot VARCHAR(100) DEFAULT NULL,
  qty_received_file DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  note VARCHAR(255) DEFAULT NULL,
  posted_stock_move_id BIGINT UNSIGNED DEFAULT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_purchase_order_receipt_items_receipt (receipt_id),
  KEY idx_purchase_order_receipt_items_po_item (purchase_order_item_id),
  KEY idx_purchase_order_receipt_items_product (product_id),
  KEY idx_purchase_order_receipt_items_stock_move (posted_stock_move_id),
  CONSTRAINT fk_purchase_order_receipt_items_receipt FOREIGN KEY (receipt_id) REFERENCES purchase_order_receipts(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_purchase_order_receipt_items_po_item FOREIGN KEY (purchase_order_item_id) REFERENCES purchase_order_items(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_purchase_order_receipt_items_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, name, module, created_at) VALUES
('purchase_orders.receive_create', 'Buat Draft Receipt Purchase Orders', 'purchase_orders', NOW()),
('purchase_orders.receive_submit', 'Submit Receipt Purchase Orders', 'purchase_orders', NOW());

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.code IN (
  'purchase_orders.receive_create',
  'purchase_orders.receive_submit'
)
WHERE r.code IN ('owner', 'admin', 'kepala_toko');