CREATE TABLE IF NOT EXISTS communication_threads (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 subject VARCHAR(190) NOT NULL,
 channel ENUM('internal','email','whatsapp','phone','other') NOT NULL DEFAULT 'internal',
 priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 status ENUM('open','waiting','resolved','archived') NOT NULL DEFAULT 'open',
 created_by BIGINT UNSIGNED NULL,
 assigned_user_id 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(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,status,priority), INDEX(operation_id), INDEX(client_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS communication_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 thread_id BIGINT UNSIGNED NOT NULL,
 sender_user_id BIGINT UNSIGNED NULL,
 direction ENUM('internal','incoming','outgoing') NOT NULL DEFAULT 'internal',
 body TEXT NOT NULL,
 recipient VARCHAR(190) NULL,
 sent_at DATETIME NOT NULL,
 read_at DATETIME NULL,
 FOREIGN KEY(thread_id) REFERENCES communication_threads(id) ON DELETE CASCADE,
 FOREIGN KEY(sender_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(thread_id,sent_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS business_meetings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 title VARCHAR(190) NOT NULL,
 meeting_type ENUM('internal','client','supplier','bank','broker','compliance','other') NOT NULL DEFAULT 'internal',
 starts_at DATETIME NOT NULL,
 ends_at DATETIME NULL,
 location VARCHAR(255) NULL,
 meeting_url VARCHAR(500) NULL,
 agenda TEXT NULL,
 minutes_text LONGTEXT NULL,
 decisions_text LONGTEXT NULL,
 status ENUM('scheduled','held','cancelled') NOT NULL DEFAULT 'scheduled',
 organizer_user_id 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(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL,
 FOREIGN KEY(organizer_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,starts_at,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS meeting_participants (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 meeting_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NULL,
 external_name VARCHAR(150) NULL,
 external_email VARCHAR(190) NULL,
 attendance_status ENUM('invited','accepted','declined','attended','absent') NOT NULL DEFAULT 'invited',
 FOREIGN KEY(meeting_id) REFERENCES business_meetings(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(meeting_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS service_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 request_code VARCHAR(40) NOT NULL,
 category ENUM('commercial','document','finance','banking','logistics','compliance','technical','other') NOT NULL DEFAULT 'other',
 subject VARCHAR(190) NOT NULL,
 description TEXT NOT NULL,
 priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
 status ENUM('new','triage','in_progress','waiting_client','resolved','closed','cancelled') NOT NULL DEFAULT 'new',
 requester_name VARCHAR(150) NULL,
 requester_email VARCHAR(190) NULL,
 assigned_user_id BIGINT UNSIGNED NULL,
 due_at DATETIME NULL,
 resolved_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 UNIQUE KEY uq_service_request_code(request_code),
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL,
 FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,status,priority), INDEX(client_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS knowledge_articles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NULL,
 category VARCHAR(100) NOT NULL,
 title VARCHAR(190) NOT NULL,
 slug VARCHAR(190) NOT NULL,
 summary TEXT NULL,
 content LONGTEXT NOT NULL,
 language_code VARCHAR(10) NOT NULL DEFAULT 'pt',
 visibility ENUM('internal','unit','all_units','client') NOT NULL DEFAULT 'internal',
 status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
 author_user_id BIGINT UNSIGNED NULL,
 published_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 UNIQUE KEY uq_knowledge_slug_lang(slug,language_code),
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE SET NULL,
 FOREIGN KEY(author_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(status,category,language_code)
) ENGINE=InnoDB;

INSERT IGNORE INTO dashboard_widgets(widget_key,name,description,minimum_role,status) VALUES
('communications_overview','Comunicações e atendimento','Conversas abertas, solicitações e próximas reuniões.',NULL,'active');