-- ============================================================
--  CareBill — TDS credit tracking.
--  Existing database:  mysql carebill < sql/tds.sql
--
--  Clients deduct TDS and pay it to the government on our behalf. The money is
--  only ours again when the deduction appears in Form 26AS, which depends on the
--  client filing their return correctly. Until then it is deducted, unclaimed,
--  and invisible — so it gets tracked like any other receivable.
-- ============================================================

CREATE TABLE tds_certificates (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  fy_code         CHAR(5) NOT NULL,              -- 26-27
  quarter         ENUM('Q1','Q2','Q3','Q4') NOT NULL,
  certificate_no  VARCHAR(40),
  tan             VARCHAR(15),
  amount          DECIMAL(14,2) DEFAULT 0,       -- as per the certificate
  section         VARCHAR(10) DEFAULT '194J',
  received_on     DATE NULL,
  in_26as         TINYINT(1) DEFAULT 0,
  as26_amount     DECIMAL(14,2) NULL,
  file_path       VARCHAR(255) NULL,
  note            VARCHAR(255),
  created_by      INT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_tds_cert (client_id, fy_code, quarter),
  INDEX ix_tds_fy (fy_code, quarter)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE clients ADD COLUMN tan VARCHAR(15) NULL AFTER pan;

INSERT INTO settings (k,v) VALUES ('tds_chase_enabled','1'), ('tds_section','194J')
ON DUPLICATE KEY UPDATE k=k;