-- =======================================================================================
-- AI & ANALYTICS REPORTING LAYER
-- =======================================================================================
-- Database: property_system
-- Description: Advanced Views, KPIs, and Predictive Models for Property Management
-- =======================================================================================

-- =======================================================================================
-- 1. FINANCIAL REPORTING VIEWS
-- =======================================================================================

-- View: Advanced Rent Roll (Real-time arrear tracking)
CREATE OR REPLACE VIEW `view_rent_roll_advanced` AS
SELECT 
    l.id AS lease_id,
    p.name AS property_name,
    u.unit_number,
    u.rent_amount AS current_market_rent,
    l.rent_amount AS lease_rent,
    CONCAT(t.first_name, ' ', t.last_name) AS tenant_name,
    l.start_date,
    l.end_date,
    DATEDIFF(l.end_date, CURRENT_DATE) AS days_to_expiry,
    l.status AS lease_status,
    
    -- Financials
    (SELECT IFNULL(SUM(balance), 0) FROM invoices 
     WHERE lease_id = l.id AND status IN ('unpaid', 'partial', 'overdue')) AS total_arrears,
    
    (SELECT MAX(due_date) FROM invoices 
     WHERE lease_id = l.id AND status != 'paid' AND due_date < CURRENT_DATE) AS oldest_unpaid_date,

    -- Risk Flag
    CASE 
        WHEN (SELECT IFNULL(SUM(balance), 0) FROM invoices WHERE lease_id = l.id) > (l.rent_amount * 2) THEN 'HIGH RISK'
        WHEN (SELECT IFNULL(SUM(balance), 0) FROM invoices WHERE lease_id = l.id) > 0 THEN 'Arrears'
        ELSE 'Good Standing'
    END AS financial_status

FROM leases l
JOIN units u ON l.unit_id = u.id
JOIN properties p ON u.property_id = p.id
JOIN users t ON l.tenant_id = t.id;

-- View: Consolidated Profit & Loss (P&L) Setup
CREATE OR REPLACE VIEW `view_pnl_monthly` AS
SELECT 
    DATE_FORMAT(transaction_date, '%Y-%m') AS month_year,
    p.id AS property_id,
    p.name AS property_name,
    
    -- Income
    SUM(CASE WHEN type = 'Income' THEN amount ELSE 0 END) AS total_income,
    
    -- Expenses
    SUM(CASE WHEN type = 'Expense' THEN amount ELSE 0 END) AS total_expense,
    
    -- Net
    (SUM(CASE WHEN type = 'Income' THEN amount ELSE 0 END) - 
     SUM(CASE WHEN type = 'Expense' THEN amount ELSE 0 END)) AS net_cash_flow

FROM (
    -- Combine Payments (Income) and Expenses (Expense)
    SELECT 'Income' AS type, amount, payment_date AS transaction_date, 
           (SELECT property_id FROM units u JOIN leases l ON l.unit_id = u.id JOIN invoices inv ON inv.lease_id = l.id WHERE inv.id = payments.invoice_id LIMIT 1) as derived_prop_id 
    FROM payments 
    WHERE status = 'verified'
    
    UNION ALL
    
    SELECT 'Expense' AS type, amount, incurred_date AS transaction_date, property_id as derived_prop_id
    FROM expenses 
    WHERE status = 'paid'
) AS consolidated
JOIN properties p ON consolidated.derived_prop_id = p.id
GROUP BY month_year, property_id;

-- =======================================================================================
-- 2. OPERATIONAL DASHBOARDS
-- =======================================================================================

-- View: Occupancy & Vacancy Rates
CREATE OR REPLACE VIEW `view_occupancy_metrics` AS
SELECT 
    p.id AS property_id,
    p.name AS property_name,
    COUNT(u.id) AS total_units,
    SUM(CASE WHEN u.status = 'occupied' THEN 1 ELSE 0 END) AS occupied_units,
    SUM(CASE WHEN u.status = 'vacant' THEN 1 ELSE 0 END) AS vacant_units,
    
    -- Rates
    ROUND((SUM(CASE WHEN u.status = 'occupied' THEN 1 ELSE 0 END) / COUNT(u.id)) * 100, 1) AS occupancy_rate,
    
    -- Lost Income Potential
    SUM(CASE WHEN u.status = 'vacant' THEN u.rent_amount ELSE 0 END) AS potential_lost_revenue

FROM properties p
LEFT JOIN units u ON p.id = u.property_id
GROUP BY p.id;

-- View: Maintenance KPIs
CREATE OR REPLACE VIEW `view_maintenance_kpis` AS
SELECT 
    p.name AS property_name,
    COUNT(mr.id) AS total_requests,
    
    -- Status counts
    SUM(CASE WHEN mr.status = 'Open' THEN 1 ELSE 0 END) AS open_requests,
    SUM(CASE WHEN mr.status = 'Completed' THEN 1 ELSE 0 END) AS completed_requests,
    
    -- Performance
    AVG(DATEDIFF(mr.completed_date, mr.created_at)) AS avg_resolution_days,
    SUM(mr.final_cost) AS total_maintenance_spend,
    
    -- Cost per unit
    ROUND(SUM(mr.final_cost) / (SELECT COUNT(*) FROM units WHERE property_id = p.id), 2) AS avg_cost_per_unit

FROM maintenance_requests mr
JOIN units u ON mr.unit_id = u.id
JOIN properties p ON u.property_id = p.id
GROUP BY p.id;

-- =======================================================================================
-- 3. AI & PREDICTIVE ANALYTICS MODELS (SQL-Based Heuristics)
-- =======================================================================================

-- AI FEATURE ENGINEERING: Tenant Behavior Profile
-- Aggregates raw data into features for risk scoring
CREATE OR REPLACE VIEW `ai_tenant_features` AS
SELECT 
    u.id AS user_id,
    CONCAT(u.first_name, ' ', u.last_name) AS tenant_name,
    
    -- Feature 1: Payment Punctuality (Avg days late)
    (SELECT AVG(DATEDIFF(pay.payment_date, inv.due_date))
     FROM payments pay JOIN invoices inv ON pay.invoice_id = inv.id 
     WHERE inv.tenant_id = u.id) AS avg_days_late,
     
    -- Feature 2: Bounced Payments
    (SELECT COUNT(*) FROM payments WHERE payer_id = u.id AND status = 'bounced') AS bounced_checks_count,
    
    -- Feature 3: Partial Payment Frequency
    (SELECT COUNT(*) FROM invoices WHERE tenant_id = u.id AND status = 'partial') AS partial_payments_count,
    
    -- Feature 4: Maintenance Usage (High maintenance tenants might be higher churn risk)
    (SELECT COUNT(*) FROM maintenance_requests WHERE requester_id = u.id) AS maintenance_requests_count

FROM users u
WHERE u.role_id = (SELECT id FROM roles WHERE role_name = 'Tenant');

-- PROCEDURE: Predict Rent Default (Generates Score 0-100)
-- 0 = Low Risk, 100 = High Risk
DELIMITER //

CREATE PROCEDURE `sp_predict_tenant_risk`(IN p_tenant_id INT, OUT p_risk_score INT, OUT p_risk_level VARCHAR(20))
BEGIN
    DECLARE v_avg_late DOUBLE;
    DECLARE v_bounced INT;
    DECLARE v_partial INT;
    DECLARE v_score INT DEFAULT 0;
    
    -- Get Features
    SELECT IFNULL(avg_days_late, 0), bounced_checks_count, partial_payments_count
    INTO v_avg_late, v_bounced, v_partial
    FROM ai_tenant_features WHERE user_id = p_tenant_id;
    
    -- Scoring Logic (Heuristic Model)
    -- 1. Late Payment Impact
    IF v_avg_late > 10 THEN SET v_score = v_score + 40;
    ELSEIF v_avg_late > 5 THEN SET v_score = v_score + 20;
    END IF;
    
    -- 2. Bounced Checks Impact
    SET v_score = v_score + (v_bounced * 30); -- High penalty for bounced checks
    
    -- 3. Partial Payments Impact
    SET v_score = v_score + (v_partial * 5);
    
    -- Cap Score at 100
    IF v_score > 100 THEN SET v_score = 100; END IF;
    IF v_score < 0 THEN SET v_score = 0; END IF;
    
    -- Determine Level
    IF v_score >= 70 THEN SET p_risk_level = 'HIGH';
    ELSEIF v_score >= 40 THEN SET p_risk_level = 'MEDIUM';
    ELSE SET p_risk_level = 'LOW';
    END IF;
    
    SET p_risk_score = v_score;
    
    -- Store Prediction
    INSERT INTO ai_analytics_predictions (entity_type, entity_id, prediction_type, score, prediction_data)
    VALUES ('tenant', p_tenant_id, 'rent_default_risk', v_score/100, JSON_OBJECT('days_late', v_avg_late, 'bounced', v_bounced));
    
END //

-- PROCEDURE: Forecast Maintenance Costs (Simple Linear Projection)
-- logic: Takes avg monthly maintenance cost * 1.1 (inflation/wear-and-tear buffer)
CREATE PROCEDURE `sp_forecast_maintenance`(IN p_property_id INT)
BEGIN
    DECLARE v_avg_monthly_cost DECIMAL(15,2);
    DECLARE v_forecast DECIMAL(15,2);
    
    SELECT AVG(monthly_sum) INTO v_avg_monthly_cost
    FROM (
        SELECT DATE_FORMAT(completed_date, '%Y-%m') as m, SUM(final_cost) as monthly_sum
        FROM maintenance_requests 
        WHERE property_id = p_property_id AND status = 'Completed'
        GROUP BY m
    ) as historical_data;
    
    IF v_avg_monthly_cost IS NULL THEN SET v_avg_monthly_cost = 0; END IF;
    
    -- Apply 10% inflation factor
    SET v_forecast = v_avg_monthly_cost * 1.10;
    
    INSERT INTO ai_analytics_predictions (entity_type, entity_id, prediction_type, score, prediction_data)
    VALUES ('property', p_property_id, 'maintenance_forecast_next_month', 0, JSON_OBJECT('forecast_amount', v_forecast));
    
END //

DELIMITER ;
