-- Klyn Database Explorer demo schema for SQLite.
-- SQLite databases are files: create the Klyn demo with:
--   sqlite3 Klyn.sqlite < sqlite.sql
-- SQLite has no native stored procedures. This script creates procedure-like
-- saved statements in demo_routine plus triggers that exercise procedural logic.

PRAGMA foreign_keys = ON;

DROP VIEW IF EXISTS v_user_activity;
DROP VIEW IF EXISTS v_open_tickets;
DROP VIEW IF EXISTS v_monthly_revenue;
DROP VIEW IF EXISTS v_order_totals;
DROP VIEW IF EXISTS v_low_stock_products;
DROP VIEW IF EXISTS v_active_customers;
DROP TRIGGER IF EXISTS trg_sales_order_insert_audit;
DROP TRIGGER IF EXISTS trg_product_low_stock_audit;
DROP TABLE IF EXISTS demo_routine;
DROP TABLE IF EXISTS audit_log;
DROP TABLE IF EXISTS support_ticket;
DROP TABLE IF EXISTS sales_order_line;
DROP TABLE IF EXISTS sales_order;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS supplier;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS app_user;

CREATE TABLE app_user (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    login TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    role_name TEXT NOT NULL,
    enabled INTEGER NOT NULL DEFAULT 1,
    last_connection TEXT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customer (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    country TEXT NOT NULL,
    city TEXT NOT NULL,
    email TEXT NOT NULL,
    active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE supplier (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    country TEXT NOT NULL,
    reliability_score REAL NOT NULL DEFAULT 90.00,
    active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE product (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    unit_price REAL NOT NULL,
    stock_qty INTEGER NOT NULL DEFAULT 0,
    supplier_id INTEGER NOT NULL REFERENCES supplier(id),
    active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE sales_order (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_number TEXT NOT NULL UNIQUE,
    customer_id INTEGER NOT NULL REFERENCES customer(id),
    owner_user_id INTEGER NOT NULL REFERENCES app_user(id),
    status TEXT NOT NULL,
    ordered_at TEXT NOT NULL,
    shipped_at TEXT NULL
);

CREATE TABLE sales_order_line (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL REFERENCES sales_order(id),
    product_id INTEGER NOT NULL REFERENCES product(id),
    quantity INTEGER NOT NULL,
    unit_price REAL NOT NULL,
    discount_rate REAL NOT NULL DEFAULT 0.00
);

CREATE TABLE support_ticket (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL REFERENCES customer(id),
    assigned_user_id INTEGER NULL REFERENCES app_user(id),
    subject TEXT NOT NULL,
    priority TEXT NOT NULL,
    status TEXT NOT NULL,
    opened_at TEXT NOT NULL,
    closed_at TEXT NULL
);

CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    entity_name TEXT NOT NULL,
    entity_id INTEGER NOT NULL,
    action_name TEXT NOT NULL,
    actor_login TEXT NOT NULL,
    message TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE demo_routine (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    routine_name TEXT NOT NULL UNIQUE,
    description TEXT NOT NULL,
    sql_text TEXT NOT NULL
);

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

CREATE VIEW v_low_stock_products AS
SELECT sku, name, category, stock_qty FROM product WHERE active = 1 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.0)) 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 strftime('%Y-%m', o.ordered_at) AS month_name,
       SUM(l.quantity * l.unit_price * (1 - l.discount_rate / 100.0)) AS revenue
FROM sales_order o
JOIN sales_order_line l ON l.order_id = o.id
WHERE o.status <> 'CANCELLED'
GROUP BY strftime('%Y-%m', o.ordered_at);

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;

CREATE TRIGGER trg_sales_order_insert_audit
AFTER INSERT ON sales_order
BEGIN
    INSERT INTO audit_log(entity_name, entity_id, action_name, actor_login, message)
    VALUES ('sales_order', NEW.id, 'CREATE', 'system', 'Order inserted');
END;

CREATE TRIGGER trg_product_low_stock_audit
AFTER UPDATE OF stock_qty ON product
WHEN NEW.stock_qty < 10
BEGIN
    INSERT INTO audit_log(entity_name, entity_id, action_name, actor_login, message)
    VALUES ('product', NEW.id, 'LOW_STOCK', 'system', 'Stock fell below threshold');
END;

INSERT INTO demo_routine(routine_name, description, sql_text) VALUES
('sp_get_customer_orders', 'Procedure-like saved query for customer orders', 'SELECT * FROM v_order_totals WHERE id IN (SELECT id FROM sales_order WHERE customer_id = :customer_id);'),
('sp_set_user_enabled', 'Procedure-like update of user enabled flag', 'UPDATE app_user SET enabled = :enabled WHERE login = :login;'),
('sp_register_audit', 'Procedure-like insert into audit_log', 'INSERT INTO audit_log(entity_name, entity_id, action_name, actor_login, message) VALUES (:entity, :entity_id, :action, :actor, :message);'),
('sp_restock_product', 'Procedure-like stock update', 'UPDATE product SET stock_qty = stock_qty + :quantity WHERE sku = :sku;'),
('sp_close_ticket', 'Procedure-like ticket close operation', 'UPDATE support_ticket SET status = ''CLOSED'', closed_at = CURRENT_TIMESTAMP WHERE id = :ticket_id;'),
('sp_sales_summary', 'Procedure-like sales summary query', 'SELECT status, COUNT(*) AS order_count FROM sales_order WHERE ordered_at BETWEEN :from AND :to GROUP BY status;');
