
CREATE TABLE IF NOT EXISTS PO_procurement_settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 replenishment_enabled TINYINT(1) NOT NULL DEFAULT 1,
 advisor_enabled TINYINT(1) NOT NULL DEFAULT 1,
 auto_po_enabled TINYINT(1) NOT NULL DEFAULT 0,
 default_procurement_mode ENUM('MANUAL','ADVICE','AUTO_DRAFT','FULL_AUTO') NOT NULL DEFAULT 'ADVICE',
 default_analysis_days INT NOT NULL DEFAULT 15,
 allow_custom_analysis_period TINYINT(1) NOT NULL DEFAULT 1,
 require_approval_for_advice_po TINYINT(1) NOT NULL DEFAULT 1,
 require_approval_for_auto_draft TINYINT(1) NOT NULL DEFAULT 1,
 full_auto_max_po_value DECIMAL(18,4) NULL,
 full_auto_max_price_increase_percent DECIMAL(8,4) NOT NULL DEFAULT 3,
 block_on_data_quality_high TINYINT(1) NOT NULL DEFAULT 1,
 block_on_critical_anomaly TINYINT(1) NOT NULL DEFAULT 1,
 updated_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_PO_procurement_settings_company(company_id),
 CONSTRAINT fk_PO_procurement_settings_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_procurement_settings_user FOREIGN KEY(updated_by) REFERENCES PO_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_recommendation_actions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 recommendation_id BIGINT UNSIGNED NOT NULL,
 action ENUM('VIEWED','ACCEPTED','OVERRIDDEN','REJECTED','CREATED_PO','CREATED_TRANSFER') NOT NULL,
 original_qty DECIMAL(18,4) NULL,
 selected_qty DECIMAL(18,4) NULL,
 reason VARCHAR(500) NULL,
 purchase_order_id BIGINT UNSIGNED NULL,
 transfer_id BIGINT UNSIGNED NULL,
 user_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_PO_recommendation_actions_reco(recommendation_id,created_at),
 KEY idx_PO_recommendation_actions_company(company_id,created_at),
 CONSTRAINT fk_PO_reco_actions_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_reco_actions_reco FOREIGN KEY(recommendation_id) REFERENCES PO_replenishment_recommendations(id),
 CONSTRAINT fk_PO_reco_actions_po FOREIGN KEY(purchase_order_id) REFERENCES PO_purchase_orders(id),
 CONSTRAINT fk_PO_reco_actions_transfer FOREIGN KEY(transfer_id) REFERENCES PO_transfers(id),
 CONSTRAINT fk_PO_reco_actions_user FOREIGN KEY(user_id) REFERENCES PO_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO PO_procurement_settings(company_id)
SELECT id FROM PO_companies;

