

-- ============================
-- CUSTOMERS + SERVICES
-- ============================

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(255),
    phone VARCHAR(50),
    email VARCHAR(100),
    status ENUM('active','pending_activation','suspended','canceled') DEFAULT 'pending_activation',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    service_profile VARCHAR(100) NOT NULL,
    speed_down INT NOT NULL,
    speed_up INT NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- ============================
-- TOWERS
-- ============================

CREATE TABLE towers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(255),
    device_count INT DEFAULT 0
);

-- ============================
-- FIELD TECH JOBS
-- ============================

CREATE TABLE jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tech_id INT NOT NULL,
    customer_id INT NOT NULL,
    description VARCHAR(255),
    scheduled_at DATETIME,
    status ENUM('pending','in_progress','completed','canceled') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tech_id) REFERENCES users(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- ============================
-- TICKETS
-- ============================

CREATE TABLE tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    subject VARCHAR(255) NOT NULL,
    status ENUM('open','in_progress','closed') DEFAULT 'open',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- ============================
-- EVENT STREAM (Provisioning Events)
-- ============================

CREATE TABLE event_stream (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(50),
    message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_event_stream_type ON event_stream(type);

-- ============================
-- AUDIT LOG
-- ============================

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    action TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);


-- ============================
-- ADDED FOR AUTO MAC SCAN & PHOTO UPDATING
-- ============================

CREATE TABLE install_verification (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ont_id INT NOT NULL,
    tech_id INT NOT NULL,
    photo_path VARCHAR(255),
    gps_lat DECIMAL(10,6),
    gps_lon DECIMAL(10,6),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO services (name, service_profile, speed_down, speed_up, price)
VALUES
('Basic 50/10', 'profile_basic', 50, 10, 49.99),
('Standard 100/20', 'profile_standard', 100, 20, 69.99),
('Premium 500/50', 'profile_premium', 500, 50, 99.99);

