CREATE TABLE IF NOT EXISTS PO_ai_advice (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 location_id BIGINT UNSIGNED NULL,
 item_id BIGINT UNSIGNED NULL,
 vendor_id BIGINT UNSIGNED NULL,
 recommendation_id BIGINT UNSIGNED NULL,
 advice_type ENUM('BUY','DO_NOT_BUY','TRANSFER','PROMOTION','PRICE_REVIEW','VENDOR_REVIEW','STOCKOUT_RISK','OVERSTOCK_RISK','INFO') NOT NULL,
 title_ar VARCHAR(255) NOT NULL,
 explanation_ar TEXT NOT NULL,
 confidence DECIMAL(8,4) NULL,
 evidence_json JSON NULL,
 valid_until DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_PO_ai_advice_item_loc (item_id,location_id,created_at),
 KEY idx_PO_ai_advice_vendor (vendor_id,created_at),
 CONSTRAINT fk_PO_ai_advice_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_ai_advice_location FOREIGN KEY(location_id) REFERENCES PO_locations(id),
 CONSTRAINT fk_PO_ai_advice_item FOREIGN KEY(item_id) REFERENCES PO_items(id),
 CONSTRAINT fk_PO_ai_advice_vendor FOREIGN KEY(vendor_id) REFERENCES PO_vendors(id),
 CONSTRAINT fk_PO_ai_advice_reco FOREIGN KEY(recommendation_id) REFERENCES PO_replenishment_recommendations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_forecast_accuracy (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 location_id BIGINT UNSIGNED NOT NULL,
 item_id BIGINT UNSIGNED NOT NULL,
 period_start DATE NOT NULL,
 period_end DATE NOT NULL,
 model_code VARCHAR(60) NOT NULL,
 forecast_qty DECIMAL(18,4) NOT NULL,
 actual_qty DECIMAL(18,4) NOT NULL,
 mae DECIMAL(18,4) NULL,
 mape DECIMAL(12,4) NULL,
 bias_percent DECIMAL(12,4) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_PO_forecast_accuracy_period (period_end,model_code),
 CONSTRAINT fk_PO_fa_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_fa_location FOREIGN KEY(location_id) REFERENCES PO_locations(id),
 CONSTRAINT fk_PO_fa_item FOREIGN KEY(item_id) REFERENCES PO_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_vendor_performance (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 vendor_id BIGINT UNSIGNED NOT NULL,
 period_start DATE NOT NULL,
 period_end DATE NOT NULL,
 po_count INT NOT NULL DEFAULT 0,
 ordered_qty DECIMAL(18,4) NOT NULL DEFAULT 0,
 received_qty DECIMAL(18,4) NOT NULL DEFAULT 0,
 fill_rate DECIMAL(8,4) NULL,
 on_time_rate DECIMAL(8,4) NULL,
 price_accuracy DECIMAL(8,4) NULL,
 return_rate DECIMAL(8,4) NULL,
 overall_score DECIMAL(8,4) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_PO_vendor_perf (company_id,vendor_id,period_start,period_end),
 CONSTRAINT fk_PO_vendor_perf_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_vendor_perf_vendor FOREIGN KEY(vendor_id) REFERENCES PO_vendors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_PO_sales_item_loc_date ON PO_sales_daily(item_id,location_id,sales_date);
CREATE INDEX idx_PO_inventory_location_available ON PO_inventory(location_id,available_qty,item_id);
CREATE INDEX idx_PO_reco_status_action ON PO_replenishment_recommendations(status,action,vendor_id);
