-- SUI API Database Schema (Reference)
-- Database: akfomyid_sui
-- Host: ftp.akfo.cc

-- ============================================================
-- 1. Users Table
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 2. Products Table
-- ============================================================
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 3. Notifications Table (EXISTING - struktur)
-- ============================================================
-- Tabel ini adalah tabel yang SUDAH ADA di database
-- Digunakan untuk menyimpan push notifications dari aplikasi Flutter

CREATE TABLE IF NOT EXISTS sui_notifications (
    sui_id INT(11) NOT NULL,
    title TEXT,
    text TEXT,
    package TEXT,
    post_time BIGINT(20),
    notification_key TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Penjelasan fields:
-- sui_id: ID pengguna (bukan primary key, bisa ada multiple notifications per user)
-- title: Judul notifikasi
-- text: Isi/konten notifikasi
-- package: Package name aplikasi yang mengirim notifikasi
-- post_time: Timestamp dalam milliseconds
-- notification_key: Unique identifier untuk notifikasi (prevent duplicates)

-- ============================================================
-- 4. Logs Table (EXISTING - struktur)
-- ============================================================
-- Tabel ini adalah tabel yang SUDAH ADA di database
-- Digunakan untuk menyimpan log activity dari aplikasi Flutter

CREATE TABLE IF NOT EXISTS sui_logs (
    sui_id INT(11) NOT NULL,
    message TEXT,
    timestamp BIGINT(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Penjelasan fields:
-- sui_id: ID pengguna
-- message: Pesan log (aktivitas yang dilakukan user)
-- timestamp: Timestamp dalam milliseconds

-- ============================================================
-- 5. Access Table (EXISTING - reference only)
-- ============================================================
-- Tabel existing yang sudah ada di database
-- Digunakan untuk access control (belum diimplementasi di API saat ini)

CREATE TABLE IF NOT EXISTS access (
    -- struktur belum diketahui secara lengkap
    -- digunakan untuk manage user access/permissions
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- USEFUL QUERIES
-- ============================================================

-- 1. Get all notifications untuk user specific
SELECT * FROM sui_notifications 
WHERE sui_id = 1 
ORDER BY post_time DESC;

-- 2. Get recent logs
SELECT * FROM sui_logs 
WHERE sui_id = 1 
ORDER BY timestamp DESC 
LIMIT 50;

-- 3. Count notifications by user
SELECT sui_id, COUNT(*) as total 
FROM sui_notifications 
GROUP BY sui_id;

-- 4. Delete old logs (older than 30 days)
DELETE FROM sui_logs 
WHERE timestamp < (UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) * 1000);

-- 5. Get user notifications with pagination
SELECT * FROM sui_notifications 
WHERE sui_id = 1 
ORDER BY post_time DESC 
LIMIT 100 OFFSET 0;

-- 6. Find duplicate notifications (by notification_key)
SELECT notification_key, COUNT(*) 
FROM sui_notifications 
GROUP BY notification_key 
HAVING COUNT(*) > 1;

-- 7. Get statistics
SELECT 
    (SELECT COUNT(*) FROM sui_notifications) as total_notifications,
    (SELECT COUNT(*) FROM sui_logs) as total_logs,
    (SELECT COUNT(DISTINCT sui_id) FROM sui_notifications) as unique_users_notif,
    (SELECT COUNT(DISTINCT sui_id) FROM sui_logs) as unique_users_logs;

-- ============================================================
-- INDEXES (untuk optimize queries)
-- ============================================================

-- Notifications indexes
ALTER TABLE sui_notifications ADD INDEX idx_sui_id (sui_id);
ALTER TABLE sui_notifications ADD INDEX idx_post_time (post_time);
ALTER TABLE sui_notifications ADD UNIQUE INDEX idx_notification_key (notification_key(100));

-- Logs indexes
ALTER TABLE sui_logs ADD INDEX idx_sui_id (sui_id);
ALTER TABLE sui_logs ADD INDEX idx_timestamp (timestamp);

-- ============================================================
-- SAMPLE DATA (untuk testing)
-- ============================================================

-- Insert sample notification
INSERT INTO sui_notifications (sui_id, title, text, package, post_time, notification_key) 
VALUES (1, 'Welcome', 'Welcome to our app', 'com.example.app', 1623000000000, 'notif_001');

-- Insert sample log
INSERT INTO sui_logs (sui_id, message, timestamp) 
VALUES (1, 'User logged in', 1623000000000);
INSERT INTO sui_logs (sui_id, message, timestamp) 
VALUES (1, 'Product viewed', 1623001000000);
INSERT INTO sui_logs (sui_id, message, timestamp) 
VALUES (1, 'Purchase completed', 1623002000000);

-- ============================================================
-- DATA TYPES EXPLANATION
-- ============================================================

INT(11)         - Integer, max size for display
BIGINT(20)      - Large integer for timestamps
VARCHAR(255)    - Variable length string
TEXT            - Large text field
DECIMAL(10, 2)  - Decimal number (10 digits, 2 after decimal)
TIMESTAMP       - Auto timestamp
BLOB            - Binary large object

-- ============================================================
-- MIGRATION NOTES
-- ============================================================

-- Jika perlu migrate data dari struktur lama:
-- 1. Backup database dulu
-- 2. Run ALTER TABLE commands untuk add missing fields
-- 3. Update models jika ada perubahan struktur
-- 4. Test API endpoints sebelum production

-- ============================================================
-- PERFORMANCE TIPS
-- ============================================================

1. Always add indexes pada fields yang sering di-query (sui_id, post_time)
2. Use LIMIT untuk pagination, jangan load semua data sekaligus
3. Consider archiving old logs untuk optimize table size
4. Monitor query performance dengan EXPLAIN

-- ============================================================
-- BACKUP & RESTORE
-- ============================================================

-- Backup database:
-- mysqldump -h ftp.akfo.cc -u akfomyid_sui -p akfomyid_sui > backup.sql

-- Restore database:
-- mysql -h ftp.akfo.cc -u akfomyid_sui -p akfomyid_sui < backup.sql

-- ============================================================