-- Digital Assets: compliance/legal tracking fields + fixes the pre-existing licenseKey/notes
-- gap (the frontend form already sends these two but CreateDigitalAssetPayload/sp_create_digital_asset
-- never had matching columns, so they were silently dropped on every save).
-- STRUCTURE ONLY — no data inserted here.
-- Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p 'stacie_Aggie_v1.0' < db/aggie_add_digital_asset_compliance_fields.sql

USE `stacie_Aggie_v1.0`;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'license_key');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE inv_digital_assets ADD COLUMN license_key VARCHAR(255) NULL AFTER vendor', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'notes');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE inv_digital_assets ADD COLUMN notes TEXT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'jurisdiction');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE inv_digital_assets ADD COLUMN jurisdiction VARCHAR(100) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'compliance_notes');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE inv_digital_assets ADD COLUMN compliance_notes TEXT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'next_law_change_check_due');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE inv_digital_assets ADD COLUMN next_law_change_check_due DATE NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'ai_compliance_check_enabled');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE inv_digital_assets ADD COLUMN ai_compliance_check_enabled TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'ai_compliance_last_summary');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE inv_digital_assets ADD COLUMN ai_compliance_last_summary TEXT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inv_digital_assets' AND COLUMN_NAME = 'ai_compliance_last_checked_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE inv_digital_assets ADD COLUMN ai_compliance_last_checked_at TIMESTAMP NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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.license_key                   AS licenseKey,
    d.cost                          AS cost,
    d.currency                      AS currency,
    d.status                        AS status,
    d.expiry_date                   AS expiryDate,
    d.assigned_to                   AS assignedTo,
    d.notes                         AS notes,
    d.jurisdiction                  AS jurisdiction,
    d.compliance_notes              AS complianceNotes,
    d.next_law_change_check_due     AS nextLawChangeCheckDue,
    d.ai_compliance_check_enabled   AS aiComplianceCheckEnabled,
    d.ai_compliance_last_summary    AS aiComplianceLastSummary,
    d.ai_compliance_last_checked_at AS aiComplianceLastCheckedAt,
    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;

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_license_key                 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_notes                       TEXT,
    IN  p_jurisdiction                VARCHAR(100),
    IN  p_compliance_notes            TEXT,
    IN  p_next_law_change_check_due   DATE,
    IN  p_ai_compliance_check_enabled TINYINT(1),
    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, license_key, cost, currency, status, expiry_date,
        assigned_to, notes, jurisdiction, compliance_notes, next_law_change_check_due,
        ai_compliance_check_enabled, created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_name, p_category, p_vendor, p_license_key, p_cost, COALESCE(p_currency, 'USD'),
        COALESCE(p_status, 'Active'), p_expiry_date, p_assigned_to, p_notes, p_jurisdiction,
        p_compliance_notes, p_next_law_change_check_due, COALESCE(p_ai_compliance_check_enabled, 0),
        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_license_key                 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_notes                       TEXT,
    IN  p_jurisdiction                VARCHAR(100),
    IN  p_compliance_notes            TEXT,
    IN  p_next_law_change_check_due   DATE,
    IN  p_ai_compliance_check_enabled TINYINT(1),
    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),
        license_key                  = COALESCE(p_license_key, license_key),
        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),
        notes                        = COALESCE(p_notes, notes),
        jurisdiction                 = COALESCE(p_jurisdiction, jurisdiction),
        compliance_notes             = COALESCE(p_compliance_notes, compliance_notes),
        next_law_change_check_due    = COALESCE(p_next_law_change_check_due, next_law_change_check_due),
        ai_compliance_check_enabled  = COALESCE(p_ai_compliance_check_enabled, ai_compliance_check_enabled),
        updated_at                   = NOW(),
        updated_by                   = p_updated_by
    WHERE id = p_asset_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;