ALTER TABLE document_templates ADD COLUMN IF NOT EXISTS subject VARCHAR(190) NULL AFTER name;
ALTER TABLE document_templates ADD COLUMN IF NOT EXISTS response_to_document_type VARCHAR(80) NULL AFTER operation_type;
ALTER TABLE document_templates ADD COLUMN IF NOT EXISTS auto_create_on_stage VARCHAR(40) NULL AFTER workflow_order;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS parent_document_id BIGINT UNSIGNED NULL AFTER template_id;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS sent_at DATETIME NULL AFTER signature_status;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS completed_at DATETIME NULL AFTER sent_at;
ALTER TABLE operation_documents ADD COLUMN IF NOT EXISTS signer_access_expires_at DATETIME NULL AFTER public_token;
ALTER TABLE operation_documents ADD INDEX idx_public_token (public_token);
ALTER TABLE signatures ADD COLUMN IF NOT EXISTS signer_document_number VARCHAR(120) NULL AFTER signer_phone;
ALTER TABLE signatures ADD COLUMN IF NOT EXISTS signer_country VARCHAR(100) NULL AFTER signer_document_number;
ALTER TABLE signatures ADD COLUMN IF NOT EXISTS otp_verified_at DATETIME NULL AFTER consent_text;
ALTER TABLE signatures ADD COLUMN IF NOT EXISTS evidence_json JSON NULL AFTER verification_hash;
CREATE TABLE IF NOT EXISTS operation_stage_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 from_stage VARCHAR(40) NULL,
 to_stage VARCHAR(40) NOT NULL,
 changed_by BIGINT UNSIGNED NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(changed_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,created_at)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS document_workflows (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 operation_type VARCHAR(80) NOT NULL,
 locale VARCHAR(5) NOT NULL DEFAULT 'en',
 status VARCHAR(30) NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS document_workflow_steps (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workflow_id BIGINT UNSIGNED NOT NULL,
 template_id BIGINT UNSIGNED NOT NULL,
 step_order INT NOT NULL DEFAULT 0,
 stage VARCHAR(40) NOT NULL DEFAULT 'documentation',
 trigger_type VARCHAR(40) NOT NULL DEFAULT 'manual',
 requires_previous_signed TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(workflow_id) REFERENCES document_workflows(id) ON DELETE CASCADE,
 FOREIGN KEY(template_id) REFERENCES document_templates(id) ON DELETE CASCADE,
 UNIQUE KEY uq_workflow_step(workflow_id,template_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS notification_queue (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 channel ENUM('email','whatsapp','internal') NOT NULL DEFAULT 'email',
 recipient VARCHAR(190) NOT NULL,
 subject VARCHAR(190) NULL,
 body LONGTEXT NOT NULL,
 entity_type VARCHAR(80) NULL,
 entity_id BIGINT UNSIGNED NULL,
 status VARCHAR(30) NOT NULL DEFAULT 'pending',
 attempts INT NOT NULL DEFAULT 0,
 scheduled_at DATETIME NOT NULL,
 sent_at DATETIME NULL,
 last_error TEXT NULL,
 created_at DATETIME NOT NULL,
 INDEX(status,scheduled_at)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS operation_tasks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(190) NOT NULL,
 description TEXT NULL,
 assigned_user_id BIGINT UNSIGNED NULL,
 due_date DATE NULL,
 priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 status ENUM('pending','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS instrument_expenses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 bank_instrument_id BIGINT UNSIGNED NOT NULL,
 instrument_loan_id BIGINT UNSIGNED NULL,
 expense_type VARCHAR(100) NOT NULL,
 description VARCHAR(255) NULL,
 amount DECIMAL(20,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 due_date DATE NULL,
 paid_at DATETIME NULL,
 status VARCHAR(30) NOT NULL DEFAULT 'pending',
 created_at DATETIME NOT NULL,
 FOREIGN KEY(bank_instrument_id) REFERENCES bank_instruments(id) ON DELETE CASCADE,
 FOREIGN KEY(instrument_loan_id) REFERENCES instrument_loans(id) ON DELETE SET NULL
) ENGINE=InnoDB;