SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE IF NOT EXISTS PO_alerts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NULL,
 alert_type VARCHAR(80) NOT NULL,
 severity ENUM('INFO','LOW','MEDIUM','HIGH','CRITICAL') NOT NULL DEFAULT 'INFO',
 title VARCHAR(220) NOT NULL,
 message TEXT NULL,
 entity_type VARCHAR(80) NULL,
 entity_id BIGINT UNSIGNED NULL,
 status ENUM('OPEN','ACKNOWLEDGED','RESOLVED','DISMISSED') NOT NULL DEFAULT 'OPEN',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 acknowledged_at DATETIME NULL,
 resolved_at DATETIME NULL,
 KEY idx_PO_alerts_company_status(company_id,status,severity,created_at),
 CONSTRAINT fk_PO_alerts_company FOREIGN KEY(company_id) REFERENCES PO_companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS PO_vendor_portal_settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 vendor_id BIGINT UNSIGNED NOT NULL,
 show_sales_qty TINYINT(1) NOT NULL DEFAULT 1,
 show_sales_value TINYINT(1) NOT NULL DEFAULT 0,
 show_stock TINYINT(1) NOT NULL DEFAULT 1,
 show_stock_by_location TINYINT(1) NOT NULL DEFAULT 1,
 show_purchase_price TINYINT(1) NOT NULL DEFAULT 0,
 show_receipts TINYINT(1) NOT NULL DEFAULT 1,
 show_movements TINYINT(1) NOT NULL DEFAULT 1,
 allow_po_acknowledgement TINYINT(1) NOT NULL DEFAULT 1,
 allow_asn TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_PO_vendor_portal_settings(company_id,vendor_id),
 CONSTRAINT fk_PO_vps_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_vps_vendor FOREIGN KEY(vendor_id) REFERENCES PO_vendors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS PO_system_health (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 component VARCHAR(100) NOT NULL,
 status ENUM('OK','WARNING','ERROR') NOT NULL,
 message VARCHAR(500) NULL,
 checked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_PO_health_component(component,checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO PO_system_settings(company_id,setting_key,setting_value,value_type) VALUES(NULL,'SYSTEM_VERSION','5.0.0','STRING');
INSERT IGNORE INTO PO_sync_endpoints(code,name,direction,base_url,path,http_method,auth_type,page_size,sync_interval_minutes,incremental_field,priority) VALUES
('ERP_INVENTORY_MOVEMENTS','Inventory Movements','ERP_TO_SYSTEM','http://nav-server','/api/po/inventory-movements','GET','BASIC',5000,15,'EntryNo',65),
('ERP_PURCHASE_RECEIPTS','Purchase Receipts','ERP_TO_SYSTEM','http://nav-server','/api/po/purchase-receipts','GET','BASIC',2000,15,'LastModifiedDateTime',85);
SET FOREIGN_KEY_CHECKS=1;
