-- ============================================
-- WhatsApp CRM Database Schema
-- phpMyAdmin > SQL tab e eta run korun
-- ============================================

CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    wa_id VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contact_id INT NOT NULL,
    wa_message_id VARCHAR(100) DEFAULT NULL,
    direction ENUM('inbound','outbound') NOT NULL,
    message_type VARCHAR(20) DEFAULT 'text',
    body TEXT,
    media_url VARCHAR(500) DEFAULT NULL,
    status VARCHAR(20) DEFAULT NULL,
    raw_payload LONGTEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contact_id) REFERENCES contacts(id)
);