-- ============================================================
-- IPS — Contact call log migration
-- Run once against `ips_db` (e.g. via phpMyAdmin).
-- Stores calls placed to contacts from the Contacts module +
-- the note captured in the call widget ("Save & close").
-- ============================================================

CREATE TABLE IF NOT EXISTS contact_calls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT NULL,                     -- contacts.id (nullable: ad-hoc numbers)
  user_id INT UNSIGNED NULL,               -- users.id (who placed the call)
  phone VARCHAR(30) NOT NULL,
  call_sid VARCHAR(100) NULL,              -- Twilio client CallSid
  direction ENUM('outbound','inbound') DEFAULT 'outbound',
  status VARCHAR(30) DEFAULT 'initiated',  -- initiated | completed | failed | no_answer ...
  duration INT DEFAULT 0,                   -- seconds
  note TEXT NULL,
  started_at DATETIME NULL,
  ended_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_cc_contact (contact_id),
  INDEX idx_cc_sid (call_sid),
  CONSTRAINT fk_cc_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  CONSTRAINT fk_cc_user    FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
