365 lines
17 KiB
SQL
365 lines
17 KiB
SQL
-- 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; |