-- schema.sql (Financeiro Pro v3 Futurista - Profissional)
-- Importar no MySQL/MariaDB:
--   mysql -u root -p < schema.sql

CREATE DATABASE IF NOT EXISTS financeiro
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE financeiro;

-- Usuários
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','finance','read') NOT NULL DEFAULT 'admin',
  profile_id INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Perfis de permissão (templates)
CREATE TABLE IF NOT EXISTS permission_profiles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_perm_profile (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS profile_permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  profile_id INT NOT NULL,
  perm_key VARCHAR(60) 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,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_profile_perm (profile_id, perm_key),
  CONSTRAINT fk_profile_perm_profile FOREIGN KEY(profile_id) REFERENCES permission_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

ALTER TABLE users
  ADD CONSTRAINT fk_users_profile FOREIGN KEY(profile_id) REFERENCES permission_profiles(id);

-- Permissões por usuário (override / cópia do perfil)
CREATE TABLE IF NOT EXISTS user_permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  perm_key VARCHAR(60) 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,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_user_perm (user_id, perm_key),
  CONSTRAINT fk_perm_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Histórico de permissões (usuário/perfil)
CREATE TABLE IF NOT EXISTS perm_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  subject_type ENUM('user','profile') NOT NULL,
  subject_id INT NOT NULL,
  changed_by_user_id INT NULL,
  source VARCHAR(40) NOT NULL, -- manual|preset|profile_apply|clone
  snapshot_json MEDIUMTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_perm_hist_subject (subject_type, subject_id),
  CONSTRAINT fk_perm_hist_user FOREIGN KEY(changed_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- Auditoria geral
CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(30) NOT NULL,     -- create|edit|delete|login|...
  entity VARCHAR(50) NOT NULL,     -- bank|document|settlement|user|...
  entity_id VARCHAR(60) NULL,
  meta_json MEDIUMTEXT NULL,
  ip VARCHAR(64) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_user (user_id, created_at),
  INDEX idx_audit_entity (entity, created_at),
  CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- Configurações
CREATE TABLE IF NOT EXISTS app_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  k VARCHAR(80) NOT NULL UNIQUE,
  v TEXT NULL,
  is_secret TINYINT(1) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Recuperação de senha
CREATE TABLE IF NOT EXISTS password_resets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pr_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_pr_hash (token_hash),
  INDEX idx_pr_exp (expires_at)
) ENGINE=InnoDB;

-- Bancos
CREATE TABLE IF NOT EXISTS banks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bank_number VARCHAR(10) NOT NULL,
  bank_name VARCHAR(120) NOT NULL,
  agency VARCHAR(20) NOT NULL,
  account_number VARCHAR(30) NOT NULL,
  account_type ENUM('corrente','poupanca') NOT NULL,
  initial_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  current_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_bank (bank_number, agency, account_number, account_type)
) ENGINE=InnoDB;

-- Categorias
CREATE TABLE IF NOT EXISTS fin_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  type ENUM('PAGAR','RECEBER','AMBOS') NOT NULL DEFAULT 'AMBOS',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_cat (name)
) ENGINE=InnoDB;

-- Centros de custo
CREATE TABLE IF NOT EXISTS fin_cost_centers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_cc (name)
) ENGINE=InnoDB;

-- Plano de contas
CREATE TABLE IF NOT EXISTS fin_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(140) NOT NULL,
  type ENUM('RECEITA','DESPESA','ATIVO','PASSIVO','PATRIMONIO') NOT NULL,
  parent_id INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_acc_code (code),
  CONSTRAINT fk_acc_parent FOREIGN KEY(parent_id) REFERENCES fin_accounts(id)
) ENGINE=InnoDB;

-- Clientes / Fornecedores
CREATE TABLE IF NOT EXISTS fin_parties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  party_type ENUM('CLIENTE','FORNECEDOR','AMBOS') NOT NULL DEFAULT 'AMBOS',
  name VARCHAR(140) NOT NULL,
  doc VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  zip_code VARCHAR(20) NULL,
  address VARCHAR(160) NULL,
  address_number VARCHAR(20) NULL,
  district VARCHAR(80) NULL,
  city VARCHAR(80) NULL,
  state VARCHAR(40) NULL,
  country VARCHAR(60) NULL,
  notes VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  INDEX idx_party_type (party_type),
  INDEX idx_party_name (name)
) ENGINE=InnoDB;

-- Documentos (lançamento)
CREATE TABLE IF NOT EXISTS fin_documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  doc_type ENUM('PAGAR','RECEBER') NOT NULL,
  counterparty_name VARCHAR(120) NOT NULL,
  counterparty_id INT NULL,
  description VARCHAR(255) NOT NULL,
  category_id INT NULL,
  cost_center_id INT NULL,
  account_id INT NULL,
  doc_number VARCHAR(60) NULL,
  notes VARCHAR(255) NULL,
  issue_date DATE NOT NULL,
  total_amount DECIMAL(15,2) NOT NULL,
  installments_count INT NOT NULL DEFAULT 1,
  status ENUM('ABERTO','QUITADO','CANCELADO') NOT NULL DEFAULT 'ABERTO',
  default_bank_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  CONSTRAINT fk_doc_bank FOREIGN KEY (default_bank_id) REFERENCES banks(id),
  CONSTRAINT fk_doc_party FOREIGN KEY (counterparty_id) REFERENCES fin_parties(id),
  CONSTRAINT fk_doc_category FOREIGN KEY (category_id) REFERENCES fin_categories(id),
  CONSTRAINT fk_doc_costcenter FOREIGN KEY (cost_center_id) REFERENCES fin_cost_centers(id),
  CONSTRAINT fk_doc_account FOREIGN KEY (account_id) REFERENCES fin_accounts(id)
) ENGINE=InnoDB;

CREATE INDEX idx_docs_type_status ON fin_documents(doc_type, status);
CREATE INDEX idx_docs_party ON fin_documents(counterparty_id);

-- Parcelas
CREATE TABLE IF NOT EXISTS fin_installments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  document_id INT NOT NULL,
  installment_no INT NOT NULL,
  due_date DATE NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  paid_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  status ENUM('ABERTO','PARCIAL','BAIXADO','CANCELADO') NOT NULL DEFAULT 'ABERTO',
  settled_at DATETIME NULL,
  bank_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  CONSTRAINT fk_inst_doc FOREIGN KEY (document_id) REFERENCES fin_documents(id) ON DELETE CASCADE,
  CONSTRAINT fk_inst_bank FOREIGN KEY (bank_id) REFERENCES banks(id),
  UNIQUE KEY uk_doc_parcela (document_id, installment_no),
  INDEX idx_inst_status_due (status, due_date),
  INDEX idx_inst_bank (bank_id)
) ENGINE=InnoDB;

-- Baixas (parciais)
CREATE TABLE IF NOT EXISTS fin_settlements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  installment_id INT NOT NULL,
  bank_id INT NOT NULL,
  direction ENUM('CREDITO','DEBITO') NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  note VARCHAR(255) NULL,
  reversed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_set_inst FOREIGN KEY(installment_id) REFERENCES fin_installments(id) ON DELETE CASCADE,
  CONSTRAINT fk_set_bank FOREIGN KEY(bank_id) REFERENCES banks(id),
  INDEX idx_set_inst (installment_id),
  INDEX idx_set_bank (bank_id),
  INDEX idx_set_occ (occurred_at)
) ENGINE=InnoDB;

-- Razão bancária
CREATE TABLE IF NOT EXISTS bank_ledger (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bank_id INT NOT NULL,
  settlement_id INT NULL,
  entry_type ENUM('CREDITO','DEBITO') NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  description VARCHAR(255) NOT NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_led_bank FOREIGN KEY (bank_id) REFERENCES banks(id),
  CONSTRAINT fk_led_set FOREIGN KEY (settlement_id) REFERENCES fin_settlements(id),
  INDEX idx_led_set (settlement_id),
  INDEX idx_led_occ (occurred_at)
) ENGINE=InnoDB;

-- Conciliação
CREATE TABLE IF NOT EXISTS bank_statement_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bank_id INT NOT NULL,
  posted_date DATE NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  description VARCHAR(255) NOT NULL,
  external_id VARCHAR(80) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_bse_bank FOREIGN KEY (bank_id) REFERENCES banks(id),
  INDEX idx_bse_bank_date (bank_id, posted_date),
  INDEX idx_bse_amt (amount)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bank_reconciliations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  statement_entry_id INT NOT NULL,
  ledger_id INT NOT NULL,
  matched_by ENUM('AUTO','MANUAL') NOT NULL DEFAULT 'MANUAL',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_recon_statement (statement_entry_id),
  UNIQUE KEY uk_recon_ledger (ledger_id),
  CONSTRAINT fk_rec_stmt FOREIGN KEY(statement_entry_id) REFERENCES bank_statement_entries(id) ON DELETE CASCADE,
  CONSTRAINT fk_rec_led FOREIGN KEY(ledger_id) REFERENCES bank_ledger(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- IA: categorias e prompts
CREATE TABLE IF NOT EXISTS prompt_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_prompt_cat (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS prompts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  name VARCHAR(140) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  CONSTRAINT fk_prompt_cat FOREIGN KEY(category_id) REFERENCES prompt_categories(id)
) ENGINE=InnoDB;

CREATE INDEX idx_prompts_cat ON prompts(category_id);
CREATE INDEX idx_prompts_active ON prompts(is_active);

-- Relatórios IA
CREATE TABLE IF NOT EXISTS ai_reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  provider ENUM('openai','gemini') NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  prompt_id INT NULL,
  prompt_name VARCHAR(140) NULL,
  prompt_body MEDIUMTEXT NULL,
  prompt_hash CHAR(64) NOT NULL,
  result_json MEDIUMTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_air_user FOREIGN KEY(user_id) REFERENCES users(id),
  CONSTRAINT fk_air_prompt FOREIGN KEY(prompt_id) REFERENCES prompts(id),
  INDEX idx_air_period (period_start, period_end)
) ENGINE=InnoDB;
