-- ============================================================
-- IPS — Contacts module migration
-- Run this once against the `ips_db` database (e.g. via phpMyAdmin).
-- Creates the shared, org-wide contacts directory used by the
-- Contacts module (ContactsController / routes/contacts.php).
-- ============================================================

CREATE TABLE IF NOT EXISTS contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name  VARCHAR(100) NOT NULL,
  last_name   VARCHAR(100) NULL,
  phone       VARCHAR(30)  NOT NULL,
  alt_phone   VARCHAR(30)  NULL,
  email       VARCHAR(150) NULL,
  company     VARCHAR(150) NULL,
  job_title   VARCHAR(100) NULL,
  group_name  VARCHAR(80)  NULL,          -- organize: Clients, Vendors, Family, ...
  is_favorite TINYINT(1)   DEFAULT 0,
  notes       TEXT NULL,
  taxpayer_id INT NULL,                    -- optional future link to taxpayers
  created_by  INT UNSIGNED NULL,           -- audit only; visibility is global (matches users.id)
  status ENUM('active','inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_phone (phone),
  INDEX idx_group (group_name),
  CONSTRAINT fk_contacts_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
