-- Contacts table for storing contact form submissions
CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    subject VARCHAR(255),
    message TEXT NOT NULL,
    status ENUM('new', 'read', 'replied', 'archived') DEFAULT 'new',
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    replied_at TIMESTAMP NULL,
    replied_by INT NULL,
    FOREIGN KEY (replied_by) REFERENCES users(id),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_created_at (created_at)
);

-- Contact replies table
CREATE TABLE IF NOT EXISTS contact_replies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contact_id INT NOT NULL,
    reply_message TEXT NOT NULL,
    replied_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    FOREIGN KEY (replied_by) REFERENCES users(id)
);

-- Add some sample data
INSERT INTO contacts (name, email, subject, message, status, priority) VALUES 
('John Doe', 'john@example.com', 'Partnership Inquiry', 'Hi, I would like to discuss a potential partnership opportunity.', 'new', 'normal'),
('Jane Smith', 'jane@example.com', 'Technical Support', 'I am having issues with the video playback feature.', 'read', 'high'),
('Mike Johnson', 'mike@example.com', 'Collaboration Request', 'I am interested in collaborating on gaming content.', 'new', 'normal'),
('Sarah Wilson', 'sarah@example.com', 'Bug Report', 'Found a bug in the contact form validation.', 'replied', 'high');