-- =========================================================
-- 097_purchase_order_resolution_phase3.sql
-- Phase 3: outstanding resolution + audit trail timeline
-- =========================================================

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

CREATE TABLE IF NOT EXISTS purchase_order_item_resolutions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  purchase_order_item_id BIGINT UNSIGNED NOT NULL,
  resolution_type VARCHAR(60) NOT NULL,
  resolution_qty DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  resolution_note TEXT NOT NULL,
  created_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  created_by_name_snapshot VARCHAR(120) DEFAULT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_purchase_order_item_resolutions_item_created (purchase_order_item_id, created_at),
  CONSTRAINT fk_purchase_order_item_resolutions_item FOREIGN KEY (purchase_order_item_id) REFERENCES purchase_order_items(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_order_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  purchase_order_id BIGINT UNSIGNED NOT NULL,
  purchase_order_item_id BIGINT UNSIGNED DEFAULT NULL,
  event_type VARCHAR(60) NOT NULL,
  event_note TEXT DEFAULT NULL,
  actor_user_id BIGINT UNSIGNED DEFAULT NULL,
  actor_name_snapshot VARCHAR(120) DEFAULT NULL,
  created_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_purchase_order_events_order_created (purchase_order_id, created_at),
  KEY idx_purchase_order_events_item_created (purchase_order_item_id, created_at),
  KEY idx_purchase_order_events_type_created (event_type, created_at),
  CONSTRAINT fk_purchase_order_events_order FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_purchase_order_events_item FOREIGN KEY (purchase_order_item_id) REFERENCES purchase_order_items(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, name, module, created_at) VALUES
('purchase_orders.resolve', 'Resolve Outstanding 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.resolve'
)
WHERE r.code IN ('owner', 'admin', 'kepala_toko');