-- Klyn Database Explorer demo schema for MariaDB.
-- Creates a database named `Klyn` with demo tables, views and stored procedures.

DROP DATABASE IF EXISTS Klyn;
CREATE DATABASE Klyn CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE Klyn;

CREATE TABLE app_user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    login VARCHAR(64) NOT NULL UNIQUE,
    display_name VARCHAR(120) NOT NULL,
    email VARCHAR(160) NOT NULL UNIQUE,
    role_name VARCHAR(40) NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    last_connection DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customer (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(160) NOT NULL,
    country VARCHAR(80) NOT NULL,
    city VARCHAR(120) NOT NULL,
    email VARCHAR(160) NOT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE supplier (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(160) NOT NULL,
    country VARCHAR(80) NOT NULL,
    reliability_score DECIMAL(5,2) NOT NULL DEFAULT 90.00,
    active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE product (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(32) NOT NULL UNIQUE,
    name VARCHAR(160) NOT NULL,
    category VARCHAR(80) NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    stock_qty INT NOT NULL DEFAULT 0,
    supplier_id INT NOT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT fk_product_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(id)
);

CREATE TABLE sales_order (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(30) NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    owner_user_id INT NOT NULL,
    status VARCHAR(24) NOT NULL,
    ordered_at DATETIME NOT NULL,
    shipped_at DATETIME NULL,
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customer(id),
    CONSTRAINT fk_order_user FOREIGN KEY (owner_user_id) REFERENCES app_user(id)
);

CREATE TABLE sales_order_line (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    discount_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_line_order FOREIGN KEY (order_id) REFERENCES sales_order(id),
    CONSTRAINT fk_line_product FOREIGN KEY (product_id) REFERENCES product(id)
);

CREATE TABLE support_ticket (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    assigned_user_id INT NULL,
    subject VARCHAR(180) NOT NULL,
    priority VARCHAR(16) NOT NULL,
    status VARCHAR(24) NOT NULL,
    opened_at DATETIME NOT NULL,
    closed_at DATETIME NULL,
    CONSTRAINT fk_ticket_customer FOREIGN KEY (customer_id) REFERENCES customer(id),
    CONSTRAINT fk_ticket_user FOREIGN KEY (assigned_user_id) REFERENCES app_user(id)
);

CREATE TABLE audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    entity_name VARCHAR(80) NOT NULL,
    entity_id INT NOT NULL,
    action_name VARCHAR(40) NOT NULL,
    actor_login VARCHAR(64) NOT NULL,
    message VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

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

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

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

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

INSERT INTO sales_order(order_number, customer_id, owner_user_id, status, ordered_at, shipped_at) VALUES
('SO-2026-0001', 1, 2, 'SHIPPED', '2026-01-15 10:00:00', '2026-01-17 15:30:00'),
('SO-2026-0002', 2, 5, 'PAID', '2026-02-04 11:20:00', NULL),
('SO-2026-0003', 3, 2, 'DRAFT', '2026-02-18 09:10:00', NULL),
('SO-2026-0004', 5, 5, 'SHIPPED', '2026-03-12 16:40:00', '2026-03-14 12:00:00'),
('SO-2026-0005', 1, 2, 'CANCELLED', '2026-04-01 08:00:00', NULL);

INSERT INTO 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 support_ticket(customer_id, assigned_user_id, subject, priority, status, opened_at, closed_at) VALUES
(1, 3, 'Gateway firmware update', 'HIGH', 'OPEN', '2026-05-01 09:00:00', NULL),
(2, 3, 'Invoice export format', 'MEDIUM', 'CLOSED', '2026-04-18 13:30:00', '2026-04-19 10:00:00'),
(3, 2, 'Slow dashboard query', 'HIGH', 'IN_PROGRESS', '2026-05-03 15:45:00', NULL),
(5, 3, 'Token activation failed', 'LOW', 'OPEN', '2026-05-07 11:15:00', NULL);

INSERT INTO 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');

CREATE VIEW v_active_customers AS
SELECT id, code, name, country, city, email FROM customer WHERE active = TRUE;

CREATE VIEW v_low_stock_products AS
SELECT sku, name, category, stock_qty FROM product WHERE active = TRUE AND stock_qty < 50;

CREATE VIEW 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 sales_order o
JOIN customer c ON c.id = o.customer_id
JOIN sales_order_line l ON l.order_id = o.id
GROUP BY o.id, o.order_number, c.name, o.status;

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

CREATE VIEW 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 support_ticket t
JOIN customer c ON c.id = t.customer_id
LEFT JOIN app_user u ON u.id = t.assigned_user_id
WHERE t.status <> 'CLOSED';

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

DELIMITER //

CREATE PROCEDURE sp_get_customer_orders(IN p_customer_id INT)
BEGIN
    SELECT * FROM v_order_totals WHERE id IN (SELECT id FROM sales_order WHERE customer_id = p_customer_id);
END//

CREATE PROCEDURE sp_set_user_enabled(IN p_login VARCHAR(64), IN p_enabled BOOLEAN)
BEGIN
    UPDATE app_user SET enabled = p_enabled WHERE login = p_login;
END//

CREATE PROCEDURE sp_register_audit(IN p_entity VARCHAR(80), IN p_entity_id INT, IN p_action VARCHAR(40), IN p_actor VARCHAR(64), IN p_message VARCHAR(255))
BEGIN
    INSERT INTO audit_log(entity_name, entity_id, action_name, actor_login, message)
    VALUES (p_entity, p_entity_id, p_action, p_actor, p_message);
END//

CREATE PROCEDURE sp_restock_product(IN p_sku VARCHAR(32), IN p_quantity INT)
BEGIN
    UPDATE product SET stock_qty = stock_qty + p_quantity WHERE sku = p_sku;
END//

CREATE PROCEDURE sp_close_ticket(IN p_ticket_id INT, IN p_actor VARCHAR(64))
BEGIN
    UPDATE support_ticket SET status = 'CLOSED', closed_at = CURRENT_TIMESTAMP WHERE id = p_ticket_id;
    INSERT INTO audit_log(entity_name, entity_id, action_name, actor_login, message)
    VALUES ('support_ticket', p_ticket_id, 'CLOSE', p_actor, 'Ticket closed from stored procedure');
END//

CREATE PROCEDURE sp_sales_summary(IN p_from DATETIME, IN p_to DATETIME)
BEGIN
    SELECT o.status, COUNT(*) AS order_count,
           SUM(l.quantity * l.unit_price * (1 - l.discount_rate / 100)) AS total_amount
    FROM sales_order o
    JOIN sales_order_line l ON l.order_id = o.id
    WHERE o.ordered_at BETWEEN p_from AND p_to
    GROUP BY o.status;
END//

DELIMITER ;
