-- database/enhancement.sql
-- ============================================
-- ENHANCEMENT FOR LOST & FOUND DATABASE (SAFE MODE)
-- Hanya Procedures, Views, dan Indexes
USE iot_db;

DELIMITER $$

-- ============================================
-- STORED PROCEDURES
-- ============================================

-- Procedure: Archive expired items
DROP PROCEDURE IF EXISTS sp_archive_expired_items$$
CREATE PROCEDURE sp_archive_expired_items(
    OUT p_archived_count INT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_item_id INT;
    DECLARE cur CURSOR FOR 
        SELECT id FROM items 
        WHERE expires_at < NOW() 
        AND status = 'unclaimed'
        AND deleted_at IS NULL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    SET p_archived_count = 0;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_item_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Archive the item
        INSERT INTO archives (
            item_id, name, category_id, photo_url, location,
            description, date_found, status, reporter_name,
            reporter_contact, archived_reason
        )
        SELECT 
            id, name, category_id, photo_url, location,
            description, date_found, status, reporter_name,
            reporter_contact, 'expired'
        FROM items
        WHERE id = v_item_id;
        
        -- Update item status
        UPDATE items
        SET status = 'expired'
        WHERE id = v_item_id;
        
        SET p_archived_count = p_archived_count + 1;
    END LOOP;
    
    CLOSE cur;
END$$

-- Procedure: Get dashboard statistics
DROP PROCEDURE IF EXISTS sp_get_dashboard_stats$$
CREATE PROCEDURE sp_get_dashboard_stats(
    OUT p_total_items INT,
    OUT p_unclaimed_items INT,
    OUT p_verified_items INT,
    OUT p_pending_claims INT
)
BEGIN
    SELECT COUNT(*) INTO p_total_items FROM items WHERE deleted_at IS NULL;
    SELECT COUNT(*) INTO p_unclaimed_items FROM items WHERE status = 'unclaimed' AND deleted_at IS NULL;
    SELECT COUNT(*) INTO p_verified_items FROM items WHERE status = 'verified' AND deleted_at IS NULL;
    SELECT COUNT(*) INTO p_pending_claims FROM claims WHERE status = 'pending' AND deleted_at IS NULL;
END$$

DELIMITER ;

-- ============================================
-- VIEWS
-- ============================================

CREATE OR REPLACE VIEW vw_dashboard_stats AS
SELECT 
    (SELECT COUNT(*) FROM items WHERE status = 'unclaimed' AND deleted_at IS NULL) AS total_unclaimed,
    (SELECT COUNT(*) FROM items WHERE status = 'verified' AND deleted_at IS NULL) AS total_verified,
    (SELECT COUNT(*) FROM lost_items WHERE status = 'active' AND deleted_at IS NULL) AS total_lost_reports,
    (SELECT COUNT(*) FROM claims WHERE status = 'pending' AND deleted_at IS NULL) AS pending_claims,
    (SELECT COUNT(*) FROM match_results WHERE is_notified = FALSE AND deleted_at IS NULL) AS unnotified_matches;

CREATE OR REPLACE VIEW vw_items_detail AS
SELECT 
    i.id, i.name, c.name AS category_name, c.slug AS category_slug,
    i.photo_url, i.location, i.date_found, i.status,
    i.reporter_name, i.reporter_contact, i.expires_at,
    u.name AS reporter_user_name, u.email AS reporter_email,
    DATEDIFF(i.expires_at, NOW()) AS days_until_expire, i.created_at
FROM items i
JOIN categories c ON i.category_id = c.id
JOIN users u ON i.reporter_id = u.id
WHERE i.deleted_at IS NULL;

CREATE OR REPLACE VIEW vw_claims_detail AS
SELECT 
    c.id, c.status, i.name AS item_name, cat.name AS category_name,
    u.name AS claimant_name, u.email AS claimant_email, u.phone AS claimant_phone,
    c.description AS claim_description, c.contact, cv.similarity_score,
    c.verified_at, v.name AS verified_by_name, c.notes, c.created_at
FROM claims c
JOIN items i ON c.item_id = i.id
JOIN categories cat ON i.category_id = cat.id
JOIN users u ON c.user_id = u.id
LEFT JOIN claim_verifications cv ON c.id = cv.claim_id
LEFT JOIN users v ON c.verified_by = v.id
WHERE c.deleted_at IS NULL;

CREATE OR REPLACE VIEW vw_match_results_detail AS
SELECT 
    mr.id, li.name AS lost_item_name, li.user_id AS lost_by_user_id,
    u.name AS lost_by_user_name, u.email AS lost_by_email,
    i.name AS found_item_name, i.reporter_name AS found_by_name,
    mr.similarity_score, mr.is_notified, mr.matched_at,
    i.id AS found_item_id, li.id AS lost_item_id
FROM match_results mr
JOIN lost_items li ON mr.lost_item_id = li.id
JOIN items i ON mr.item_id = i.id
JOIN users u ON li.user_id = u.id
WHERE mr.deleted_at IS NULL
ORDER BY mr.similarity_score DESC;

CREATE OR REPLACE VIEW vw_category_stats AS
SELECT 
    c.id, c.name, c.slug,
    COUNT(DISTINCT i.id) AS total_items,
    COUNT(DISTINCT CASE WHEN i.status = 'unclaimed' THEN i.id END) AS unclaimed_items,
    COUNT(DISTINCT CASE WHEN i.status = 'verified' THEN i.id END) AS verified_items,
    COUNT(DISTINCT li.id) AS total_lost_reports
FROM categories c
LEFT JOIN items i ON c.id = i.category_id AND i.deleted_at IS NULL
LEFT JOIN lost_items li ON c.id = li.category_id AND li.deleted_at IS NULL
WHERE c.deleted_at IS NULL
GROUP BY c.id, c.name, c.slug;

CREATE OR REPLACE VIEW vw_user_activity AS
SELECT 
    u.id, u.name, u.email, r.name AS role_name,
    COUNT(DISTINCT i.id) AS items_reported,
    COUNT(DISTINCT li.id) AS lost_items_reported,
    COUNT(DISTINCT cl.id) AS claims_made,
    COUNT(DISTINCT CASE WHEN cl.status = 'approved' THEN cl.id END) AS claims_approved,
    u.created_at AS member_since
FROM users u
JOIN roles r ON u.role_id = r.id
LEFT JOIN items i ON u.id = i.reporter_id AND i.deleted_at IS NULL
LEFT JOIN lost_items li ON u.id = li.user_id AND li.deleted_at IS NULL
LEFT JOIN claims cl ON u.id = cl.user_id AND cl.deleted_at IS NULL
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.name, u.email, r.name, u.created_at;

CREATE OR REPLACE VIEW vw_recent_activities AS
SELECT 
    al.id, al.action, al.entity_type, al.entity_id, al.details,
    u.name AS user_name, u.email AS user_email, r.name AS user_role,
    al.ip_address, al.created_at
FROM audit_logs al
LEFT JOIN users u ON al.user_id = u.id
LEFT JOIN roles r ON u.role_id = r.id
ORDER BY al.created_at DESC
LIMIT 100;

-- ============================================
-- INDEXES
-- ============================================

-- Hapus index jika exist (cara aman di MySQL lama agak ribet, jadi langsung CREATE saja biasanya OK jika belum ada)
-- Gunakan DROP INDEX jika perlu mereset index

CREATE INDEX idx_items_status_category ON items(status, category_id, deleted_at);
CREATE INDEX idx_items_date_status ON items(date_found, status, deleted_at);
CREATE INDEX idx_claims_status_item ON claims(status, item_id, deleted_at);
CREATE INDEX idx_match_results_scores ON match_results(similarity_score DESC, is_notified);
CREATE INDEX idx_audit_logs_date_user ON audit_logs(created_at DESC, user_id);
CREATE INDEX idx_lost_items_status_user ON lost_items(status, user_id, deleted_at);
CREATE INDEX idx_notifications_user_read ON notifications(user_id, is_read, created_at DESC);

-- Full-text indexes
CREATE FULLTEXT INDEX idx_items_search ON items(name, location);
CREATE FULLTEXT INDEX idx_lost_items_search ON lost_items(name, description);

-- ============================================
-- SUCCESS MESSAGE
-- ============================================
SELECT '✅ Database enhancements (Safe Mode) created!' AS Status;
SELECT '⚙️ Procedures: 2' AS Info;
SELECT '📈 Views: 7' AS Info;
SELECT '🚀 Indexes: 9' AS Info;
SELECT '⚠️ Note: Triggers & Functions dihapus untuk menghindari Error 1419' AS Note;