197 lines
7.6 KiB
SQL
197 lines
7.6 KiB
SQL
-- 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; |