CREATE TABLE IF NOT EXISTS business_units (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(20) NOT NULL UNIQUE,
 name VARCHAR(150) NOT NULL,
 country VARCHAR(100) NOT NULL,
 city VARCHAR(100) NULL,
 unit_type ENUM('headquarters','branch','representative_office') NOT NULL DEFAULT 'branch',
 timezone VARCHAR(80) NOT NULL DEFAULT 'UTC',
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO business_units(code,name,country,city,unit_type,timezone,currency,status,created_at) VALUES
('CH-ZRH','Switzerland Headquarters','Switzerland','Zurich','headquarters','Europe/Zurich','CHF','active',NOW()),
('BR','Brazil Unit','Brazil',NULL,'branch','America/Sao_Paulo','BRL','active',NOW()),
('AR','Argentina Unit','Argentina',NULL,'branch','America/Argentina/Buenos_Aires','ARS','active',NOW()),
('CO','Colombia Unit','Colombia',NULL,'branch','America/Bogota','COP','active',NOW()),
('QA','Qatar Unit','Qatar','Doha','branch','Asia/Qatar','QAR','active',NOW()),
('AE-DXB','Dubai Unit','United Arab Emirates','Dubai','branch','Asia/Dubai','AED','active',NOW()),
('SY','Syria Unit','Syria',NULL,'branch','Asia/Damascus','USD','active',NOW()),
('SG','Singapore Unit','Singapore','Singapore','branch','Asia/Singapore','SGD','active',NOW());

CREATE TABLE IF NOT EXISTS user_units (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NOT NULL,
 access_role ENUM('director','manager','sales','administrative','finance','compliance','viewer') NOT NULL DEFAULT 'viewer',
 is_primary TINYINT(1) NOT NULL DEFAULT 0,
 can_view_all TINYINT(1) NOT NULL DEFAULT 0,
 can_edit TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uq_user_unit(user_id,unit_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 INDEX(unit_id,access_role)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_preferences (
 user_id BIGINT UNSIGNED PRIMARY KEY,
 theme ENUM('light','dark','system') NOT NULL DEFAULT 'light',
 accent_color VARCHAR(20) NOT NULL DEFAULT '#1261a0',
 sidebar_collapsed TINYINT(1) NOT NULL DEFAULT 0,
 dashboard_layout_json JSON NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_two_factor (
 user_id BIGINT UNSIGNED PRIMARY KEY,
 secret VARCHAR(64) NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 0,
 recovery_codes_json JSON NULL,
 confirmed_at DATETIME NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

ALTER TABLE operations ADD COLUMN IF NOT EXISTS unit_id BIGINT UNSIGNED NULL AFTER code;
ALTER TABLE operations ADD INDEX IF NOT EXISTS idx_operations_unit(unit_id);

CREATE TABLE IF NOT EXISTS unit_access_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NULL,
 action VARCHAR(80) NOT NULL,
 ip_address VARCHAR(64) NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 INDEX(user_id,created_at)
) ENGINE=InnoDB;
INSERT IGNORE INTO user_units(user_id,unit_id,access_role,is_primary,can_view_all,can_edit,created_at)
SELECT u.id,bu.id,CASE WHEN u.role='admin' THEN 'director' WHEN u.role='director' THEN 'director' ELSE 'viewer' END,1,1,1,NOW()
FROM users u JOIN business_units bu ON bu.code='CH-ZRH'
WHERE u.role IN ('admin','director');