-- Orphan-stub domains (no prior schema/UI): Materials, Liabilities, Sprints, Resources.
-- New purpose-designed tables + SP-write / View-read stacks. Schemas are our design
-- (no existing contract) and are expected to be reviewed/adjusted by the product owner.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/orphan_domains_sp_views.sql

USE db_abos_v0.1;

-- =========================================================================
-- 1) Inventory Materials  (/api/inventory/materials)
-- =========================================================================
CREATE TABLE IF NOT EXISTS inv_materials (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT NOT NULL,
    name          VARCHAR(255) NOT NULL,
    code          VARCHAR(100) NULL,
    category      VARCHAR(150) NULL,
    unit          VARCHAR(50) NULL,
    stock_qty     DECIMAL(15,2) NULL DEFAULT 0,
    unit_cost     DECIMAL(15,2) NULL,
    currency      VARCHAR(10) NULL DEFAULT 'USD',
    reorder_level DECIMAL(15,2) NULL DEFAULT 0,
    status        ENUM('Active','Inactive','Discontinued') NULL DEFAULT 'Active',
    notes         TEXT 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_materials_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_materials AS
SELECT id AS id, id AS materialId, tenant_id, name, code, category, unit,
       stock_qty AS stockQty, unit_cost AS unitCost, currency, reorder_level AS reorderLevel,
       status, notes, created_at AS createdAt, updated_at AS updatedAt
FROM inv_materials WHERE is_deleted = 0;

-- =========================================================================
-- 2) Finance Liabilities  (/api/inventory/liabilities  — stub path retained)
-- =========================================================================
CREATE TABLE IF NOT EXISTS fin_liabilities (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id    INT NOT NULL,
    name         VARCHAR(255) NOT NULL,
    type         VARCHAR(50) NULL,
    counterparty VARCHAR(255) NULL,
    principal    DECIMAL(15,2) NOT NULL DEFAULT 0,
    outstanding  DECIMAL(15,2) NULL,
    currency     VARCHAR(10) NULL DEFAULT 'USD',
    interest_rate DECIMAL(6,3) NULL,
    start_date   DATE NULL,
    due_date     DATE NULL,
    status       ENUM('Active','Settled','Defaulted') NULL DEFAULT 'Active',
    notes        TEXT 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_fin_liabilities_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_liabilities AS
SELECT id AS id, id AS liabilityId, tenant_id, name, type, counterparty,
       principal, COALESCE(outstanding, principal) AS outstanding, currency,
       interest_rate AS interestRate, start_date AS startDate, due_date AS dueDate,
       status, notes, created_at AS createdAt, updated_at AS updatedAt
FROM fin_liabilities WHERE is_deleted = 0;

-- =========================================================================
-- 3) Project Sprints  (/api/projects/sprints)
-- =========================================================================
CREATE TABLE IF NOT EXISTS proj_sprints (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id  INT NOT NULL,
    name       VARCHAR(255) NOT NULL,
    project_id INT NULL,
    goal       TEXT NULL,
    start_date DATE NULL,
    end_date   DATE NULL,
    status     ENUM('Planning','Active','Completed','Cancelled') NULL DEFAULT 'Planning',
    capacity   INT 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_proj_sprints_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_sprints AS
SELECT s.id AS id, s.id AS sprintId, s.tenant_id, s.name, s.project_id AS projectId,
       p.name AS projectName, s.goal, s.start_date AS startDate, s.end_date AS endDate,
       s.status, s.capacity, s.created_at AS createdAt, s.updated_at AS updatedAt
FROM proj_sprints s
LEFT JOIN dim_projects p ON p.id = s.project_id
WHERE s.is_deleted = 0;

-- =========================================================================
-- 4) Project Resources  (/api/projects/resources)
-- =========================================================================
CREATE TABLE IF NOT EXISTS proj_resources (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT NOT NULL,
    name          VARCHAR(255) NOT NULL,
    type          VARCHAR(50) NULL,
    project_id    INT NULL,
    role          VARCHAR(150) NULL,
    allocation_pct INT NULL,
    cost_rate     DECIMAL(15,2) NULL,
    currency      VARCHAR(10) NULL DEFAULT 'USD',
    availability  ENUM('Available','Allocated','Unavailable') NULL DEFAULT 'Available',
    notes         TEXT 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_proj_resources_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_resources AS
SELECT r.id AS id, r.id AS resourceId, r.tenant_id, r.name, r.type, r.project_id AS projectId,
       p.name AS projectName, r.role, r.allocation_pct AS allocationPct,
       r.cost_rate AS costRate, r.currency, r.availability,
       r.notes, r.created_at AS createdAt, r.updated_at AS updatedAt
FROM proj_resources r
LEFT JOIN dim_projects p ON p.id = r.project_id
WHERE r.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_material;
DROP PROCEDURE IF EXISTS sp_update_material;
DROP PROCEDURE IF EXISTS sp_soft_delete_material;
DROP PROCEDURE IF EXISTS sp_create_liability;
DROP PROCEDURE IF EXISTS sp_update_liability;
DROP PROCEDURE IF EXISTS sp_soft_delete_liability;
DROP PROCEDURE IF EXISTS sp_create_sprint;
DROP PROCEDURE IF EXISTS sp_update_sprint;
DROP PROCEDURE IF EXISTS sp_soft_delete_sprint;
DROP PROCEDURE IF EXISTS sp_create_resource;
DROP PROCEDURE IF EXISTS sp_update_resource;
DROP PROCEDURE IF EXISTS sp_soft_delete_resource;

DELIMITER $$

-- ---- Materials ----
CREATE PROCEDURE sp_create_material(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_code VARCHAR(100), IN p_category VARCHAR(150),
    IN p_unit VARCHAR(50), IN p_stock_qty DECIMAL(15,2), IN p_unit_cost DECIMAL(15,2), IN p_currency VARCHAR(10),
    IN p_reorder_level DECIMAL(15,2), IN p_status VARCHAR(50), IN p_notes TEXT, IN p_created_by INT, OUT p_material_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Material name is required'; END IF;
    INSERT INTO inv_materials (tenant_id,name,code,category,unit,stock_qty,unit_cost,currency,reorder_level,status,notes,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_code,p_category,p_unit,COALESCE(p_stock_qty,0),p_unit_cost,COALESCE(p_currency,'USD'),COALESCE(p_reorder_level,0),COALESCE(p_status,'Active'),p_notes,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_material_id = LAST_INSERT_ID();
END$$
CREATE PROCEDURE sp_update_material(
    IN p_tenant_id INT, IN p_material_id INT, IN p_name VARCHAR(255), IN p_code VARCHAR(100), IN p_category VARCHAR(150),
    IN p_unit VARCHAR(50), IN p_stock_qty DECIMAL(15,2), IN p_unit_cost DECIMAL(15,2), IN p_currency VARCHAR(10),
    IN p_reorder_level DECIMAL(15,2), IN p_status VARCHAR(50), IN p_notes TEXT, IN p_updated_by INT)
proc: BEGIN
    UPDATE inv_materials SET name=COALESCE(p_name,name),code=COALESCE(p_code,code),category=COALESCE(p_category,category),
        unit=COALESCE(p_unit,unit),stock_qty=COALESCE(p_stock_qty,stock_qty),unit_cost=COALESCE(p_unit_cost,unit_cost),
        currency=COALESCE(p_currency,currency),reorder_level=COALESCE(p_reorder_level,reorder_level),status=COALESCE(p_status,status),
        notes=COALESCE(p_notes,notes),updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_material_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$
CREATE PROCEDURE sp_soft_delete_material(IN p_tenant_id INT, IN p_material_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE inv_materials SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_material_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$

-- ---- Liabilities ----
CREATE PROCEDURE sp_create_liability(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_counterparty VARCHAR(255),
    IN p_principal DECIMAL(15,2), IN p_outstanding DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_interest_rate DECIMAL(6,3),
    IN p_start_date DATE, IN p_due_date DATE, IN p_status VARCHAR(50), IN p_notes TEXT, IN p_created_by INT, OUT p_liability_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Liability name is required'; END IF;
    INSERT INTO fin_liabilities (tenant_id,name,type,counterparty,principal,outstanding,currency,interest_rate,start_date,due_date,status,notes,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_type,p_counterparty,COALESCE(p_principal,0),COALESCE(p_outstanding,p_principal),COALESCE(p_currency,'USD'),p_interest_rate,p_start_date,p_due_date,COALESCE(p_status,'Active'),p_notes,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_liability_id = LAST_INSERT_ID();
END$$
CREATE PROCEDURE sp_update_liability(
    IN p_tenant_id INT, IN p_liability_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_counterparty VARCHAR(255),
    IN p_principal DECIMAL(15,2), IN p_outstanding DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_interest_rate DECIMAL(6,3),
    IN p_start_date DATE, IN p_due_date DATE, IN p_status VARCHAR(50), IN p_notes TEXT, IN p_updated_by INT)
proc: BEGIN
    UPDATE fin_liabilities SET name=COALESCE(p_name,name),type=COALESCE(p_type,type),counterparty=COALESCE(p_counterparty,counterparty),
        principal=COALESCE(p_principal,principal),outstanding=COALESCE(p_outstanding,outstanding),currency=COALESCE(p_currency,currency),
        interest_rate=COALESCE(p_interest_rate,interest_rate),start_date=COALESCE(p_start_date,start_date),due_date=COALESCE(p_due_date,due_date),
        status=COALESCE(p_status,status),notes=COALESCE(p_notes,notes),updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_liability_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$
CREATE PROCEDURE sp_soft_delete_liability(IN p_tenant_id INT, IN p_liability_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE fin_liabilities SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_liability_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$

-- ---- Sprints ----
CREATE PROCEDURE sp_create_sprint(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_project_id INT, IN p_goal TEXT,
    IN p_start_date DATE, IN p_end_date DATE, IN p_status VARCHAR(50), IN p_capacity INT, IN p_created_by INT, OUT p_sprint_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sprint name is required'; END IF;
    INSERT INTO proj_sprints (tenant_id,name,project_id,goal,start_date,end_date,status,capacity,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_project_id,p_goal,p_start_date,p_end_date,COALESCE(p_status,'Planning'),p_capacity,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_sprint_id = LAST_INSERT_ID();
END$$
CREATE PROCEDURE sp_update_sprint(
    IN p_tenant_id INT, IN p_sprint_id INT, IN p_name VARCHAR(255), IN p_project_id INT, IN p_goal TEXT,
    IN p_start_date DATE, IN p_end_date DATE, IN p_status VARCHAR(50), IN p_capacity INT, IN p_updated_by INT)
proc: BEGIN
    UPDATE proj_sprints SET name=COALESCE(p_name,name),project_id=COALESCE(p_project_id,project_id),goal=COALESCE(p_goal,goal),
        start_date=COALESCE(p_start_date,start_date),end_date=COALESCE(p_end_date,end_date),status=COALESCE(p_status,status),
        capacity=COALESCE(p_capacity,capacity),updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_sprint_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$
CREATE PROCEDURE sp_soft_delete_sprint(IN p_tenant_id INT, IN p_sprint_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE proj_sprints SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_sprint_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$

-- ---- Resources ----
CREATE PROCEDURE sp_create_resource(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_project_id INT, IN p_role VARCHAR(150),
    IN p_allocation_pct INT, IN p_cost_rate DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_availability VARCHAR(50),
    IN p_notes TEXT, IN p_created_by INT, OUT p_resource_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Resource name is required'; END IF;
    INSERT INTO proj_resources (tenant_id,name,type,project_id,role,allocation_pct,cost_rate,currency,availability,notes,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_type,p_project_id,p_role,p_allocation_pct,p_cost_rate,COALESCE(p_currency,'USD'),COALESCE(p_availability,'Available'),p_notes,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_resource_id = LAST_INSERT_ID();
END$$
CREATE PROCEDURE sp_update_resource(
    IN p_tenant_id INT, IN p_resource_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_project_id INT, IN p_role VARCHAR(150),
    IN p_allocation_pct INT, IN p_cost_rate DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_availability VARCHAR(50),
    IN p_notes TEXT, IN p_updated_by INT)
proc: BEGIN
    UPDATE proj_resources SET name=COALESCE(p_name,name),type=COALESCE(p_type,type),project_id=COALESCE(p_project_id,project_id),
        role=COALESCE(p_role,role),allocation_pct=COALESCE(p_allocation_pct,allocation_pct),cost_rate=COALESCE(p_cost_rate,cost_rate),
        currency=COALESCE(p_currency,currency),availability=COALESCE(p_availability,availability),notes=COALESCE(p_notes,notes),
        updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_resource_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$
CREATE PROCEDURE sp_soft_delete_resource(IN p_tenant_id INT, IN p_resource_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE proj_resources SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_resource_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$

DELIMITER ;