-- 004: financeiro
CREATE TABLE financial_accounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  type ENUM('CASH','BANK','GATEWAY','OTHER') NOT NULL DEFAULT 'CASH',
  active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_fin_accounts_name (company_id, name),
  CONSTRAINT fk_fa_company FOREIGN KEY (company_id) REFERENCES companies (id)
);
CREATE TABLE financial_categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  name VARCHAR(100) NOT NULL,
  type ENUM('INCOME','EXPENSE') NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_fin_categories (company_id, type, name),
  CONSTRAINT fk_fc_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_fc_parent FOREIGN KEY (parent_id) REFERENCES financial_categories (id)
);
CREATE TABLE cost_centers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_cost_centers (company_id, name),
  CONSTRAINT fk_cc_company FOREIGN KEY (company_id) REFERENCES companies (id)
);
CREATE TABLE accounts_payable (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  supplier_id BIGINT UNSIGNED NULL,
  category_id BIGINT UNSIGNED NULL,
  cost_center_id BIGINT UNSIGNED NULL,
  description VARCHAR(190) NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  paid_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  due_date DATE NOT NULL,
  paid_at DATETIME NULL,
  status ENUM('OPEN','PARTIAL','PAID','CANCELED') NOT NULL DEFAULT 'OPEN',
  installment_no SMALLINT UNSIGNED NULL,
  installment_total SMALLINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ap_uuid (uuid),
  KEY idx_ap_due (company_id, status, due_date),
  CONSTRAINT fk_ap_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_ap_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
  CONSTRAINT fk_ap_category FOREIGN KEY (category_id) REFERENCES financial_categories (id),
  CONSTRAINT fk_ap_cc FOREIGN KEY (cost_center_id) REFERENCES cost_centers (id)
);
CREATE TABLE accounts_receivable (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NULL,
  sale_id BIGINT UNSIGNED NULL,
  category_id BIGINT UNSIGNED NULL,
  cost_center_id BIGINT UNSIGNED NULL,
  description VARCHAR(190) NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  received_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  due_date DATE NOT NULL,
  received_at DATETIME NULL,
  status ENUM('OPEN','PARTIAL','RECEIVED','CANCELED') NOT NULL DEFAULT 'OPEN',
  installment_no SMALLINT UNSIGNED NULL,
  installment_total SMALLINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ar_uuid (uuid),
  KEY idx_ar_due (company_id, status, due_date),
  KEY idx_ar_customer (customer_id),
  CONSTRAINT fk_ar_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_ar_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
  CONSTRAINT fk_ar_sale FOREIGN KEY (sale_id) REFERENCES sales (id),
  CONSTRAINT fk_ar_category FOREIGN KEY (category_id) REFERENCES financial_categories (id),
  CONSTRAINT fk_ar_cc FOREIGN KEY (cost_center_id) REFERENCES cost_centers (id)
);
CREATE TABLE financial_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  account_id BIGINT UNSIGNED NULL,
  category_id BIGINT UNSIGNED NULL,
  cost_center_id BIGINT UNSIGNED NULL,
  type ENUM('INCOME','EXPENSE','TRANSFER_IN','TRANSFER_OUT') NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  description VARCHAR(190) NOT NULL,
  occurred_at DATETIME NOT NULL,
  reference_type VARCHAR(40) NULL,
  reference_id VARCHAR(64) NULL,
  reconciled TINYINT(1) NOT NULL DEFAULT 0,
  reconciled_at DATETIME NULL,
  user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_fe_uuid (uuid),
  KEY idx_fe_date (company_id, occurred_at),
  KEY idx_fe_ref (reference_type, reference_id),
  CONSTRAINT fk_fe_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_fe_account FOREIGN KEY (account_id) REFERENCES financial_accounts (id),
  CONSTRAINT fk_fe_category FOREIGN KEY (category_id) REFERENCES financial_categories (id),
  CONSTRAINT fk_fe_cc FOREIGN KEY (cost_center_id) REFERENCES cost_centers (id),
  CONSTRAINT fk_fe_user FOREIGN KEY (user_id) REFERENCES users (id)
);