-- =========================
-- Global Database
-- =========================
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'usmcGlobal')
BEGIN
    CREATE DATABASE usmcGlobal;
END;
GO

USE usmcGlobal;
GO

IF OBJECT_ID('CaseCatalogue', 'U') IS NULL
BEGIN
    CREATE TABLE CaseCatalogue (
        id INT IDENTITY(1,1) PRIMARY KEY,
        CaseName NVARCHAR(100) NOT NULL,
        Description NVARCHAR(255),
        CreatedDate DATE,
        Status NVARCHAR(50),
        AdminContact NVARCHAR(100)
    );
END;
GO

INSERT INTO CaseCatalogue (CaseName, Description, CreatedDate, Status, AdminContact)
VALUES
('sanDiegoCase', 'West Coast operations node, San Diego region', '2025-01-15', 'Active', 'case_admin@usmc.local'),
('oceansideCase', 'Southern California coastal operations node', '2025-01-16', 'Active', 'ops_admin@usmc.local'),
('twentyninepalmsCase', 'Desert training and operations node', '2025-01-17', 'Active', 'audit_admin@usmc.local'),
('albanyCase', 'East Coast logistics node, Albany GA', '2025-01-18', 'Active', 'case_admin@usmc.local'),
('cherryPointCase', 'Aviation operations node, Cherry Point NC', '2025-01-19', 'Active', 'ops_admin@usmc.local'),
('okinawaCase', 'Pacific operations node, Okinawa JP', '2025-01-20', 'Active', 'audit_admin@usmc.local'),
('harlingenCase', 'South Texas operations node, Harlingen TX', '2025-01-21', 'Active', 'case_admin@usmc.local');
GO

-- =========================
-- Helper: Create tables A–Z
-- =========================
-- TableA has richer schema, others are simple shells
-- Wrap in a procedure-like block for reuse

-- San Diego Case
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'sanDiegoCase')
BEGIN
    CREATE DATABASE sanDiegoCase;
END;
GO

USE sanDiegoCase;
GO

CREATE TABLE tableA (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Setting NVARCHAR(100),
    Value NVARCHAR(100),
    Notes NVARCHAR(255)
);

-- Tables B–Z
CREATE TABLE tableB (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableC (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableD (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableE (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableF (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableG (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableH (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableI (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableJ (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableK (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableL (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableM (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableN (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableO (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableP (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableQ (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableR (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableS (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableT (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableU (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableV (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableW (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableX (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableY (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
CREATE TABLE tableZ (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
GO

-- Insert dummy configuration data into TableA
INSERT INTO tableA (Setting, Value, Notes) VALUES
('ServerName', 'CaseServer01', 'Identifier for the case database server'),
('IPAddress', '192.168.10.25', 'Static IP assigned to the server'),
('OperatingSystem', 'Windows Server 2022', 'OS baseline for forensic intake'),
('CPU', 'Intel Xeon Silver 4310 @ 2.1GHz', '12 cores, optimized for multi-thread tasks'),
('Memory', '64GB DDR4 ECC', 'Error-correcting RAM for reliability'),
('Storage', '2TB NVMe SSD', 'Fast storage for case data'),
('NetworkAdapter', 'Intel I350 Gigabit Ethernet', 'Primary NIC'),
('FirewallEnabled', 'True', 'Ensures baseline security posture'),
('BackupSchedule', 'Daily at 02:00', 'Automated backup window'),
('LogRetentionDays', '90', 'Retains logs for 3 months'),
('AdminUser', 'case_admin', 'Default administrative account'),
('MaxConnections', '250', 'Connection cap for concurrent sessions'),
('EncryptionProtocol', 'TLS 1.3', 'Secure communication enforced'),
('MonitoringAgent', 'Enabled', 'Health and performance telemetry active');
GO

-- =========================
-- Repeat for other Case DBs
-- =========================
-- OceansideCase, TwentyninepalmsCase, AlbanyCase, CherryPointCase, OkinawaCase, HarlingenCase
-- Each gets TableA with dummy config data (values varied per DB), plus empty tables B–Z
-- Example for OceansideCase:

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'oceansideCase')
BEGIN
    CREATE DATABASE oceansideCase;
END;
GO

USE oceansideCase;
GO

CREATE TABLE tableA (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Setting NVARCHAR(100),
    Value NVARCHAR(100),
    Notes NVARCHAR(255)
);

-- Tables B–Z same as above
CREATE TABLE tableB (id INT IDENTITY(1,1) PRIMARY KEY, data NVARCHAR(255));
-- ... repeat through tableZ ...

INSERT INTO tableA (Setting, Value, Notes) VALUES
('ServerName', 'CaseServer02', 'Identifier for the case database server'),
('IPAddress', '192.168.20.45', 'Static IP assigned to the server'),
('OperatingSystem', 'Ubuntu 22.04 LTS', 'Linux baseline for forensic intake'),
('CPU', 'AMD EPYC 7543 @ 2.8GHz', 'High core count processor'),
('Memory', '128GB DDR5 ECC', 'Large memory footprint'),
('Storage', '4TB SATA HDD', 'Bulk storage for case data'),
('NetworkAdapter', 'Broadcom NetXtreme 10GbE', 'High-speed NIC'),
('FirewallEnabled', 'True', 'Baseline security posture'),
('BackupSchedule', 'Weekly Sunday 03:00', 'Automated backup window'),
('LogRetentionDays', '180', 'Extended retention policy'),
('AdminUser', 'ops_admin', 'Default administrative account'),
('MaxConnections', '500', 'Connection cap for concurrent sessions'),
('EncryptionProtocol', 'TLS 1.2', 'Secure communication enforced'),
('MonitoringAgent', 'Disabled', 'Telemetry disabled for testing');
GO

-- Repeat similar blocks for:
-- twentyninepalmsCase
-- albanyCase
-- cherryPointCase
-- okinawaCase
-- harlingenCase