-- Inventory Digital Assets: purpose-fit table + SP-write / View-read stack.
-- The legacy dim_assets (asset_tag/serial_number/asset_type_id, Available/Allocated status)
-- models physical assets and does not match the frontend DigitalAsset shape (licenses/domains
-- with vendor, cost, expiry, assignee). We introduce inv_digital_assets for the UI contract.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/digital_assets_sp_views.sql

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS inv_digital_assets (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id   INT NOT NULL,
    name        VARCHAR(255) NOT NULL,
    category    VARCHAR(150) NULL,
    vendor      VARCHAR(255) NULL,
    cost        DECIMAL(15,2) NULL,
    currency    VARCHAR(10) NULL DEFAULT 'USD',
    status      VARCHAR(50) NULL DEFAULT 'Active',
    expiry_date DATE NULL,
    assigned_to VARCHAR(255) NULL,
    created_at  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by  INT NULL,
    updated_at  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by  INT NULL,
    is_deleted  TINYINT(1) NULL DEFAULT 0,
    KEY idx_inv_digital_assets_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_digital_assets AS
SELECT
    d.id          AS id,
    d.id          AS assetId,
    d.tenant_id   AS tenant_id,
    d.name        AS name,
    d.category    AS category,
    d.vendor      AS vendor,
    d.cost        AS cost,
    d.currency    AS currency,
    d.status      AS status,
    d.expiry_date AS expiryDate,
    d.assigned_to AS assignedTo,
    d.created_at  AS createdAt,
    d.updated_at  AS updatedAt
FROM inv_digital_assets d
WHERE d.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_digital_asset;
DROP PROCEDURE IF EXISTS sp_update_digital_asset;
DROP PROCEDURE IF EXISTS sp_soft_delete_digital_asset;

DELIMITER $$

CREATE PROCEDURE sp_create_digital_asset(
    IN  p_tenant_id   INT,
    IN  p_name        VARCHAR(255),
    IN  p_category    VARCHAR(150),
    IN  p_vendor      VARCHAR(255),
    IN  p_cost        DECIMAL(15,2),
    IN  p_currency    VARCHAR(10),
    IN  p_status      VARCHAR(50),
    IN  p_expiry_date DATE,
    IN  p_assigned_to VARCHAR(255),
    IN  p_created_by  INT,
    OUT p_asset_id    INT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_name IS NULL OR p_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Asset name is required';
    END IF;

    INSERT INTO inv_digital_assets (
        tenant_id, name, category, vendor, cost, currency, status, expiry_date, assigned_to,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_name, p_category, p_vendor, p_cost, COALESCE(p_currency, 'USD'),
        COALESCE(p_status, 'Active'), p_expiry_date, p_assigned_to,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_asset_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_digital_asset(
    IN  p_tenant_id   INT,
    IN  p_asset_id    INT,
    IN  p_name        VARCHAR(255),
    IN  p_category    VARCHAR(150),
    IN  p_vendor      VARCHAR(255),
    IN  p_cost        DECIMAL(15,2),
    IN  p_currency    VARCHAR(10),
    IN  p_status      VARCHAR(50),
    IN  p_expiry_date DATE,
    IN  p_assigned_to VARCHAR(255),
    IN  p_updated_by  INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM inv_digital_assets
        WHERE id = p_asset_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Digital asset not found';
    END IF;

    UPDATE inv_digital_assets SET
        name        = COALESCE(p_name, name),
        category    = COALESCE(p_category, category),
        vendor      = COALESCE(p_vendor, vendor),
        cost        = COALESCE(p_cost, cost),
        currency    = COALESCE(p_currency, currency),
        status      = COALESCE(p_status, status),
        expiry_date = COALESCE(p_expiry_date, expiry_date),
        assigned_to = COALESCE(p_assigned_to, assigned_to),
        updated_at  = NOW(),
        updated_by  = p_updated_by
    WHERE id = p_asset_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

CREATE PROCEDURE sp_soft_delete_digital_asset(
    IN  p_tenant_id  INT,
    IN  p_asset_id   INT,
    IN  p_deleted_by INT
)
proc: BEGIN
    UPDATE inv_digital_assets SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_asset_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;