-- Create database (SQL Server syntax)
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'NetworkAssets')
BEGIN
    CREATE DATABASE NetworkAssets;
END;
GO

-- Switch to the new database
USE NetworkAssets;
GO

-- Drop tables if they already exist
IF OBJECT_ID('dbo.Device_URL_Map', 'U') IS NOT NULL DROP TABLE dbo.Device_URL_Map;
IF OBJECT_ID('dbo.Devices', 'U') IS NOT NULL DROP TABLE dbo.Devices;
IF OBJECT_ID('dbo.URLs', 'U') IS NOT NULL DROP TABLE dbo.URLs;
GO

-- Devices table
CREATE TABLE dbo.Devices (
    device_id INT IDENTITY(1,1) PRIMARY KEY,
    hostname NVARCHAR(100) NOT NULL,
    ip_address NVARCHAR(45) NOT NULL,
    device_type NVARCHAR(50),
    location NVARCHAR(100),
    status NVARCHAR(20)
);
GO

-- URLs table
CREATE TABLE dbo.URLs (
    url_id INT IDENTITY(1,1) PRIMARY KEY,
    url NVARCHAR(255) NOT NULL,
    description NVARCHAR(255),
    category NVARCHAR(50),
    owner NVARCHAR(100)
);
GO

-- Mapping table
CREATE TABLE dbo.Device_URL_Map (
    map_id INT IDENTITY(1,1) PRIMARY KEY,
    device_id INT NOT NULL,
    url_id INT NOT NULL,
    relationship NVARCHAR(50),
    FOREIGN KEY (device_id) REFERENCES dbo.Devices(device_id),
    FOREIGN KEY (url_id) REFERENCES dbo.URLs(url_id)
);
GO

-- Seed data for Devices
INSERT INTO dbo.Devices (hostname, ip_address, device_type, location, status)
VALUES
('router01', '192.168.1.1', 'Router', 'HQ Rack 1', 'Active'),
('server01', '192.168.1.10', 'Server', 'HQ Rack 2', 'Active'),
('fw-core', '192.168.1.254', 'Firewall', 'HQ Rack 0', 'Active');
GO

-- Seed data for URLs
INSERT INTO dbo.URLs (url, description, category, owner)
VALUES
('https://portal.company.com', 'Internal company portal', 'Portal', 'IT Department'),
('https://api.company.com/v1', 'REST API endpoint', 'API', 'DevOps'),
('https://dashboard.company.com', 'Monitoring dashboard', 'Dashboard', 'Operations');
GO

-- Seed mapping data
INSERT INTO dbo.Device_URL_Map (device_id, url_id, relationship)
VALUES
(2, 1, 'Hosts'),
(2, 2, 'Hosts'),
(3, 3, 'Reverse Proxy');
GO