-- Call Tracking CRM — Phase 1 Schema
-- Run this in phpMyAdmin (SQL tab) on database: zsziykvi_calltrack

-- ============================
-- USERS & ROLES
-- ============================
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','agent') NOT NULL DEFAULT 'agent',
    twilio_client_identity VARCHAR(100) DEFAULT NULL, -- used for browser click-to-call routing later
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================
-- CAMPAIGNS (marketing sources — each gets its own tracking number)
-- ============================
CREATE TABLE campaigns (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================
-- TWILIO TRACKING NUMBERS
-- ============================
CREATE TABLE tracking_numbers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT UNSIGNED DEFAULT NULL,
    phone_number VARCHAR(20) NOT NULL UNIQUE,   -- E.164 format, e.g. +15551234567
    twilio_sid VARCHAR(64) NOT NULL,            -- Twilio's PhoneNumber SID
    friendly_name VARCHAR(150) DEFAULT NULL,
    forward_to_user_id INT UNSIGNED DEFAULT NULL, -- which agent this number rings by default
    ivr_enabled TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
    FOREIGN KEY (forward_to_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================
-- CONTACTS / LEADS
-- ============================
CREATE TABLE contacts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) DEFAULT NULL,
    last_name VARCHAR(100) DEFAULT NULL,
    phone VARCHAR(20) NOT NULL,        -- E.164 format
    email VARCHAR(150) DEFAULT NULL,
    company VARCHAR(150) DEFAULT NULL,
    stage ENUM('new','contacted','qualified','won','lost') NOT NULL DEFAULT 'new',
    campaign_id INT UNSIGNED DEFAULT NULL,      -- how they came in
    assigned_to INT UNSIGNED DEFAULT NULL,      -- which agent owns this lead
    notes TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone (phone),
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================
-- CALL LOGS
-- ============================
CREATE TABLE calls (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    twilio_call_sid VARCHAR(64) NOT NULL UNIQUE,
    contact_id INT UNSIGNED DEFAULT NULL,
    tracking_number_id INT UNSIGNED DEFAULT NULL,
    handled_by INT UNSIGNED DEFAULT NULL,       -- agent who took/made the call
    direction ENUM('inbound','outbound') NOT NULL,
    from_number VARCHAR(20) NOT NULL,
    to_number VARCHAR(20) NOT NULL,
    status VARCHAR(30) DEFAULT NULL,             -- completed, no-answer, busy, failed, etc.
    duration_seconds INT UNSIGNED DEFAULT 0,
    recording_url VARCHAR(500) DEFAULT NULL,
    disposition VARCHAR(50) DEFAULT NULL,        -- e.g. interested, voicemail, wrong-number (agent-set)
    started_at DATETIME DEFAULT NULL,
    ended_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
    FOREIGN KEY (tracking_number_id) REFERENCES tracking_numbers(id) ON DELETE SET NULL,
    FOREIGN KEY (handled_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================
-- SMS LOGS
-- ============================
CREATE TABLE sms_messages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    twilio_message_sid VARCHAR(64) NOT NULL UNIQUE,
    contact_id INT UNSIGNED DEFAULT NULL,
    tracking_number_id INT UNSIGNED DEFAULT NULL,
    direction ENUM('inbound','outbound') NOT NULL,
    from_number VARCHAR(20) NOT NULL,
    to_number VARCHAR(20) NOT NULL,
    body TEXT DEFAULT NULL,
    status VARCHAR(30) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
    FOREIGN KEY (tracking_number_id) REFERENCES tracking_numbers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================
-- ACTIVITY TIMELINE (notes, stage changes, etc. — unified feed per contact)
-- ============================
CREATE TABLE activity_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contact_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED DEFAULT NULL,          -- who performed the action (NULL = system)
    type VARCHAR(50) NOT NULL,                  -- note, call, sms, stage_change, created
    description TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;