SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

-- Vendor portal identity and scoped access
CREATE TABLE IF NOT EXISTS PO_vendor_users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 vendor_id BIGINT UNSIGNED NOT NULL,
 username VARCHAR(100) NOT NULL,
 full_name VARCHAR(180) NOT NULL,
 email VARCHAR(180) NULL,
 phone VARCHAR(40) NULL,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('VENDOR_ADMIN','VENDOR_USER','VENDOR_VIEWER') NOT NULL DEFAULT 'VENDOR_USER',
 can_view_sales_qty TINYINT(1) NOT NULL DEFAULT 1,
 can_view_sales_value TINYINT(1) NOT NULL DEFAULT 0,
 can_view_stock_by_location TINYINT(1) NOT NULL DEFAULT 1,
 can_view_cost TINYINT(1) NOT NULL DEFAULT 0,
 can_acknowledge_po TINYINT(1) NOT NULL DEFAULT 1,
 can_submit_asn TINYINT(1) NOT NULL DEFAULT 1,
 active TINYINT(1) NOT NULL DEFAULT 1,
 last_login_at DATETIME 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_vendor_users_company_username (company_id,username),
 KEY idx_PO_vendor_users_vendor (vendor_id,active),
 CONSTRAINT fk_PO_vendor_users_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_vendor_users_vendor FOREIGN KEY(vendor_id) REFERENCES PO_vendors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_vendor_access_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 vendor_user_id BIGINT UNSIGNED NOT NULL,
 token_hash CHAR(64) NOT NULL UNIQUE,
 label VARCHAR(100) NULL,
 expires_at DATETIME NULL,
 last_used_at DATETIME NULL,
 revoked_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_PO_vendor_tokens_user FOREIGN KEY(vendor_user_id) REFERENCES PO_vendor_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_vendor_user_locations (
 vendor_user_id BIGINT UNSIGNED NOT NULL,
 location_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(vendor_user_id,location_id),
 CONSTRAINT fk_PO_vendor_user_locations_user FOREIGN KEY(vendor_user_id) REFERENCES PO_vendor_users(id),
 CONSTRAINT fk_PO_vendor_user_locations_location FOREIGN KEY(location_id) REFERENCES PO_locations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_inventory_movements (
 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,
 erp_entry_no BIGINT NULL,
 posting_date DATE NOT NULL,
 posting_datetime DATETIME NULL,
 movement_type ENUM('SALE','SALE_RETURN','PURCHASE_RECEIPT','PURCHASE_RETURN','TRANSFER_IN','TRANSFER_OUT','POSITIVE_ADJ','NEGATIVE_ADJ','COUNT','OTHER') NOT NULL,
 document_no VARCHAR(80) NULL,
 source_no VARCHAR(80) NULL,
 quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
 cost_amount DECIMAL(18,4) NULL,
 sales_amount DECIMAL(18,4) NULL,
 remaining_quantity DECIMAL(18,4) NULL,
 last_synced_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_PO_inventory_movement_entry (company_id,erp_entry_no),
 KEY idx_PO_inventory_movement_vendor_view (item_id,location_id,posting_date),
 KEY idx_PO_inventory_movement_doc (document_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_purchase_receipts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 vendor_id BIGINT UNSIGNED NOT NULL,
 erp_receipt_no VARCHAR(80) NOT NULL,
 erp_po_no VARCHAR(80) NULL,
 receipt_date DATE NOT NULL,
 location_id BIGINT UNSIGNED NULL,
 total_qty DECIMAL(18,4) NOT NULL DEFAULT 0,
 total_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
 last_synced_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_PO_purchase_receipt (company_id,erp_receipt_no),
 KEY idx_PO_receipts_vendor_date (vendor_id,receipt_date),
 CONSTRAINT fk_PO_receipts_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_receipts_vendor FOREIGN KEY(vendor_id) REFERENCES PO_vendors(id),
 CONSTRAINT fk_PO_receipts_location FOREIGN KEY(location_id) REFERENCES PO_locations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_purchase_receipt_lines (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 receipt_id BIGINT UNSIGNED NOT NULL,
 erp_line_no INT NOT NULL,
 item_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
 unit_cost DECIMAL(18,6) NULL,
 line_amount DECIMAL(18,4) NULL,
 lot_no VARCHAR(80) NULL,
 expiration_date DATE NULL,
 UNIQUE KEY uq_PO_receipt_line (receipt_id,erp_line_no),
 CONSTRAINT fk_PO_receipt_lines_receipt FOREIGN KEY(receipt_id) REFERENCES PO_purchase_receipts(id),
 CONSTRAINT fk_PO_receipt_lines_item FOREIGN KEY(item_id) REFERENCES PO_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Supplier collaboration
CREATE TABLE IF NOT EXISTS PO_vendor_po_acknowledgements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 purchase_order_id BIGINT UNSIGNED NOT NULL,
 vendor_id BIGINT UNSIGNED NOT NULL,
 vendor_user_id BIGINT UNSIGNED NULL,
 status ENUM('PENDING','ACKNOWLEDGED','PARTIAL','REJECTED','CHANGE_REQUESTED') NOT NULL DEFAULT 'PENDING',
 acknowledged_at DATETIME NULL,
 promised_delivery_date DATE NULL,
 vendor_reference VARCHAR(100) NULL,
 comment TEXT 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_vendor_ack_po (purchase_order_id),
 CONSTRAINT fk_PO_vendor_ack_po FOREIGN KEY(purchase_order_id) REFERENCES PO_purchase_orders(id),
 CONSTRAINT fk_PO_vendor_ack_vendor FOREIGN KEY(vendor_id) REFERENCES PO_vendors(id),
 CONSTRAINT fk_PO_vendor_ack_user FOREIGN KEY(vendor_user_id) REFERENCES PO_vendor_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_vendor_po_line_acknowledgements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 acknowledgement_id BIGINT UNSIGNED NOT NULL,
 purchase_order_line_id BIGINT UNSIGNED NOT NULL,
 confirmed_qty DECIMAL(18,4) NULL,
 confirmed_unit_cost DECIMAL(18,6) NULL,
 promised_delivery_date DATE NULL,
 status ENUM('CONFIRMED','PARTIAL','UNAVAILABLE','PRICE_CHANGE','DATE_CHANGE') NOT NULL DEFAULT 'CONFIRMED',
 comment VARCHAR(500) NULL,
 UNIQUE KEY uq_PO_vendor_line_ack (acknowledgement_id,purchase_order_line_id),
 CONSTRAINT fk_PO_vendor_line_ack_header FOREIGN KEY(acknowledgement_id) REFERENCES PO_vendor_po_acknowledgements(id),
 CONSTRAINT fk_PO_vendor_line_ack_line FOREIGN KEY(purchase_order_line_id) REFERENCES PO_purchase_order_lines(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_advance_shipping_notices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 vendor_id BIGINT UNSIGNED NOT NULL,
 purchase_order_id BIGINT UNSIGNED NOT NULL,
 asn_no VARCHAR(80) NOT NULL,
 vendor_reference VARCHAR(100) NULL,
 shipment_date DATE NULL,
 expected_arrival_date DATE NULL,
 carrier_name VARCHAR(120) NULL,
 vehicle_no VARCHAR(80) NULL,
 tracking_no VARCHAR(120) NULL,
 status ENUM('DRAFT','SUBMITTED','ACCEPTED','IN_TRANSIT','RECEIVED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
 created_by_vendor_user_id 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_asn_no (company_id,asn_no),
 CONSTRAINT fk_PO_asn_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_asn_vendor FOREIGN KEY(vendor_id) REFERENCES PO_vendors(id),
 CONSTRAINT fk_PO_asn_po FOREIGN KEY(purchase_order_id) REFERENCES PO_purchase_orders(id),
 CONSTRAINT fk_PO_asn_user FOREIGN KEY(created_by_vendor_user_id) REFERENCES PO_vendor_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_asn_lines (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 asn_id BIGINT UNSIGNED NOT NULL,
 purchase_order_line_id BIGINT UNSIGNED NOT NULL,
 shipped_qty DECIMAL(18,4) NOT NULL,
 lot_no VARCHAR(80) NULL,
 production_date DATE NULL,
 expiration_date DATE NULL,
 UNIQUE KEY uq_PO_asn_line (asn_id,purchase_order_line_id),
 CONSTRAINT fk_PO_asn_lines_asn FOREIGN KEY(asn_id) REFERENCES PO_advance_shipping_notices(id),
 CONSTRAINT fk_PO_asn_lines_po_line FOREIGN KEY(purchase_order_line_id) REFERENCES PO_purchase_order_lines(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Contracts, budgets, service levels and compliance
CREATE TABLE IF NOT EXISTS PO_vendor_contracts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 vendor_id BIGINT UNSIGNED NOT NULL,
 contract_no VARCHAR(80) NOT NULL,
 start_date DATE NOT NULL,
 end_date DATE NOT NULL,
 currency_code VARCHAR(10) NOT NULL DEFAULT 'SAR',
 minimum_value DECIMAL(18,4) NULL,
 maximum_value DECIMAL(18,4) NULL,
 payment_terms_code VARCHAR(50) NULL,
 status ENUM('DRAFT','ACTIVE','EXPIRED','SUSPENDED','CLOSED') NOT NULL DEFAULT 'ACTIVE',
 attachment_path VARCHAR(500) 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_vendor_contract (company_id,contract_no),
 CONSTRAINT fk_PO_contract_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_contract_vendor FOREIGN KEY(vendor_id) REFERENCES PO_vendors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_vendor_contract_lines (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 contract_id BIGINT UNSIGNED NOT NULL,
 item_id BIGINT UNSIGNED NOT NULL,
 agreed_price DECIMAL(18,6) NOT NULL,
 min_qty DECIMAL(18,4) NULL,
 max_qty DECIMAL(18,4) NULL,
 lead_time_days INT NULL,
 rebate_percent DECIMAL(8,4) NULL,
 valid_from DATE NULL,
 valid_to DATE NULL,
 UNIQUE KEY uq_PO_contract_line (contract_id,item_id),
 CONSTRAINT fk_PO_contract_lines_contract FOREIGN KEY(contract_id) REFERENCES PO_vendor_contracts(id),
 CONSTRAINT fk_PO_contract_lines_item FOREIGN KEY(item_id) REFERENCES PO_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_budgets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 fiscal_year SMALLINT NOT NULL,
 fiscal_period TINYINT NULL,
 location_id BIGINT UNSIGNED NULL,
 category_id BIGINT UNSIGNED NULL,
 budget_amount DECIMAL(18,4) NOT NULL,
 committed_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
 consumed_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
 warning_percent DECIMAL(8,4) NOT NULL DEFAULT 85,
 hard_stop TINYINT(1) NOT NULL DEFAULT 0,
 active 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,
 KEY idx_PO_budget_scope (company_id,fiscal_year,fiscal_period,location_id,category_id),
 CONSTRAINT fk_PO_budget_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_budget_location FOREIGN KEY(location_id) REFERENCES PO_locations(id),
 CONSTRAINT fk_PO_budget_category FOREIGN KEY(category_id) REFERENCES PO_categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_service_level_targets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 location_id BIGINT UNSIGNED NULL,
 category_id BIGINT UNSIGNED NULL,
 item_id BIGINT UNSIGNED NULL,
 target_availability_percent DECIMAL(8,4) NOT NULL DEFAULT 97,
 max_stockout_days DECIMAL(8,2) NOT NULL DEFAULT 1,
 priority INT NOT NULL DEFAULT 100,
 active TINYINT(1) NOT NULL DEFAULT 1,
 KEY idx_PO_service_level_scope (company_id,item_id,category_id,location_id,priority),
 CONSTRAINT fk_PO_service_level_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_service_level_location FOREIGN KEY(location_id) REFERENCES PO_locations(id),
 CONSTRAINT fk_PO_service_level_category FOREIGN KEY(category_id) REFERENCES PO_categories(id),
 CONSTRAINT fk_PO_service_level_item FOREIGN KEY(item_id) REFERENCES PO_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_item_substitutes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 item_id BIGINT UNSIGNED NOT NULL,
 substitute_item_id BIGINT UNSIGNED NOT NULL,
 priority INT NOT NULL DEFAULT 100,
 conversion_factor DECIMAL(18,6) NOT NULL DEFAULT 1,
 active TINYINT(1) NOT NULL DEFAULT 1,
 UNIQUE KEY uq_PO_item_substitute (company_id,item_id,substitute_item_id),
 CONSTRAINT fk_PO_substitute_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_substitute_item FOREIGN KEY(item_id) REFERENCES PO_items(id),
 CONSTRAINT fk_PO_substitute_alt FOREIGN KEY(substitute_item_id) REFERENCES PO_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data quality, anomaly detection and simulations
CREATE TABLE IF NOT EXISTS PO_data_quality_issues (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 entity_type VARCHAR(60) NOT NULL,
 entity_id BIGINT UNSIGNED NULL,
 issue_code VARCHAR(80) NOT NULL,
 severity ENUM('INFO','WARNING','HIGH','CRITICAL') NOT NULL DEFAULT 'WARNING',
 title_ar VARCHAR(255) NOT NULL,
 details_ar TEXT NULL,
 detected_value VARCHAR(500) NULL,
 expected_rule VARCHAR(500) NULL,
 status ENUM('OPEN','ACKNOWLEDGED','FIXED','IGNORED') NOT NULL DEFAULT 'OPEN',
 detected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 resolved_at DATETIME NULL,
 KEY idx_PO_dq_open (company_id,status,severity,issue_code),
 CONSTRAINT fk_PO_dq_company FOREIGN KEY(company_id) REFERENCES PO_companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_anomalies (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 anomaly_type ENUM('PRICE','QUANTITY','SALES','STOCK','VENDOR','LEAD_TIME','DUPLICATE_PO','BUDGET','EXPIRY','OTHER') NOT NULL,
 entity_type VARCHAR(60) NOT NULL,
 entity_id BIGINT UNSIGNED NULL,
 item_id BIGINT UNSIGNED NULL,
 vendor_id BIGINT UNSIGNED NULL,
 location_id BIGINT UNSIGNED NULL,
 severity ENUM('LOW','MEDIUM','HIGH','CRITICAL') NOT NULL DEFAULT 'MEDIUM',
 score DECIMAL(8,4) NULL,
 title_ar VARCHAR(255) NOT NULL,
 explanation_ar TEXT NOT NULL,
 evidence_json JSON NULL,
 status ENUM('OPEN','REVIEWED','RESOLVED','FALSE_POSITIVE') NOT NULL DEFAULT 'OPEN',
 detected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 resolved_at DATETIME NULL,
 KEY idx_PO_anomaly_control_tower (company_id,status,severity,anomaly_type,detected_at),
 CONSTRAINT fk_PO_anomaly_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_anomaly_item FOREIGN KEY(item_id) REFERENCES PO_items(id),
 CONSTRAINT fk_PO_anomaly_vendor FOREIGN KEY(vendor_id) REFERENCES PO_vendors(id),
 CONSTRAINT fk_PO_anomaly_location FOREIGN KEY(location_id) REFERENCES PO_locations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_what_if_scenarios (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(180) NOT NULL,
 scenario_type ENUM('DEMAND_UPLIFT','VENDOR_DELAY','PRICE_CHANGE','EVENT','LOCATION_CLOSURE','CUSTOM') NOT NULL,
 parameters_json JSON NOT NULL,
 status ENUM('DRAFT','RUNNING','COMPLETED','FAILED') NOT NULL DEFAULT 'DRAFT',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 completed_at DATETIME NULL,
 CONSTRAINT fk_PO_scenario_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_scenario_user FOREIGN KEY(created_by) REFERENCES PO_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_what_if_results (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 scenario_id BIGINT UNSIGNED NOT NULL,
 metric_code VARCHAR(80) NOT NULL,
 metric_value DECIMAL(24,6) NULL,
 details_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_PO_whatif_results (scenario_id,metric_code),
 CONSTRAINT fk_PO_whatif_result_scenario FOREIGN KEY(scenario_id) REFERENCES PO_what_if_scenarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS PO_notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NULL,
 vendor_user_id BIGINT UNSIGNED NULL,
 channel ENUM('IN_APP','EMAIL','WHATSAPP','PUSH') NOT NULL DEFAULT 'IN_APP',
 notification_type VARCHAR(80) NOT NULL,
 title VARCHAR(255) NOT NULL,
 body TEXT NOT NULL,
 entity_type VARCHAR(60) NULL,
 entity_id BIGINT UNSIGNED NULL,
 status ENUM('PENDING','SENT','FAILED','READ') NOT NULL DEFAULT 'PENDING',
 sent_at DATETIME NULL,
 read_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_PO_notifications_vendor (vendor_user_id,status,created_at),
 CONSTRAINT fk_PO_notification_company FOREIGN KEY(company_id) REFERENCES PO_companies(id),
 CONSTRAINT fk_PO_notification_user FOREIGN KEY(user_id) REFERENCES PO_users(id),
 CONSTRAINT fk_PO_notification_vendor_user FOREIGN KEY(vendor_user_id) REFERENCES PO_vendor_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add integrations as separate domains (intentionally not a monolithic ERP API)
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',3000,15,'LastModifiedDateTime',85);

SET FOREIGN_KEY_CHECKS=1;
