-- Klyn Database Explorer demo schema for SQL Server.
-- Creates a database named [Klyn] with demo tables, views and stored procedures.

IF DB_ID(N'Klyn') IS NOT NULL
BEGIN
    ALTER DATABASE [Klyn] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [Klyn];
END;
GO

CREATE DATABASE [Klyn];
GO

USE [Klyn];
GO

CREATE TABLE dbo.app_user (
    id INT IDENTITY(1,1) PRIMARY KEY,
    login NVARCHAR(64) NOT NULL UNIQUE,
    display_name NVARCHAR(120) NOT NULL,
    email NVARCHAR(160) NOT NULL UNIQUE,
    role_name NVARCHAR(40) NOT NULL,
    enabled BIT NOT NULL DEFAULT 1,
    last_connection DATETIME2 NULL,
    created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.customer (
    id INT IDENTITY(1,1) PRIMARY KEY,
    code NVARCHAR(20) NOT NULL UNIQUE,
    name NVARCHAR(160) NOT NULL,
    country NVARCHAR(80) NOT NULL,
    city NVARCHAR(120) NOT NULL,
    email NVARCHAR(160) NOT NULL,
    active BIT NOT NULL DEFAULT 1,
    created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.supplier (
    id INT IDENTITY(1,1) PRIMARY KEY,
    code NVARCHAR(20) NOT NULL UNIQUE,
    name NVARCHAR(160) NOT NULL,
    country NVARCHAR(80) NOT NULL,
    reliability_score DECIMAL(5,2) NOT NULL DEFAULT 90.00,
    active BIT NOT NULL DEFAULT 1
);

CREATE TABLE dbo.product (
    id INT IDENTITY(1,1) PRIMARY KEY,
    sku NVARCHAR(32) NOT NULL UNIQUE,
    name NVARCHAR(160) NOT NULL,
    category NVARCHAR(80) NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    stock_qty INT NOT NULL DEFAULT 0,
    supplier_id INT NOT NULL REFERENCES dbo.supplier(id),
    active BIT NOT NULL DEFAULT 1
);

CREATE TABLE dbo.sales_order (
    id INT IDENTITY(1,1) PRIMARY KEY,
    order_number NVARCHAR(30) NOT NULL UNIQUE,
    customer_id INT NOT NULL REFERENCES dbo.customer(id),
    owner_user_id INT NOT NULL REFERENCES dbo.app_user(id),
    status NVARCHAR(24) NOT NULL,
    ordered_at DATETIME2 NOT NULL,
    shipped_at DATETIME2 NULL
);

CREATE TABLE dbo.sales_order_line (
    id INT IDENTITY(1,1) PRIMARY KEY,
    order_id INT NOT NULL REFERENCES dbo.sales_order(id),
    product_id INT NOT NULL REFERENCES dbo.product(id),
    quantity INT NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    discount_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00
);

CREATE TABLE dbo.support_ticket (
    id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES dbo.customer(id),
    assigned_user_id INT NULL REFERENCES dbo.app_user(id),
    subject NVARCHAR(180) NOT NULL,
    priority NVARCHAR(16) NOT NULL,
    status NVARCHAR(24) NOT NULL,
    opened_at DATETIME2 NOT NULL,
    closed_at DATETIME2 NULL
);

CREATE TABLE dbo.audit_log (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    entity_name NVARCHAR(80) NOT NULL,
    entity_id INT NOT NULL,
    action_name NVARCHAR(40) NOT NULL,
    actor_login NVARCHAR(64) NOT NULL,
    message NVARCHAR(255) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

INSERT INTO dbo.app_user(login, display_name, email, role_name, enabled, last_connection) VALUES
('admin', 'Alice Martin', 'alice.martin@klyn.demo', 'DBA', 1, '2026-05-20T08:30:00'),
('jdurand', 'Julien Durand', 'julien.durand@klyn.demo', 'Analyst', 1, '2026-05-21T10:15:00'),
('nrobert', 'Nora Robert', 'nora.robert@klyn.demo', 'Support', 1, '2026-05-22T14:45:00'),
('mbernard', 'Marc Bernard', 'marc.bernard@klyn.demo', 'Operator', 0, NULL),
('snguyen', 'Sofia Nguyen', 'sofia.nguyen@klyn.demo', 'Manager', 1, '2026-05-23T09:05:00');

INSERT INTO dbo.customer(code, name, country, city, email, active) VALUES
('CUST-001', 'Northwind Research', 'France', 'Lyon', 'contact@northwind.demo', 1),
('CUST-002', 'Blue Harbor Labs', 'Belgium', 'Brussels', 'hello@blueharbor.demo', 1),
('CUST-003', 'Solar Forge', 'Germany', 'Hamburg', 'sales@solarforge.demo', 1),
('CUST-004', 'Green Valley Retail', 'Spain', 'Valencia', 'office@greenvalley.demo', 0),
('CUST-005', 'Atlas Mobility', 'Italy', 'Milan', 'support@atlas.demo', 1);

INSERT INTO dbo.supplier(code, name, country, reliability_score, active) VALUES
('SUP-001', 'Krypton Components', 'France', 96.50, 1),
('SUP-002', 'Nexus Devices', 'Germany', 91.25, 1),
('SUP-003', 'Aurora Packaging', 'Italy', 88.75, 1),
('SUP-004', 'Helios Logistics', 'Spain', 83.40, 0);

INSERT INTO dbo.product(sku, name, category, unit_price, stock_qty, supplier_id, active) VALUES
('SKU-1001', 'Klyn Starter Kit', 'Software', 149.00, 120, 1, 1),
('SKU-1002', 'Telemetry Gateway', 'Hardware', 429.00, 42, 2, 1),
('SKU-1003', 'Audit Pack', 'Software', 89.00, 200, 1, 1),
('SKU-1004', 'Secure Token', 'Security', 39.00, 500, 2, 1),
('SKU-1005', 'Deployment Box', 'Packaging', 12.50, 800, 3, 1),
('SKU-1006', 'Legacy Adapter', 'Hardware', 75.00, 8, 2, 0);

INSERT INTO dbo.sales_order(order_number, customer_id, owner_user_id, status, ordered_at, shipped_at) VALUES
('SO-2026-0001', 1, 2, 'SHIPPED', '2026-01-15T10:00:00', '2026-01-17T15:30:00'),
('SO-2026-0002', 2, 5, 'PAID', '2026-02-04T11:20:00', NULL),
('SO-2026-0003', 3, 2, 'DRAFT', '2026-02-18T09:10:00', NULL),
('SO-2026-0004', 5, 5, 'SHIPPED', '2026-03-12T16:40:00', '2026-03-14T12:00:00'),
('SO-2026-0005', 1, 2, 'CANCELLED', '2026-04-01T08:00:00', NULL);

INSERT INTO dbo.sales_order_line(order_id, product_id, quantity, unit_price, discount_rate) VALUES
(1, 1, 2, 149.00, 0.00), (1, 4, 10, 39.00, 5.00),
(2, 2, 1, 429.00, 0.00), (2, 3, 3, 89.00, 0.00),
(3, 1, 1, 149.00, 0.00), (3, 5, 20, 12.50, 0.00),
(4, 2, 2, 429.00, 3.00), (4, 4, 30, 39.00, 7.50),
(5, 6, 1, 75.00, 0.00);

INSERT INTO dbo.support_ticket(customer_id, assigned_user_id, subject, priority, status, opened_at, closed_at) VALUES
(1, 3, 'Gateway firmware update', 'HIGH', 'OPEN', '2026-05-01T09:00:00', NULL),
(2, 3, 'Invoice export format', 'MEDIUM', 'CLOSED', '2026-04-18T13:30:00', '2026-04-19T10:00:00'),
(3, 2, 'Slow dashboard query', 'HIGH', 'IN_PROGRESS', '2026-05-03T15:45:00', NULL),
(5, 3, 'Token activation failed', 'LOW', 'OPEN', '2026-05-07T11:15:00', NULL);

INSERT INTO dbo.audit_log(entity_name, entity_id, action_name, actor_login, message) VALUES
('customer', 1, 'CREATE', 'admin', 'Initial customer import'),
('sales_order', 1, 'SHIP', 'jdurand', 'Shipment confirmed'),
('support_ticket', 2, 'CLOSE', 'nrobert', 'Customer confirmed resolution'),
('product', 6, 'DISABLE', 'admin', 'Legacy adapter retired');
GO

CREATE VIEW dbo.v_active_customers AS
SELECT id, code, name, country, city, email FROM dbo.customer WHERE active = 1;
GO

CREATE VIEW dbo.v_low_stock_products AS
SELECT sku, name, category, stock_qty FROM dbo.product WHERE active = 1 AND stock_qty < 50;
GO

CREATE VIEW dbo.v_order_totals AS
SELECT o.id, o.order_number, c.name AS customer_name, o.status,
       SUM(l.quantity * l.unit_price * (1 - l.discount_rate / 100)) AS total_amount
FROM dbo.sales_order o
JOIN dbo.customer c ON c.id = o.customer_id
JOIN dbo.sales_order_line l ON l.order_id = o.id
GROUP BY o.id, o.order_number, c.name, o.status;
GO

CREATE VIEW dbo.v_monthly_revenue AS
SELECT FORMAT(o.ordered_at, 'yyyy-MM') AS month_name,
       SUM(l.quantity * l.unit_price * (1 - l.discount_rate / 100)) AS revenue
FROM dbo.sales_order o
JOIN dbo.sales_order_line l ON l.order_id = o.id
WHERE o.status <> 'CANCELLED'
GROUP BY FORMAT(o.ordered_at, 'yyyy-MM');
GO

CREATE VIEW dbo.v_open_tickets AS
SELECT t.id, c.name AS customer_name, u.display_name AS assigned_to, t.priority, t.subject, t.opened_at
FROM dbo.support_ticket t
JOIN dbo.customer c ON c.id = t.customer_id
LEFT JOIN dbo.app_user u ON u.id = t.assigned_user_id
WHERE t.status <> 'CLOSED';
GO

CREATE VIEW dbo.v_user_activity AS
SELECT u.login, u.display_name, u.role_name, COUNT(a.id) AS audit_events, u.last_connection
FROM dbo.app_user u
LEFT JOIN dbo.audit_log a ON a.actor_login = u.login
GROUP BY u.login, u.display_name, u.role_name, u.last_connection;
GO

CREATE PROCEDURE dbo.sp_get_customer_orders @customer_id INT AS
BEGIN
    SET NOCOUNT ON;
    SELECT ot.* FROM dbo.v_order_totals ot JOIN dbo.sales_order o ON o.id = ot.id WHERE o.customer_id = @customer_id;
END;
GO

CREATE PROCEDURE dbo.sp_set_user_enabled @login NVARCHAR(64), @enabled BIT AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.app_user SET enabled = @enabled WHERE login = @login;
END;
GO

CREATE PROCEDURE dbo.sp_register_audit @entity NVARCHAR(80), @entity_id INT, @action NVARCHAR(40), @actor NVARCHAR(64), @message NVARCHAR(255) AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.audit_log(entity_name, entity_id, action_name, actor_login, message)
    VALUES (@entity, @entity_id, @action, @actor, @message);
END;
GO

CREATE PROCEDURE dbo.sp_restock_product @sku NVARCHAR(32), @quantity INT AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.product SET stock_qty = stock_qty + @quantity WHERE sku = @sku;
END;
GO

CREATE PROCEDURE dbo.sp_close_ticket @ticket_id INT, @actor NVARCHAR(64) AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.support_ticket SET status = 'CLOSED', closed_at = SYSUTCDATETIME() WHERE id = @ticket_id;
    INSERT INTO dbo.audit_log(entity_name, entity_id, action_name, actor_login, message)
    VALUES ('support_ticket', @ticket_id, 'CLOSE', @actor, 'Ticket closed from stored procedure');
END;
GO

CREATE PROCEDURE dbo.sp_sales_summary @from DATETIME2, @to DATETIME2 AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.status, COUNT(*) AS order_count,
           SUM(l.quantity * l.unit_price * (1 - l.discount_rate / 100)) AS total_amount
    FROM dbo.sales_order o
    JOIN dbo.sales_order_line l ON l.order_id = o.id
    WHERE o.ordered_at BETWEEN @from AND @to
    GROUP BY o.status;
END;
GO
