Table-Queries.sql

/*
--1. VERÝTABANI OLUÞTURMA 
CREATE DATABASE ITAssetManagementDB;
go 
use ITAssetManagementDB;
go 
*/

--2. USERS Tablosu (Soft Delete & GUID)
create table Users(

	id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    -- 'ACTIVE' veya 'DEACTIVATED' (Soft Delete için)
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'DEACTIVATED')),
    role VARCHAR(20) NOT NULL CHECK (role IN ('ADMIN', 'TECHNICIAN', 'USER')),
    created_at DATETIME2 DEFAULT GETDATE()
);


-- 3. ASSETS Tablosu (GUID)
CREATE TABLE Assets(
id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    asset_name VARCHAR(100) NOT NULL,
    asset_type VARCHAR(50) NOT NULL CHECK (asset_type IN ('SERVER', 'LAPTOP', 'SWITCH', 'ROUTER')),
    ip_address VARCHAR(45) NULL,
    mac_address VARCHAR(17) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL CHECK (status IN ('ACTIVE', 'MAINTENANCE', 'RETIRED', 'SPARE')),
    location VARCHAR(100) NULL,
    -- Ýliþki: USERS (1) ----o{ ASSETS (N)
    assigned_user_id UNIQUEIDENTIFIER NULL, 
    updated_at DATETIME2 DEFAULT GETDATE(),
    CONSTRAINT FK_Assets_Users FOREIGN KEY (assigned_user_id) REFERENCES users(id)
);

-- 4. LICENSES Tablosu (GUID)
CREATE TABLE licenses (
    id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    software_name VARCHAR(100) NOT NULL,
    license_key VARCHAR(255) NOT NULL,
    expiration_date DATE NULL,
    total_slots INT NOT NULL CHECK (total_slots >= 0)
);


-- 5. ASSET_LICENSES Ara Tablosu (N:M Ýliþkisi)
-- Notasyon: ASSETS }o----||----o{ LICENSES
CREATE TABLE asset_licenses (
    asset_id UNIQUEIDENTIFIER NOT NULL,
    license_id UNIQUEIDENTIFIER NOT NULL,
    assigned_at DATETIME2 DEFAULT GETDATE(),
    PRIMARY KEY (asset_id, license_id),
    CONSTRAINT FK_AL_Assets FOREIGN KEY (asset_id) REFERENCES assets(id),
    CONSTRAINT FK_AL_Licenses FOREIGN KEY (license_id) REFERENCES licenses(id)
);


-- 6. AUDIT_LOGS Tablosu (Deðiþmez Hafýza & GUID)
-- Notasyon: USERS ||----}o AUDIT_LOGS ve ASSETS ||----}o AUDIT_LOGS
CREATE TABLE audit_logs (
    id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    asset_id UNIQUEIDENTIFIER NOT NULL,
    action_by_id UNIQUEIDENTIFIER NULL, -- Ýþlemi yapan kullanýcý
    action_type VARCHAR(50) NOT NULL,
    old_value NVARCHAR(MAX) NULL, -- Büyük veri (JSON) için NVARCHAR(MAX)
    new_value NVARCHAR(MAX) NULL,
    logged_at DATETIME2 DEFAULT GETDATE(),
    CONSTRAINT FK_Audit_Assets FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
    CONSTRAINT FK_Audit_Users FOREIGN KEY (action_by_id) REFERENCES users(id)
);