-- MySQL 8.0; run once in the selected database, after backup and stopping writes.
-- Task 1 cannot deploy alone: Tasks 5A-5C must supply explicit dimensions in INSERTs.
-- MySQL DDL implicitly commits. A failed/running version requires manual recovery
-- from backup; never delete its version record and blindly rerun partial DDL.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS schema_migrations (
 version VARCHAR(100) PRIMARY KEY, status VARCHAR(20) NOT NULL,
 started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, completed_at DATETIME NULL,
 details JSON NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS schema_migration_reconciliations (
 version VARCHAR(100) NOT NULL, phase VARCHAR(10) NOT NULL, organization_id BIGINT NOT NULL,
 on_hand DECIMAL(30,6) NOT NULL, reserved DECIMAL(30,6) NOT NULL, locked DECIMAL(30,6) NOT NULL,
 in_transit DECIMAL(30,6) NOT NULL, inbound_count BIGINT NOT NULL, outbound_count BIGINT NOT NULL,
 application_count BIGINT NOT NULL, stock_log_count BIGINT NOT NULL, captured_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(version,phase,organization_id), FOREIGN KEY(organization_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DELIMITER $$
DROP PROCEDURE IF EXISTS upgrade_company_isolation_20261008$$
CREATE PROCEDURE upgrade_company_isolation_20261008()
BEGIN
 DECLARE lock_acquired INT DEFAULT 0;
 DECLARE version_started BOOLEAN DEFAULT FALSE;
 DECLARE migration_version VARCHAR(100) DEFAULT '2026-10-08-company-master-data-isolation';
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
  IF version_started THEN
   UPDATE schema_migrations SET status='failed',details=JSON_SET(details,'$.recovery','DDL may have committed; restore backup or review partial schema before retry') WHERE version=BINARY migration_version;
  END IF;
  IF lock_acquired=1 THEN DO RELEASE_LOCK(CONCAT(DATABASE(),':company_isolation')); END IF;
  RESIGNAL;
 END;
 SELECT GET_LOCK(CONCAT(DATABASE(),':company_isolation'),0) INTO lock_acquired;
 IF lock_acquired IS NULL OR lock_acquired<>1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Company isolation migration is already running'; END IF;
 IF EXISTS(SELECT 1 FROM schema_migrations WHERE version=BINARY migration_version) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Company isolation migration version already recorded; do not rerun';
 END IF;

 -- Generated from scripts/checks/company-master-data-integrity.sql; edit that source.
 -- Sync: pwsh -NoProfile -File scripts/sync-company-integrity.ps1 (verify with -Check).
-- BEGIN GENERATED COMPANY INTEGRITY CHECKS
DROP TEMPORARY TABLE IF EXISTS tmp_company_application_dimensions;
CREATE TEMPORARY TABLE tmp_company_application_dimensions AS
SELECT a.id, COUNT(ai.id) item_count, COUNT(DISTINCT m.organization_id) organization_count,
       MIN(m.organization_id) organization_id, COUNT(m.id) material_count
FROM material_applications a LEFT JOIN application_items ai ON ai.application_id=a.id
LEFT JOIN materials m ON m.id=ai.material_id GROUP BY a.id;

DROP TEMPORARY TABLE IF EXISTS tmp_company_log_dimensions;
CREATE TEMPORARY TABLE tmp_company_log_dimensions AS
SELECT s.id, o.organization_id, o.warehouse_id, o.location_id
FROM stock_logs s JOIN inbound_orders o ON s.related_order_type='inbound' AND s.related_order_id=o.id
JOIN inbound_items i ON i.inbound_order_id=o.id AND i.material_id=s.material_id
WHERE s.type='in' AND s.quantity=i.quantity
UNION
SELECT s.id, o.organization_id, o.warehouse_id, o.location_id
FROM stock_logs s JOIN outbound_orders o ON s.related_order_type='outbound' AND s.related_order_id=o.id
JOIN outbound_items i ON i.outbound_order_id=o.id AND i.material_id=s.material_id
WHERE s.type='out' AND s.quantity=i.quantity
UNION
SELECT s.id, d.organization_id, b.warehouse_id, b.location_id
FROM stock_logs s JOIN material_applications a ON s.related_order_type='application' AND s.related_order_id=a.id
JOIN tmp_company_application_dimensions d ON d.id=a.id AND d.item_count>0 AND d.organization_count=1 AND d.material_count=d.item_count
JOIN application_items i ON i.application_id=a.id AND i.material_id=s.material_id
JOIN inventory_balances b ON b.organization_id=d.organization_id AND b.material_id=s.material_id
JOIN warehouses w ON w.id=b.warehouse_id AND w.organization_id=d.organization_id
JOIN warehouse_locations l ON l.id=b.location_id AND l.warehouse_id=w.id
WHERE s.type='out' AND a.status='approved' AND s.quantity=i.quantity;

DROP TEMPORARY TABLE IF EXISTS tmp_company_integrity_anomalies;
CREATE TEMPORARY TABLE tmp_company_integrity_anomalies (
 issue VARCHAR(100) NOT NULL, entity_id BIGINT NOT NULL, detail VARCHAR(500) NOT NULL
);
INSERT INTO tmp_company_integrity_anomalies
SELECT 'category_parent',c.id,'Parent category belongs to another company or is missing'
FROM material_categories c LEFT JOIN material_categories p ON p.id=c.parent_id
WHERE c.parent_id IS NOT NULL AND (p.id IS NULL OR p.organization_id<>c.organization_id);
INSERT INTO tmp_company_integrity_anomalies
SELECT 'material_category_unit_type',m.id,'Material category, base unit or type belongs to another company or is missing'
FROM materials m LEFT JOIN material_categories c ON c.id=m.category_id
LEFT JOIN units u ON u.id=m.base_unit_id LEFT JOIN material_types mt ON mt.id=m.material_type_id
WHERE c.id IS NULL OR c.organization_id<>m.organization_id
OR (m.base_unit_id IS NOT NULL AND (u.id IS NULL OR u.organization_id<>m.organization_id))
OR (m.material_type_id IS NOT NULL AND (mt.id IS NULL OR mt.organization_id<>m.organization_id));
INSERT INTO tmp_company_integrity_anomalies
SELECT 'rule_category',r.id,'Code rule category belongs to another company or is missing'
FROM code_rules r LEFT JOIN material_categories c ON c.id=r.category_id
WHERE c.id IS NULL OR c.organization_id<>r.organization_id;
INSERT INTO tmp_company_integrity_anomalies
SELECT 'duplicate_default',user_id,'User has more than one default organization'
FROM user_organizations WHERE is_default<>0 GROUP BY user_id HAVING COUNT(*)>1;
INSERT INTO tmp_company_integrity_anomalies
SELECT 'user_without_access',u.id,'User has no enabled company grant and no enabled super_admin role'
FROM users u WHERE NOT EXISTS(SELECT 1 FROM user_organizations g JOIN organizations o ON o.id=g.organization_id AND o.status='active' WHERE g.user_id=u.id)
AND NOT EXISTS(SELECT 1 FROM user_roles ur JOIN roles r ON r.id=ur.role_id AND r.code='super_admin' AND r.enabled=1 WHERE ur.user_id=u.id);
INSERT INTO tmp_company_integrity_anomalies
SELECT 'multiple_warehouses',organization_id,'Company has multiple warehouses (including inactive ones)'
FROM warehouses GROUP BY organization_id HAVING COUNT(*)>1;
INSERT INTO tmp_company_integrity_anomalies
SELECT 'multiple_locations',warehouse_id,'Warehouse has multiple locations (including inactive ones)'
FROM warehouse_locations GROUP BY warehouse_id HAVING COUNT(*)>1;
INSERT INTO tmp_company_integrity_anomalies
SELECT 'inventory_dimensions',b.id,'Inventory material, company, warehouse or location do not agree'
FROM inventory_balances b LEFT JOIN materials m ON m.id=b.material_id LEFT JOIN warehouses w ON w.id=b.warehouse_id
LEFT JOIN warehouse_locations l ON l.id=b.location_id LEFT JOIN organizations o ON o.id=b.organization_id
WHERE o.id IS NULL OR m.id IS NULL OR w.id IS NULL OR l.id IS NULL OR m.organization_id<>b.organization_id OR w.organization_id<>b.organization_id OR l.warehouse_id<>b.warehouse_id;
INSERT INTO tmp_company_integrity_anomalies
SELECT 'warning_dimensions',r.id,'Warning material, company or warehouse do not agree'
FROM stock_warning_rules r LEFT JOIN materials m ON m.id=r.material_id LEFT JOIN warehouses w ON w.id=r.warehouse_id
WHERE m.id IS NULL OR w.id IS NULL OR m.organization_id<>r.organization_id OR w.organization_id<>r.organization_id;
INSERT INTO tmp_company_integrity_anomalies
SELECT 'inbound_dimensions',o.id,'Inbound company, warehouse, location, materials or actual inventory dimensions do not agree'
FROM inbound_orders o LEFT JOIN warehouses w ON w.id=o.warehouse_id LEFT JOIN warehouse_locations l ON l.id=o.location_id
WHERE w.id IS NULL OR l.id IS NULL OR w.organization_id<>o.organization_id OR l.warehouse_id<>o.warehouse_id
OR NOT EXISTS(SELECT 1 FROM inbound_items i WHERE i.inbound_order_id=o.id)
OR EXISTS(SELECT 1 FROM inbound_items i LEFT JOIN materials m ON m.id=i.material_id
 WHERE i.inbound_order_id=o.id AND (m.id IS NULL OR m.organization_id<>o.organization_id
 OR NOT EXISTS(SELECT 1 FROM inventory_balances b WHERE b.material_id=i.material_id AND b.organization_id=o.organization_id AND b.warehouse_id=o.warehouse_id AND b.location_id=o.location_id)));
INSERT INTO tmp_company_integrity_anomalies
SELECT 'outbound_dimensions',o.id,'Outbound company, warehouse, location, materials or actual inventory dimensions do not agree'
FROM outbound_orders o LEFT JOIN warehouses w ON w.id=o.warehouse_id LEFT JOIN warehouse_locations l ON l.id=o.location_id
WHERE w.id IS NULL OR l.id IS NULL OR w.organization_id<>o.organization_id OR l.warehouse_id<>o.warehouse_id
OR NOT EXISTS(SELECT 1 FROM outbound_items i WHERE i.outbound_order_id=o.id)
OR EXISTS(SELECT 1 FROM outbound_items i LEFT JOIN materials m ON m.id=i.material_id
 WHERE i.outbound_order_id=o.id AND (m.id IS NULL OR m.organization_id<>o.organization_id
 OR NOT EXISTS(SELECT 1 FROM inventory_balances b WHERE b.material_id=i.material_id AND b.organization_id=o.organization_id AND b.warehouse_id=o.warehouse_id AND b.location_id=o.location_id)));
INSERT INTO tmp_company_integrity_anomalies
SELECT 'application_dimensions',id,'Application has empty, missing or mixed-company material details'
FROM tmp_company_application_dimensions WHERE item_count=0 OR organization_count<>1 OR material_count<>item_count;
INSERT INTO tmp_company_integrity_anomalies
SELECT 'unattributable_stock_log',s.id,'Log has no unique verified material/document/company/warehouse/location association'
FROM stock_logs s LEFT JOIN tmp_company_log_dimensions d ON d.id=s.id
LEFT JOIN materials m ON m.id=s.material_id LEFT JOIN warehouses w ON w.id=d.warehouse_id LEFT JOIN warehouse_locations l ON l.id=d.location_id
GROUP BY s.id,m.id,m.organization_id
HAVING COUNT(d.id)<>1 OR COUNT(m.id)=0 OR SUM(d.organization_id<>m.organization_id OR w.organization_id<>d.organization_id OR l.warehouse_id<>d.warehouse_id)>0;

-- The same check file must work before the columns exist and after backfill.
SET @company_check_sql = IF(
 (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='stock_logs' AND column_name IN ('organization_id','warehouse_id','location_id'))=3,
 'INSERT INTO tmp_company_integrity_anomalies SELECT ''stored_stock_log_dimensions'',s.id,''Stored log dimensions differ from verified source document'' FROM stock_logs s LEFT JOIN tmp_company_log_dimensions d ON d.id=s.id WHERE d.id IS NULL OR s.organization_id IS NULL OR s.warehouse_id IS NULL OR s.location_id IS NULL OR s.organization_id<>d.organization_id OR s.warehouse_id<>d.warehouse_id OR s.location_id<>d.location_id',
 'DO 0');
PREPARE company_check FROM @company_check_sql;
EXECUTE company_check;
DEALLOCATE PREPARE company_check;
SET @company_check_sql = IF(
 (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='material_applications' AND column_name='organization_id')=1,
 'INSERT INTO tmp_company_integrity_anomalies SELECT ''stored_application_dimensions'',a.id,''Stored application company differs from verified detail materials'' FROM material_applications a JOIN tmp_company_application_dimensions d ON d.id=a.id WHERE a.organization_id IS NULL OR a.organization_id<>d.organization_id',
 'DO 0');
PREPARE company_check FROM @company_check_sql;
EXECUTE company_check;
DEALLOCATE PREPARE company_check;

-- END GENERATED COMPANY INTEGRITY CHECKS
 IF EXISTS(SELECT 1 FROM tmp_company_integrity_anomalies) THEN
  SELECT issue,entity_id,detail FROM tmp_company_integrity_anomalies ORDER BY issue,entity_id;
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Company isolation integrity anomalies; run scripts/checks/company-master-data-integrity.sql and repair before upgrade';
 END IF;

 INSERT INTO schema_migrations(version,status,details) VALUES(migration_version,'running',JSON_OBJECT('version',migration_version,'executor',CURRENT_USER()));
 SET version_started=TRUE;
 INSERT INTO schema_migration_reconciliations(version,phase,organization_id,on_hand,reserved,locked,in_transit,inbound_count,outbound_count,application_count,stock_log_count)
 SELECT migration_version,'before',o.id,
 COALESCE((SELECT SUM(on_hand) FROM inventory_balances WHERE organization_id=o.id),0),
 COALESCE((SELECT SUM(reserved) FROM inventory_balances WHERE organization_id=o.id),0),
 COALESCE((SELECT SUM(locked) FROM inventory_balances WHERE organization_id=o.id),0),
 COALESCE((SELECT SUM(in_transit) FROM inventory_balances WHERE organization_id=o.id),0),
 (SELECT COUNT(*) FROM inbound_orders WHERE organization_id=o.id),
 (SELECT COUNT(*) FROM outbound_orders WHERE organization_id=o.id),
 (SELECT COUNT(*) FROM tmp_company_application_dimensions WHERE organization_id=o.id),
 (SELECT COUNT(*) FROM tmp_company_log_dimensions WHERE organization_id=o.id)
 FROM organizations o;

 ALTER TABLE user_organizations ADD COLUMN default_user_id BIGINT GENERATED ALWAYS AS (CASE WHEN is_default THEN user_id ELSE NULL END) VIRTUAL, ADD UNIQUE KEY uk_user_organizations_default(default_user_id);
 ALTER TABLE warehouses ADD UNIQUE KEY uk_warehouses_organization(organization_id);
 ALTER TABLE warehouse_locations ADD UNIQUE KEY uk_warehouse_locations_warehouse(warehouse_id);
 CREATE TABLE organization_audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,actor_user_id BIGINT NOT NULL,organization_id BIGINT NULL,
  action VARCHAR(100) NOT NULL,old_value JSON NULL,new_value JSON NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_organization_audit_org_created(organization_id,created_at),KEY idx_organization_audit_actor_created(actor_user_id,created_at),
  FOREIGN KEY(actor_user_id) REFERENCES users(id),FOREIGN KEY(organization_id) REFERENCES organizations(id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 ALTER TABLE material_applications ADD COLUMN organization_id BIGINT NULL;
 ALTER TABLE stock_logs ADD COLUMN organization_id BIGINT NULL,ADD COLUMN warehouse_id BIGINT NULL,ADD COLUMN location_id BIGINT NULL;
 UPDATE material_applications a JOIN tmp_company_application_dimensions d ON d.id=a.id SET a.organization_id=d.organization_id;
 UPDATE stock_logs s JOIN tmp_company_log_dimensions d ON d.id=s.id SET s.organization_id=d.organization_id,s.warehouse_id=d.warehouse_id,s.location_id=d.location_id;
 IF EXISTS(SELECT 1 FROM material_applications WHERE organization_id IS NULL)
 OR EXISTS(SELECT 1 FROM stock_logs WHERE organization_id IS NULL OR warehouse_id IS NULL OR location_id IS NULL) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Company isolation backfill incomplete; restore backup and investigate';
 END IF;
 ALTER TABLE material_applications MODIFY organization_id BIGINT NOT NULL,
 ADD KEY idx_material_applications_org_user_created(organization_id,user_id,created_at),
 ADD KEY idx_material_applications_org_status_created(organization_id,status,created_at),
 ADD FOREIGN KEY(organization_id) REFERENCES organizations(id);
 ALTER TABLE stock_logs MODIFY organization_id BIGINT NOT NULL,MODIFY warehouse_id BIGINT NOT NULL,MODIFY location_id BIGINT NOT NULL,
 ADD KEY idx_stock_logs_org_material_created(organization_id,material_id,created_at),
 ADD FOREIGN KEY(organization_id) REFERENCES organizations(id),ADD FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),ADD FOREIGN KEY(location_id) REFERENCES warehouse_locations(id);
 ALTER TABLE inbound_orders ALTER COLUMN organization_id DROP DEFAULT,ALTER COLUMN warehouse_id DROP DEFAULT,ALTER COLUMN location_id DROP DEFAULT;
 ALTER TABLE outbound_orders ALTER COLUMN organization_id DROP DEFAULT,ALTER COLUMN warehouse_id DROP DEFAULT,ALTER COLUMN location_id DROP DEFAULT;

 INSERT INTO schema_migration_reconciliations(version,phase,organization_id,on_hand,reserved,locked,in_transit,inbound_count,outbound_count,application_count,stock_log_count)
 SELECT migration_version,'after',o.id,
 COALESCE((SELECT SUM(on_hand) FROM inventory_balances WHERE organization_id=o.id),0),
 COALESCE((SELECT SUM(reserved) FROM inventory_balances WHERE organization_id=o.id),0),
 COALESCE((SELECT SUM(locked) FROM inventory_balances WHERE organization_id=o.id),0),
 COALESCE((SELECT SUM(in_transit) FROM inventory_balances WHERE organization_id=o.id),0),
 (SELECT COUNT(*) FROM inbound_orders WHERE organization_id=o.id),
 (SELECT COUNT(*) FROM outbound_orders WHERE organization_id=o.id),
 (SELECT COUNT(*) FROM material_applications WHERE organization_id=o.id),
 (SELECT COUNT(*) FROM stock_logs WHERE organization_id=o.id)
 FROM organizations o;
 IF EXISTS(SELECT 1 FROM schema_migration_reconciliations b JOIN schema_migration_reconciliations a
 ON a.version=b.version AND a.organization_id=b.organization_id AND a.phase='after'
 WHERE b.version=BINARY migration_version AND b.phase='before'
 AND (a.on_hand<>b.on_hand OR a.reserved<>b.reserved OR a.locked<>b.locked OR a.in_transit<>b.in_transit
 OR a.inbound_count<>b.inbound_count OR a.outbound_count<>b.outbound_count OR a.application_count<>b.application_count OR a.stock_log_count<>b.stock_log_count)) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Company isolation reconciliation mismatch; restore backup and investigate';
 END IF;
 UPDATE schema_migrations SET status='completed',completed_at=NOW() WHERE version=BINARY migration_version;
 SET version_started=FALSE;
 DO RELEASE_LOCK(CONCAT(DATABASE(),':company_isolation'));
END$$
CALL upgrade_company_isolation_20261008()$$
DROP PROCEDURE upgrade_company_isolation_20261008$$
DELIMITER ;