-- database/schema.sql
-- Lost & Found Database Schema (FIXED - SYNC with ERD)
-- MySQL/MariaDB Database

-- ============================================
-- CREATE DATABASE
-- ============================================
DROP DATABASE IF EXISTS lost_and_found;
CREATE DATABASE lost_and_found CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE lost_and_found;

-- Set charset dan collation
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- Drop tables if exists (untuk clean migration)
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS revision_logs;
DROP TABLE IF EXISTS verification_logs;
DROP TABLE IF EXISTS match_results;
DROP TABLE IF EXISTS claim_verifications;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS archives;
DROP TABLE IF EXISTS claims;
DROP TABLE IF EXISTS attachments;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS lost_items;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;

-- ============================================
-- ROLES TABLE
-- ============================================
CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    permissions JSON DEFAULT NULL COMMENT 'RBAC permissions in JSON format',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_roles_name (name),
    INDEX idx_roles_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- USERS TABLE
-- ============================================
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    nrp VARCHAR(20) UNIQUE DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    role_id INT UNSIGNED NOT NULL DEFAULT 3,
    status VARCHAR(20) DEFAULT 'active',
    last_login DATETIME DEFAULT NULL COMMENT 'Last login timestamp',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT,
    INDEX idx_users_email (email),
    INDEX idx_users_nrp (nrp),
    INDEX idx_users_role_id (role_id),
    INDEX idx_users_status (status),
    INDEX idx_users_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- CATEGORIES TABLE
-- ============================================
CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    icon_url VARCHAR(255) DEFAULT NULL COMMENT 'Category icon URL',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_categories_slug (slug),
    INDEX idx_categories_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- ITEMS TABLE (Barang Ditemukan)
-- ============================================
CREATE TABLE items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    photo_url VARCHAR(255) DEFAULT NULL,
    location VARCHAR(200) NOT NULL,
    description TEXT NOT NULL COMMENT 'Public description',
    secret_details TEXT DEFAULT NULL COMMENT 'RAHASIA - untuk verifikasi klaim (hanya visible untuk owner/admin)',
    date_found DATE NOT NULL COMMENT 'Tanggal barang ditemukan',
    status VARCHAR(50) DEFAULT 'unclaimed' COMMENT 'unclaimed, claimed, expired',
    reporter_id INT UNSIGNED NOT NULL,
    reporter_name VARCHAR(100) NOT NULL,
    reporter_contact VARCHAR(50) NOT NULL,
    view_count INT DEFAULT 0 COMMENT 'Total views untuk analytics',
    expires_at TIMESTAMP NULL DEFAULT NULL COMMENT 'Tanggal barang akan dihapus (3 bulan dari date_found)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
    FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_items_category_id (category_id),
    INDEX idx_items_status (status),
    INDEX idx_items_reporter_id (reporter_id),
    INDEX idx_items_date_found (date_found),
    INDEX idx_items_expires_at (expires_at),
    INDEX idx_items_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- LOST_ITEMS TABLE (Barang Hilang)
-- ============================================
CREATE TABLE lost_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    color VARCHAR(50) DEFAULT NULL,
    location VARCHAR(200) DEFAULT NULL,
    description TEXT NOT NULL COMMENT 'Deskripsi untuk auto-matching',
    date_lost DATE NOT NULL COMMENT 'Tanggal barang hilang',
    status VARCHAR(50) DEFAULT 'active' COMMENT 'active, resolved',
    resolved_at DATETIME DEFAULT NULL COMMENT 'Kapan laporan hilang ditandai sebagai selesai',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
    INDEX idx_lost_items_user_id (user_id),
    INDEX idx_lost_items_category_id (category_id),
    INDEX idx_lost_items_status (status),
    INDEX idx_lost_items_date_lost (date_lost),
    INDEX idx_lost_items_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- ATTACHMENTS TABLE (Foto Barang)
-- ============================================
CREATE TABLE attachments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id INT UNSIGNED DEFAULT NULL COMMENT 'FK to items, nullable for lost_items',
    lost_item_id INT UNSIGNED DEFAULT NULL COMMENT 'FK to lost_items, nullable for items',
    file_url VARCHAR(255) NOT NULL,
    file_type VARCHAR(50) DEFAULT NULL COMMENT 'jpg, png, gif, dll',
    file_size INT DEFAULT NULL COMMENT 'File size in bytes',
    upload_by_user_id INT UNSIGNED DEFAULT NULL,
    display_order INT DEFAULT 0 COMMENT 'Order untuk display multiple photos',
    is_primary BOOLEAN DEFAULT FALSE COMMENT 'Primary photo untuk display',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (lost_item_id) REFERENCES lost_items(id) ON DELETE CASCADE,
    FOREIGN KEY (upload_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_attachments_item_id (item_id),
    INDEX idx_attachments_lost_item_id (lost_item_id),
    INDEX idx_attachments_is_primary (is_primary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- CLAIMS TABLE (Klaim Barang)
-- ============================================
CREATE TABLE claims (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    description TEXT NOT NULL COMMENT 'Deskripsi dari user untuk verifikasi',
    proof_url VARCHAR(255) DEFAULT NULL,
    contact VARCHAR(50) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending' COMMENT 'pending, approved, rejected',
    notes TEXT DEFAULT NULL COMMENT 'Admin verification notes',
    rejection_reason VARCHAR(255) DEFAULT NULL COMMENT 'Alasan penolakan klaim',
    attempt_count INT DEFAULT 1 COMMENT 'Jumlah percobaan klaim (fraud prevention)',
    verified_at TIMESTAMP NULL DEFAULT NULL,
    verified_by INT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_claims_item_id (item_id),
    INDEX idx_claims_user_id (user_id),
    INDEX idx_claims_status (status),
    INDEX idx_claims_verified_by (verified_by),
    INDEX idx_claims_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- CLAIM_VERIFICATIONS TABLE (Data Verifikasi Klaim)
-- ============================================
CREATE TABLE claim_verifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    claim_id INT UNSIGNED UNIQUE NOT NULL,
    similarity_score DECIMAL(5,2) DEFAULT 0.00 COMMENT 'Similarity score 0-100',
    matched_keywords TEXT DEFAULT NULL COMMENT 'Keywords matched (JSON format)',
    verification_notes TEXT DEFAULT NULL,
    is_auto_matched BOOLEAN DEFAULT FALSE,
    verification_method VARCHAR(50) DEFAULT 'manual' COMMENT 'manual, auto, hybrid',
    metadata JSON DEFAULT NULL COMMENT 'Additional verification data (extensible)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (claim_id) REFERENCES claims(id) ON DELETE CASCADE,
    INDEX idx_claim_verifications_claim_id (claim_id),
    INDEX idx_claim_verifications_similarity_score (similarity_score),
    INDEX idx_claim_verifications_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- VERIFICATION_LOGS TABLE (Audit Trail Verifikasi)
-- ============================================
CREATE TABLE verification_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    verification_id INT UNSIGNED NOT NULL,
    verified_by_user_id INT UNSIGNED NOT NULL,
    action VARCHAR(50) NOT NULL COMMENT 'approve, reject, pending, review',
    reason TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (verification_id) REFERENCES claim_verifications(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by_user_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_verification_logs_verification_id (verification_id),
    INDEX idx_verification_logs_verified_by_user_id (verified_by_user_id),
    INDEX idx_verification_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- MATCH_RESULTS TABLE (Hasil Auto-Matching)
-- ============================================
CREATE TABLE match_results (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lost_item_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    similarity_score DECIMAL(5,2) NOT NULL,
    matched_fields TEXT DEFAULT NULL COMMENT 'JSON format: {category, description, color}',
    match_reason VARCHAR(100) DEFAULT NULL COMMENT 'Reason for matching: color, location, description, etc',
    matched_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_notified BOOLEAN DEFAULT FALSE,
    notified_at DATETIME DEFAULT NULL COMMENT 'Kapan user diberitahu tentang match',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (lost_item_id) REFERENCES lost_items(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    INDEX idx_match_results_lost_item_id (lost_item_id),
    INDEX idx_match_results_item_id (item_id),
    INDEX idx_match_results_similarity_score (similarity_score),
    INDEX idx_match_results_is_notified (is_notified),
    INDEX idx_match_results_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- ARCHIVES TABLE (Barang yang Diarsipkan)
-- ============================================
CREATE TABLE archives (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id INT UNSIGNED UNIQUE NOT NULL COMMENT 'Original item ID',
    name VARCHAR(100) NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    photo_url VARCHAR(255) DEFAULT NULL,
    location VARCHAR(200) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    date_found DATE DEFAULT NULL COMMENT 'Tanggal barang ditemukan (dari items)',
    status VARCHAR(50) DEFAULT NULL,
    reporter_name VARCHAR(100) DEFAULT NULL,
    reporter_contact VARCHAR(50) DEFAULT NULL,
    archived_reason VARCHAR(100) DEFAULT NULL COMMENT 'expired, case_closed',
    claimed_by INT UNSIGNED DEFAULT NULL,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
    FOREIGN KEY (claimed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_archives_item_id (item_id),
    INDEX idx_archives_category_id (category_id),
    INDEX idx_archives_archived_reason (archived_reason),
    INDEX idx_archives_archived_at (archived_at),
    INDEX idx_archives_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- REVISION_LOGS TABLE (Audit Trail Edit Barang)
-- ============================================
CREATE TABLE revision_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    field_name VARCHAR(50) NOT NULL,
    old_value TEXT DEFAULT NULL,
    new_value TEXT DEFAULT NULL,
    reason TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_revision_logs_item_id (item_id),
    INDEX idx_revision_logs_user_id (user_id),
    INDEX idx_revision_logs_created_at (created_at),
    INDEX idx_revision_logs_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- AUDIT_LOGS TABLE (System Audit Trail)
-- ============================================
CREATE TABLE audit_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED DEFAULT NULL,
    action VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) DEFAULT NULL,
    entity_id INT UNSIGNED DEFAULT NULL,
    details TEXT DEFAULT NULL,
    ip_address VARCHAR(50) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_audit_logs_user_id (user_id),
    INDEX idx_audit_logs_action (action),
    INDEX idx_audit_logs_entity_type (entity_type),
    INDEX idx_audit_logs_entity_id (entity_id),
    INDEX idx_audit_logs_created_at (created_at),
    INDEX idx_audit_logs_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- NOTIFICATIONS TABLE (Notifikasi User)
-- ============================================
CREATE TABLE notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    type VARCHAR(50) NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    entity_type VARCHAR(50) DEFAULT NULL,
    entity_id INT UNSIGNED DEFAULT NULL,
    channel VARCHAR(50) DEFAULT 'push' COMMENT 'email, sms, push',
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_notifications_user_id (user_id),
    INDEX idx_notifications_type (type),
    INDEX idx_notifications_is_read (is_read),
    INDEX idx_notifications_created_at (created_at),
    INDEX idx_notifications_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SUCCESS MESSAGE
-- ============================================
SELECT '✅ Database schema created successfully!' AS Status;
SELECT '📋 Total tables: 15 (UPGRADED from 13)' AS Info;
SELECT '🔑 Indexes created on all tables' AS Info;
SELECT '🔗 Foreign keys with proper constraints' AS Info;
SELECT '✨ NOW SYNC with ERD diagram!' AS Info;
SELECT '📝 Next step: Run seed.sql to populate initial data' AS NextStep;