-- =============================================
-- MIGRATION: Table QR Code & Customer Messaging
-- =============================================

-- Add QR token to restaurant_tables
ALTER TABLE restaurant_tables ADD COLUMN qr_token VARCHAR(64) NULL AFTER notes;
ALTER TABLE restaurant_tables ADD UNIQUE INDEX idx_qr_token (qr_token);

-- Generate QR tokens for existing tables
UPDATE restaurant_tables SET qr_token = CONCAT(
    LOWER(SUBSTRING(MD5(RAND()), 1, 8)),
    '-',
    LOWER(SUBSTRING(MD5(RAND()), 1, 8)),
    '-',
    LOWER(SUBSTRING(MD5(RAND()), 1, 8))
) WHERE qr_token IS NULL;

-- Table Messages (notifications from customer to cashier)
CREATE TABLE IF NOT EXISTS table_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_id INT NOT NULL,
    order_id INT NULL,
    sender_type ENUM('customer','cashier','system') NOT NULL DEFAULT 'customer',
    sender_name VARCHAR(100) NULL,
    message_type ENUM('order','add_item','question','call_staff','payment','complaint','info','lead') NOT NULL DEFAULT 'info',
    message TEXT NOT NULL,
    extra_data JSON NULL,
    is_read TINYINT(1) DEFAULT 0,
    read_by INT NULL,
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    FOREIGN KEY (read_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Table Customer Leads
CREATE TABLE IF NOT EXISTS table_leads (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_id INT NOT NULL,
    order_id INT NULL,
    customer_name VARCHAR(100),
    phone VARCHAR(20),
    whatsapp VARCHAR(20),
    instagram VARCHAR(100),
    tiktok VARCHAR(100),
    facebook VARCHAR(100),
    email VARCHAR(100),
    notes TEXT,
    is_converted TINYINT(1) DEFAULT 0,
    converted_customer_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    FOREIGN KEY (converted_customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;