-- ============================================================
--  CareBill — GST e-invoicing. Run once on an existing database:
--    mysql carebill < sql/einvoice.sql
--  (schema.sql already contains this for fresh installs)
-- ============================================================
ALTER TABLE clients ADD COLUMN pincode VARCHAR(10) AFTER state_code;

CREATE TABLE einvoices (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  invoice_id      INT NOT NULL,
  irn             CHAR(64) NULL,
  ack_no          VARCHAR(30) NULL,
  ack_date        DATETIME NULL,
  signed_qr       MEDIUMTEXT NULL,
  signed_invoice  MEDIUMTEXT NULL,
  ewb_no          VARCHAR(20) NULL,
  status          ENUM('pending','active','cancelled','failed') DEFAULT 'pending',
  error           VARCHAR(500) NULL,
  attempts        INT DEFAULT 0,
  driver          VARCHAR(20) NULL,
  payload         MEDIUMTEXT NULL,
  cancelled_at    DATETIME NULL,
  cancel_reason   VARCHAR(200) NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_einv_invoice (invoice_id),
  UNIQUE KEY uq_einv_irn (irn),
  INDEX ix_einv_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (k,v) VALUES
 ('einv_enabled','0'), ('einv_driver','manual'), ('einv_gstin',''),
 ('einv_base_url','https://einv-apisandbox.nic.in'), ('einv_username',''), ('einv_password',''),
 ('einv_client_id',''), ('einv_client_secret',''), ('einv_public_key',''),
 ('einv_gsp_url',''), ('einv_gsp_cancel_url',''), ('einv_gsp_headers',''),
 ('einv_30day_rule','0'), ('einv_token_cache','')
ON DUPLICATE KEY UPDATE k=k;