-- migration_add_parties.sql
USE financeiro;

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,
  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;

ALTER TABLE fin_documents
  ADD COLUMN counterparty_id INT NULL AFTER counterparty_name;

ALTER TABLE fin_documents
  ADD CONSTRAINT fk_doc_party FOREIGN KEY(counterparty_id) REFERENCES fin_parties(id);

CREATE INDEX idx_docs_party ON fin_documents(counterparty_id);


-- Novos campos de endereço (opcional)
ALTER TABLE fin_parties
  ADD COLUMN zip_code VARCHAR(20) NULL AFTER phone,
  ADD COLUMN address VARCHAR(160) NULL AFTER zip_code,
  ADD COLUMN address_number VARCHAR(20) NULL AFTER address,
  ADD COLUMN district VARCHAR(80) NULL AFTER address_number,
  ADD COLUMN city VARCHAR(80) NULL AFTER district,
  ADD COLUMN state VARCHAR(40) NULL AFTER city,
  ADD COLUMN country VARCHAR(60) NULL AFTER state;
