CREATE TABLE IF NOT EXISTS departments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 parent_id BIGINT UNSIGNED NULL,
 code VARCHAR(40) NOT NULL,
 name VARCHAR(150) NOT NULL,
 description TEXT NULL,
 manager_user_id BIGINT UNSIGNED NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 UNIQUE KEY uq_department_unit_code(unit_id,code),
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(parent_id) REFERENCES departments(id) ON DELETE SET NULL,
 FOREIGN KEY(manager_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS teams (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 department_id BIGINT UNSIGNED NULL,
 name VARCHAR(150) NOT NULL,
 team_type ENUM('commercial','administrative','finance','compliance','operations','logistics','executive','other') NOT NULL DEFAULT 'other',
 leader_user_id BIGINT UNSIGNED 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,
 FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL,
 FOREIGN KEY(leader_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(unit_id,department_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS team_members (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 team_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 member_role ENUM('leader','member','assistant','observer') NOT NULL DEFAULT 'member',
 joined_at DATETIME NOT NULL,
 UNIQUE KEY uq_team_user(team_id,user_id),
 FOREIGN KEY(team_id) REFERENCES teams(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(user_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS permission_profiles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 description TEXT NULL,
 is_system TINYINT(1) NOT NULL DEFAULT 0,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 UNIQUE KEY uq_permission_profile_name(name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS permission_profile_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 profile_id BIGINT UNSIGNED NOT NULL,
 module_key VARCHAR(80) NOT NULL,
 can_view TINYINT(1) NOT NULL DEFAULT 0,
 can_create TINYINT(1) NOT NULL DEFAULT 0,
 can_edit TINYINT(1) NOT NULL DEFAULT 0,
 can_delete TINYINT(1) NOT NULL DEFAULT 0,
 can_approve TINYINT(1) NOT NULL DEFAULT 0,
 can_export TINYINT(1) NOT NULL DEFAULT 0,
 UNIQUE KEY uq_profile_module(profile_id,module_key),
 FOREIGN KEY(profile_id) REFERENCES permission_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_unit_governance (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NOT NULL,
 permission_profile_id BIGINT UNSIGNED NULL,
 department_id BIGINT UNSIGNED NULL,
 data_scope ENUM('own','team','department','unit','all_units') NOT NULL DEFAULT 'own',
 approval_limit DECIMAL(20,2) NULL,
 approval_currency CHAR(3) NOT NULL DEFAULT 'USD',
 may_delegate TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 UNIQUE KEY uq_user_unit_governance(user_id,unit_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(unit_id) REFERENCES business_units(id) ON DELETE CASCADE,
 FOREIGN KEY(permission_profile_id) REFERENCES permission_profiles(id) ON DELETE SET NULL,
 FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS dashboard_widgets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 widget_key VARCHAR(80) NOT NULL,
 name VARCHAR(120) NOT NULL,
 description VARCHAR(255) NULL,
 minimum_role VARCHAR(40) NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 UNIQUE KEY uq_widget_key(widget_key)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_dashboard_widgets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 widget_id BIGINT UNSIGNED NOT NULL,
 position_order INT NOT NULL DEFAULT 0,
 column_span TINYINT UNSIGNED NOT NULL DEFAULT 1,
 is_visible TINYINT(1) NOT NULL DEFAULT 1,
 settings_json JSON NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_user_widget(user_id,widget_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(widget_id) REFERENCES dashboard_widgets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT IGNORE INTO permission_profiles(id,name,description,is_system,status,created_at) VALUES
(1,'Diretoria','Acesso executivo amplo, aprovações e relatórios.',1,'active',NOW()),
(2,'Gerência','Gestão da unidade, equipe e operações.',1,'active',NOW()),
(3,'Força de vendas','Clientes, pipeline, propostas e pedidos de venda.',1,'active',NOW()),
(4,'Administrativo','Cadastros, documentos e suporte operacional.',1,'active',NOW()),
(5,'Financeiro','Instrumentos, pagamentos, faturas e fluxo financeiro.',1,'active',NOW()),
(6,'Compliance','KYC, due diligence, documentos e alertas.',1,'active',NOW()),
(7,'Consulta','Acesso somente para visualização.',1,'active',NOW());

INSERT IGNORE INTO dashboard_widgets(widget_key,name,description,minimum_role,status) VALUES
('executive_summary','Resumo executivo','Indicadores consolidados da unidade ativa.',NULL,'active'),
('sales_pipeline','Pipeline comercial','Quantidade e valor por etapa comercial.',NULL,'active'),
('pending_approvals','Aprovações pendentes','Solicitações aguardando decisão.',NULL,'active'),
('financial_position','Posição financeira','Entradas, saídas e saldo registrado.',NULL,'active'),
('inventory_position','Posição de estoque','Lotes, disponibilidade e reservas.',NULL,'active'),
('risk_compliance','Riscos e compliance','Alertas abertos e casos de compliance.',NULL,'active'),
('logistics_status','Situação logística','Embarques por status.',NULL,'active'),
('my_tasks','Minha equipe e tarefas','Resumo de equipes e responsabilidades.',NULL,'active');