CREATE DATABASE network_manager;
USE network_manager;

-- Users table for application login
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password CHAR(60) NOT NULL,  -- Changed to CHAR(60) for bcrypt hash
    email VARCHAR(100) NOT NULL UNIQUE,
    role ENUM('admin', 'operator', 'viewer') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    full_name VARCHAR(100) NOT NULL
);

-- Network switches table
CREATE TABLE switches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    switch_name VARCHAR(100) NOT NULL,
    brand_model VARCHAR(100) NOT NULL,
    hostname VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    switch_username VARCHAR(50) NOT NULL,
    switch_password VARCHAR(255) NOT NULL,  -- Consider encryption for production
    snmp_read_string VARCHAR(50),
    snmp_write_string VARCHAR(50),
    property_name VARCHAR(100),
    location VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
); 