-- ============================================================
--  CareBill — collection targets, SMS, data retention, Tally sync.
--  Existing database:  mysql carebill < sql/pendings.sql
-- ============================================================
ALTER TABLE templates      MODIFY channel ENUM('email','whatsapp','call_script','sms') DEFAULT 'email';
ALTER TABLE reminder_rules MODIFY channel ENUM('email','whatsapp','call','internal','sms') DEFAULT 'email';
ALTER TABLE outbox         MODIFY channel ENUM('email','whatsapp','call','sms') DEFAULT 'email';
ALTER TABLE templates ADD COLUMN dlt_template_id VARCHAR(30) NULL;

-- what each collector is expected to bring in, month by month
CREATE TABLE collection_targets (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  user_id         INT NOT NULL,
  month           CHAR(7) NOT NULL,                -- YYYY-MM
  amount          DECIMAL(14,2) NOT NULL,
  note            VARCHAR(200),
  set_by          INT NULL,
  UNIQUE KEY uq_target (user_id, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- numbers that asked for no more SMS
CREATE TABLE sms_optouts (
  phone           VARCHAR(20) PRIMARY KEY,
  source          VARCHAR(40),
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- what came back from Tally, so nothing is imported twice
CREATE TABLE tally_imports (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  voucher_key     VARCHAR(120) NOT NULL,           -- Tally's GUID, or type+number+date
  voucher_type    VARCHAR(40),
  voucher_no      VARCHAR(60),
  voucher_date    DATE,
  party_ledger    VARCHAR(200),
  amount          DECIMAL(14,2),
  client_id       INT NULL,
  payment_id      BIGINT NULL,
  status          ENUM('imported','unmatched','skipped') DEFAULT 'imported',
  note            VARCHAR(255),
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_tally_voucher (voucher_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE clients ADD COLUMN tally_ledger VARCHAR(200) NULL;

INSERT INTO settings (k,v) VALUES
 ('sms_driver','log'), ('sms_sender_id',''), ('sms_entity_id',''), ('sms_api_key',''), ('sms_api_url','https://control.msg91.com/api/v5/flow'),
 ('retention_message_months','24'), ('retention_login_months','12'), ('retention_events_months','12'),
 ('retention_upload_days','90'), ('retention_financial_years','8'),
 ('tally_url',''), ('tally_company',''), ('tally_sync_enabled','0')
ON DUPLICATE KEY UPDATE k=k;
ALTER TABLE payments MODIFY source ENUM('bank_import','manual','gateway','tally') DEFAULT 'manual';
ALTER TABLE payments ADD COLUMN tally_pushed_at DATETIME NULL;
ALTER TABLE invoices ADD COLUMN tally_pushed_at DATETIME NULL;