-- =========================================================
-- 095_purchase_orders_phase1.sql
-- Phase 1: internal purchase orders domain + permissions
-- =========================================================

CREATE TABLE IF NOT EXISTS purchase_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(40) NOT NULL,
  status ENUM('draft','finalized','sent') NOT NULL DEFAULT 'draft',
  order_date DATE NOT NULL,
  vendor_name_snapshot VARCHAR(180) NOT NULL,
  vendor_contact_snapshot VARCHAR(180) DEFAULT NULL,
  vendor_note TEXT DEFAULT NULL,
  internal_note TEXT DEFAULT NULL,
  created_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  created_by_name_snapshot VARCHAR(120) DEFAULT NULL,
  finalized_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  finalized_by_name_snapshot VARCHAR(120) DEFAULT NULL,
  finalized_at DATETIME DEFAULT NULL,
  sent_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  sent_by_name_snapshot VARCHAR(120) DEFAULT NULL,
  sent_at DATETIME DEFAULT NULL,
  completed_at DATETIME DEFAULT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_purchase_orders_location_code (location_id, code),
  KEY idx_purchase_orders_scope_status_date (tenant_id, location_id, status, order_date),
  KEY idx_purchase_orders_vendor_name (vendor_name_snapshot),
  CONSTRAINT fk_purchase_orders_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_order_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  purchase_order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  product_name_snapshot VARCHAR(180) NOT NULL,
  sku_snapshot VARCHAR(80) DEFAULT NULL,
  unit_snapshot VARCHAR(100) DEFAULT NULL,
  warehouse_group_snapshot VARCHAR(120) DEFAULT NULL,
  qty_on_hand_snapshot DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  minimum_stock_snapshot DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  stock_status_snapshot VARCHAR(40) DEFAULT NULL,
  qty_out_period_snapshot DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  suggestion_reason_snapshot VARCHAR(255) DEFAULT NULL,
  suggested_qty DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  owner_final_qty DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  received_qty_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  resolved_qty_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  outstanding_qty_open DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  status ENUM('draft','finalized') NOT NULL DEFAULT 'draft',
  owner_note VARCHAR(255) DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_purchase_order_items_order (purchase_order_id),
  KEY idx_purchase_order_items_product (product_id),
  CONSTRAINT fk_purchase_order_items_order FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_purchase_order_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.view', 'Lihat Purchase Orders', 'purchase_orders', NOW()),
('purchase_orders.create', 'Buat Draft Purchase Orders', 'purchase_orders', NOW()),
('purchase_orders.edit_draft', 'Edit Draft Purchase Orders', 'purchase_orders', NOW()),
('purchase_orders.finalize', 'Finalisasi Purchase Orders', 'purchase_orders', NOW()),
('purchase_orders.export_pdf', 'Export PDF 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.view',
  'purchase_orders.create',
  'purchase_orders.edit_draft',
  'purchase_orders.finalize',
  'purchase_orders.export_pdf'
)
WHERE r.code IN ('owner', 'admin', 'kepala_toko');