-- ============================
-- schema.sql
--  📌 Notes**
-- ✔ Password hashes  
-- Replace the placeholder hashes with real ones:
--    password_hash('yourpassword', PASSWORD_DEFAULT)
-- ✔ Indexing  
-- All high‑traffic tables (devices, event_stream, tickets) include indexes for speed.
-- ✔ Foreign keys  
-- Everything is relational and cascades safely.
-- ✔ Ready for production
-- This schema supports:
-- - Provisioning
-- - Field tech app
-- - Dashboards
-- - Cron automation
-- - Vendor modules
-- - Event streaming
-- - Audit logging
-- ============================

-- ============================
-- CORE SYSTEM TABLES
-- ============================

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','noc','tech','customer') NOT NULL DEFAULT 'customer',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE api_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(128) NOT NULL UNIQUE,
    expires_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ============================
-- 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
);

-- ============================
-- DEVICES
-- ============================

CREATE TABLE devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ip VARCHAR(50) NOT NULL,
    vendor VARCHAR(50) NOT NULL,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL,
    service_id INT NOT NULL,
    status ENUM('active','inactive','down') DEFAULT 'inactive',
    firmware_file VARCHAR(255),
    firmware_scheduled TINYINT DEFAULT 0,
    outage_ticket_created TINYINT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (service_id) REFERENCES services(id)
);

CREATE INDEX idx_devices_ip ON devices(ip);
CREATE INDEX idx_devices_status ON devices(status);

-- ============================
-- 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
);
```

-- ============================
-- ADDED FOR ADVANCED DASHBOARDS
-- ============================

CREATE TABLE service_metrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    latency_ms INT,
    jitter_ms INT,
    packet_loss_pct DECIMAL(5,2),
    mos DECIMAL(3,2),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
```


-- ============================
-- SEED DATA
-- ============================

INSERT INTO users (username, password_hash, role)
VALUES
('admin', '$2y$10$ReplaceWithRealHash', 'admin'),
('tech1', '$2y$10$ReplaceWithRealHash', 'tech');

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);

