-- V014 - External stakeholder portals, secure data rooms and collaboration
CREATE TABLE IF NOT EXISTS external_organizations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 counterparty_id BIGINT UNSIGNED NULL,
 organization_type ENUM('supplier','bank','broker','carrier','insurer','auditor','inspection','other') NOT NULL,
 legal_name VARCHAR(190) NOT NULL,
 trade_name VARCHAR(190) NULL,
 country_code CHAR(2) NULL,
 tax_id VARCHAR(80) NULL,
 status ENUM('pending','active','suspended','blocked') NOT NULL DEFAULT 'pending',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,organization_type,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS external_portal_users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 organization_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(190) NOT NULL,
 email VARCHAR(190) NOT NULL,
 phone VARCHAR(60) NULL,
 job_title VARCHAR(150) NULL,
 password_hash VARCHAR(255) NULL,
 locale VARCHAR(10) NOT NULL DEFAULT 'en',
 role ENUM('organization_admin','manager','operator','viewer','auditor') NOT NULL DEFAULT 'viewer',
 status ENUM('invited','active','suspended','blocked') NOT NULL DEFAULT 'invited',
 must_change_password TINYINT(1) NOT NULL DEFAULT 1,
 last_login_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(organization_id) REFERENCES external_organizations(id) ON DELETE CASCADE,
 UNIQUE KEY uq_external_user_email(email),
 INDEX(organization_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS external_portal_invitations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 organization_id BIGINT UNSIGNED NOT NULL,
 email VARCHAR(190) NOT NULL,
 role VARCHAR(40) NOT NULL DEFAULT 'viewer',
 token_hash CHAR(64) NOT NULL,
 expires_at DATETIME NOT NULL,
 accepted_at DATETIME NULL,
 invited_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(organization_id) REFERENCES external_organizations(id) ON DELETE CASCADE,
 FOREIGN KEY(invited_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_external_invitation_token(token_hash),
 INDEX(email,expires_at,accepted_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS external_operation_access (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 external_user_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NOT NULL,
 access_level ENUM('view','collaborate','upload','approve') NOT NULL DEFAULT 'view',
 granted_by BIGINT UNSIGNED NULL,
 granted_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 FOREIGN KEY(external_user_id) REFERENCES external_portal_users(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(granted_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_external_operation_access(external_user_id,operation_id),
 INDEX(operation_id,revoked_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS data_rooms (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 operation_id BIGINT UNSIGNED NULL,
 name VARCHAR(190) NOT NULL,
 description TEXT NULL,
 classification ENUM('internal','confidential','restricted','external') NOT NULL DEFAULT 'confidential',
 status ENUM('draft','active','locked','archived') NOT NULL DEFAULT 'draft',
 expires_at DATETIME NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,operation_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS data_room_members (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 data_room_id BIGINT UNSIGNED NOT NULL,
 external_user_id BIGINT UNSIGNED NOT NULL,
 permission ENUM('view','download','upload','manage') NOT NULL DEFAULT 'view',
 watermark_enabled TINYINT(1) NOT NULL DEFAULT 1,
 download_allowed TINYINT(1) NOT NULL DEFAULT 0,
 expires_at DATETIME NULL,
 added_by BIGINT UNSIGNED NULL,
 added_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 FOREIGN KEY(data_room_id) REFERENCES data_rooms(id) ON DELETE CASCADE,
 FOREIGN KEY(external_user_id) REFERENCES external_portal_users(id) ON DELETE CASCADE,
 FOREIGN KEY(added_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_data_room_member(data_room_id,external_user_id),
 INDEX(external_user_id,revoked_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS data_room_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 data_room_id BIGINT UNSIGNED NOT NULL,
 operation_document_id BIGINT UNSIGNED NULL,
 title VARCHAR(190) NOT NULL,
 item_type ENUM('document','notice','link') NOT NULL DEFAULT 'document',
 external_url VARCHAR(500) NULL,
 notes TEXT NULL,
 version_label VARCHAR(30) NULL,
 checksum_sha256 CHAR(64) NULL,
 visible_from DATETIME NULL,
 visible_until DATETIME NULL,
 uploaded_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(data_room_id) REFERENCES data_rooms(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(uploaded_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(data_room_id,created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS external_portal_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NULL,
 data_room_id BIGINT UNSIGNED NULL,
 internal_user_id BIGINT UNSIGNED NULL,
 external_user_id BIGINT UNSIGNED NULL,
 direction ENUM('internal_to_external','external_to_internal','system') NOT NULL,
 subject VARCHAR(190) NULL,
 body TEXT NOT NULL,
 read_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(data_room_id) REFERENCES data_rooms(id) ON DELETE CASCADE,
 FOREIGN KEY(internal_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(external_user_id) REFERENCES external_portal_users(id) ON DELETE SET NULL,
 INDEX(external_user_id,read_at,created_at), INDEX(operation_id,created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS external_portal_access_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 external_user_id BIGINT UNSIGNED NULL,
 organization_id BIGINT UNSIGNED NULL,
 action VARCHAR(100) NOT NULL,
 entity_type VARCHAR(80) NULL,
 entity_id BIGINT UNSIGNED NULL,
 ip_address VARCHAR(64) NULL,
 user_agent VARCHAR(500) NULL,
 metadata_json JSON NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(external_user_id) REFERENCES external_portal_users(id) ON DELETE SET NULL,
 FOREIGN KEY(organization_id) REFERENCES external_organizations(id) ON DELETE SET NULL,
 INDEX(external_user_id,created_at), INDEX(entity_type,entity_id)
) ENGINE=InnoDB;