-- V013 - workflow, governance, authorized signers, secure verification and notifications
ALTER TABLE users ADD COLUMN IF NOT EXISTS may_sign_documents TINYINT(1) NOT NULL DEFAULT 0 AFTER status;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signer_title VARCHAR(150) NULL AFTER may_sign_documents;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signer_signature_path VARCHAR(255) NULL AFTER signer_title;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signer_initials_path VARCHAR(255) NULL AFTER signer_signature_path;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signer_authorized_at DATETIME NULL AFTER signer_initials_path;
ALTER TABLE users ADD COLUMN IF NOT EXISTS signer_authorized_by BIGINT UNSIGNED NULL AFTER signer_authorized_at;

ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS internal_signature_status ENUM('not_required','pending','requested','signed','rejected') NOT NULL DEFAULT 'pending' AFTER signature_status;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS internal_signer_user_id BIGINT UNSIGNED NULL AFTER internal_signature_status;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS internal_signature_requested_at DATETIME NULL AFTER internal_signer_user_id;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS internally_signed_at DATETIME NULL AFTER internal_signature_requested_at;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS content_hash CHAR(64) NULL AFTER internally_signed_at;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS verification_key CHAR(64) NULL AFTER content_hash;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS verification_code VARCHAR(40) NULL AFTER verification_key;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS issued_at DATETIME NULL AFTER verification_code;
ALTER TABLE operation_documents ADD UNIQUE KEY uq_document_verification_key(verification_key);
ALTER TABLE operation_documents ADD UNIQUE KEY uq_document_verification_code(verification_code);

CREATE TABLE IF NOT EXISTS internal_document_signatures (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_document_id BIGINT UNSIGNED NOT NULL,
 signer_user_id BIGINT UNSIGNED NOT NULL,
 signature_hash CHAR(64) NOT NULL,
 content_hash CHAR(64) NOT NULL,
 ip_address VARCHAR(64) NULL,
 user_agent VARCHAR(500) NULL,
 signed_at DATETIME NOT NULL,
 notes VARCHAR(500) NULL,
 FOREIGN KEY(operation_document_id) REFERENCES operation_documents(id) ON DELETE CASCADE,
 FOREIGN KEY(signer_user_id) REFERENCES users(id) ON DELETE RESTRICT,
 UNIQUE KEY uq_internal_document_signature(operation_document_id),
 INDEX(signer_user_id,signed_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 notification_type VARCHAR(60) NOT NULL DEFAULT 'general',
 title VARCHAR(190) NOT NULL,
 message TEXT NOT NULL,
 action_url VARCHAR(500) NULL,
 entity_type VARCHAR(80) NULL,
 entity_id BIGINT UNSIGNED NULL,
 priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 read_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(user_id,read_at,created_at), INDEX(entity_type,entity_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS document_validation_otps (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_document_id BIGINT UNSIGNED NOT NULL,
 email VARCHAR(190) NOT NULL,
 otp_hash VARCHAR(255) NOT NULL,
 expires_at DATETIME NOT NULL,
 attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
 verified_at DATETIME NULL,
 request_ip VARCHAR(64) NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(operation_document_id) REFERENCES operation_documents(id) ON DELETE CASCADE,
 INDEX(operation_document_id,email,expires_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS workflow_definitions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NULL,
 name VARCHAR(190) NOT NULL, operation_type VARCHAR(80) NOT NULL,
 description TEXT NULL, status ENUM('draft','active','inactive') NOT NULL DEFAULT 'draft',
 version INT UNSIGNED NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL, updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_type,status)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS workflow_steps (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, workflow_id BIGINT UNSIGNED NOT NULL,
 step_key VARCHAR(80) NOT NULL, name VARCHAR(190) NOT NULL, step_type ENUM('task','approval','document','signature','notification','condition','wait','system') NOT NULL DEFAULT 'task',
 sequence_no INT NOT NULL DEFAULT 0, responsible_role VARCHAR(80) NULL, settings_json JSON NULL, is_required TINYINT(1) NOT NULL DEFAULT 1,
 FOREIGN KEY(workflow_id) REFERENCES workflow_definitions(id) ON DELETE CASCADE,
 UNIQUE KEY uq_workflow_step_key(workflow_id,step_key), INDEX(workflow_id,sequence_no)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS automation_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NULL, name VARCHAR(190) NOT NULL,
 event_key VARCHAR(100) NOT NULL, conditions_json JSON NULL, actions_json JSON NOT NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL, updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(event_key,status)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS legal_clauses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category VARCHAR(100) NOT NULL, clause_code VARCHAR(60) NOT NULL,
 title VARCHAR(190) NOT NULL, language_code VARCHAR(10) NOT NULL DEFAULT 'en', content LONGTEXT NOT NULL,
 version INT UNSIGNED NOT NULL DEFAULT 1, status ENUM('draft','approved','archived') NOT NULL DEFAULT 'draft', created_by BIGINT UNSIGNED NULL, approved_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL, updated_at DATETIME NULL, UNIQUE KEY uq_clause_code_lang_version(clause_code,language_code,version)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS board_resolutions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NULL, resolution_no VARCHAR(60) NOT NULL,
 title VARCHAR(190) NOT NULL, resolution_text LONGTEXT NOT NULL, meeting_date DATE NOT NULL,
 status ENUM('draft','approved','rejected','archived') NOT NULL DEFAULT 'draft', created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL,
 UNIQUE KEY uq_resolution_no(resolution_no), FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS powers_of_attorney (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NULL, grantor VARCHAR(190) NOT NULL, attorney_name VARCHAR(190) NOT NULL,
 powers_text LONGTEXT NOT NULL, valid_from DATE NOT NULL, valid_until DATE NULL, spending_limit DECIMAL(18,2) NULL, currency CHAR(3) NULL,
 status ENUM('draft','active','expired','revoked') NOT NULL DEFAULT 'draft', document_id BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL, FOREIGN KEY(document_id) REFERENCES operation_documents(id) ON DELETE SET NULL
) ENGINE=InnoDB;

UPDATE users SET may_sign_documents=1, signer_authorized_at=NOW() WHERE role IN ('admin','director');