CREATE DATABASE IF NOT EXISTS fibertv;
USE fibertv;

CREATE TABLE IF NOT EXISTS service_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address TEXT NOT NULL,
    plan VARCHAR(50) NOT NULL,
    install_date DATE NOT NULL,
    comments TEXT,
    promo VARCHAR(50),
    request_date DATETIME NOT NULL,
    status ENUM('pending', 'contacted', 'installed', 'cancelled') DEFAULT 'pending'
);

CREATE TABLE IF NOT EXISTS contact_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    subject VARCHAR(50) NOT NULL,
    message TEXT NOT NULL,
    submission_date DATETIME NOT NULL,
    status ENUM('unread', 'read', 'replied', 'resolved') DEFAULT 'unread'
);

CREATE TABLE IF NOT EXISTS subscribers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    subscription_date DATETIME NOT NULL,
    active BOOLEAN DEFAULT TRUE
);