CREATE TABLE IF NOT EXISTS work_tasks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 title VARCHAR(190) NOT NULL,
 description TEXT NULL,
 task_type ENUM('general','call','email','document','approval','compliance','finance','logistics','follow_up') NOT NULL DEFAULT 'general',
 priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 status ENUM('backlog','pending','in_progress','waiting','completed','cancelled') NOT NULL DEFAULT 'pending',
 assigned_user_id BIGINT UNSIGNED NULL,
 assigned_team_id BIGINT UNSIGNED NULL,
 created_by BIGINT UNSIGNED NULL,
 start_at DATETIME NULL,
 due_at DATETIME NULL,
 completed_at DATETIME NULL,
 estimated_minutes INT UNSIGNED NULL,
 progress TINYINT UNSIGNED NOT NULL DEFAULT 0,
 visibility ENUM('private','team','unit') NOT NULL DEFAULT 'unit',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(assigned_team_id) REFERENCES teams(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,status,due_at), INDEX(assigned_user_id,status), INDEX(operation_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS task_comments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 task_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NULL,
 comment_text TEXT NOT NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(task_id) REFERENCES work_tasks(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(task_id,created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS business_activities (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 user_id BIGINT UNSIGNED NULL,
 activity_type ENUM('note','call','email','meeting','visit','message','status_change','system') NOT NULL DEFAULT 'note',
 subject VARCHAR(190) NOT NULL,
 details TEXT NULL,
 activity_at DATETIME NOT NULL,
 duration_minutes INT UNSIGNED NULL,
 outcome VARCHAR(190) NULL,
 next_action VARCHAR(190) NULL,
 next_action_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,activity_at), INDEX(operation_id,activity_at), INDEX(client_id,activity_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sla_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 name VARCHAR(150) NOT NULL,
 entity_type ENUM('operation','document','approval','compliance','payment','shipment','task') NOT NULL,
 event_key VARCHAR(80) NOT NULL,
 priority ENUM('low','normal','high','critical','all') NOT NULL DEFAULT 'all',
 response_minutes INT UNSIGNED NOT NULL,
 resolution_minutes INT UNSIGNED NOT NULL,
 business_hours_only TINYINT(1) NOT NULL DEFAULT 1,
 escalation_role VARCHAR(50) NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 INDEX(unit_id,entity_type,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS responsibility_delegations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 delegator_user_id BIGINT UNSIGNED NOT NULL,
 delegate_user_id BIGINT UNSIGNED NOT NULL,
 delegation_type ENUM('tasks','approvals','operations','documents','all') NOT NULL DEFAULT 'tasks',
 starts_at DATETIME NOT NULL,
 ends_at DATETIME NOT NULL,
 reason VARCHAR(255) NULL,
 status ENUM('scheduled','active','revoked','expired') NOT NULL DEFAULT 'scheduled',
 created_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(delegator_user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(delegate_user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(unit_id,status,starts_at,ends_at), INDEX(delegate_user_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS task_status_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 task_id BIGINT UNSIGNED NOT NULL,
 changed_by BIGINT UNSIGNED NULL,
 old_status VARCHAR(40) NULL,
 new_status VARCHAR(40) NOT NULL,
 notes VARCHAR(255) NULL,
 changed_at DATETIME NOT NULL,
 FOREIGN KEY(task_id) REFERENCES work_tasks(id) ON DELETE CASCADE,
 FOREIGN KEY(changed_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(task_id,changed_at)
) ENGINE=InnoDB;

INSERT IGNORE INTO dashboard_widgets(widget_key,name,description,minimum_role,status) VALUES
('task_productivity','Produtividade e prazos','Tarefas pessoais, atrasos e atividades próximas.',NULL,'active');