-- ========================================
-- SQL Helper untuk Testing KSP Mobile API
-- ========================================

-- 1. CREATE PERSONAL ACCESS TOKENS TABLE (untuk Laravel Sanctum)
-- ========================================
CREATE TABLE IF NOT EXISTS `personal_access_tokens` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) NOT NULL,
  `tokenable_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `token` varchar(64) NOT NULL,
  `abilities` text,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. INSERT MEMBER TESTING
-- ========================================
-- Password: "password" (hashed dengan bcrypt)
INSERT INTO `member` (
  `nik`, 
  `no_anggota`, 
  `password`, 
  `nama`, 
  `tempat_lahir`,
  `tanggal_lahir`,
  `jenis_kelamin`,
  `email`, 
  `no_telp`, 
  `status`, 
  `unit_kerja`,
  `alamat`,
  `total_pokok`,
  `total_wajib`,
  `total_tabungan`,
  `create_time`
) VALUES 
(
  '1234567890',
  'AGT-001',
  '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
  'John Doe',
  'Jakarta',
  '1990-01-15',
  'L',
  'john@example.com',
  '081234567890',
  'aktif',
  'IT Department',
  'Jl. Testing No. 123, Jakarta',
  100000,
  50000,
  200000,
  NOW()
),
(
  '0987654321',
  'AGT-002',
  '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
  'Jane Smith',
  'Bandung',
  '1992-05-20',
  'P',
  'jane@example.com',
  '081234567891',
  'aktif',
  'Finance Department',
  'Jl. Example No. 456, Bandung',
  100000,
  50000,
  150000,
  NOW()
);

-- 3. INSERT JENIS SIMPANAN
-- ========================================
INSERT INTO `jenis_simpanan` (`id`, `jenis`, `bunga_perbulan`, `urutan`, `create_time`) VALUES
(1, 'Simpanan Pokok', 0.000, 1, NOW()),
(7, 'Simpanan Wajib', 0.000, 2, NOW()),
(8, 'Simpanan Gerbung', 0.000, 3, NOW()),
(9, 'Simpanan Property', 0.000, 4, NOW()),
(10, 'Simpanan Syariah', 0.000, 5, NOW()),
(11, 'SHU', 0.000, 6, NOW()),
(12, 'SHU 2023', 0.000, 7, NOW())
ON DUPLICATE KEY UPDATE 
  `jenis` = VALUES(`jenis`),
  `update_time` = NOW();

-- 4. INSERT JENIS PINJAMAN
-- ========================================
INSERT INTO `jenis_pinjaman` (`id`, `nama_pinjaman`, `urutan`, `create_time`) VALUES
(1, 'Pinjaman Reguler', 1, NOW()),
(2, 'Pinjaman Darurat', 2, NOW()),
(3, 'Pinjaman Pendidikan', 3, NOW()),
(4, 'Pinjaman Usaha', 4, NOW())
ON DUPLICATE KEY UPDATE 
  `nama_pinjaman` = VALUES(`nama_pinjaman`),
  `update_time` = NOW();

-- 5. INSERT SAMPLE SIMPANAN
-- ========================================
-- Simpanan untuk member John Doe (id=1, nik=1234567890)
INSERT INTO `simpanan` (
  `id_member`, 
  `tanggal`, 
  `no_transaksi`, 
  `jumlah_simpanan`, 
  `jenis_simpanan`,
  `is_closing`,
  `create_time`
) VALUES
-- Simpanan Pokok
(1, '2024-01-01', 'SMP-001-2024', 100000, 1, 0, NOW()),
-- Simpanan Wajib
(1, '2024-01-01', 'SMP-002-2024', 50000, 7, 0, NOW()),
(1, '2024-02-01', 'SMP-003-2024', 50000, 7, 0, NOW()),
(1, '2024-03-01', 'SMP-004-2024', 50000, 7, 0, NOW()),
-- Simpanan Sukarela/Tabungan
(1, '2024-01-15', 'SMP-005-2024', 100000, 8, 0, NOW()),
(1, '2024-02-15', 'SMP-006-2024', 100000, 8, 0, NOW());

-- Simpanan untuk member Jane Smith (id=2)
INSERT INTO `simpanan` (
  `id_member`, 
  `tanggal`, 
  `no_transaksi`, 
  `jumlah_simpanan`, 
  `jenis_simpanan`,
  `is_closing`,
  `create_time`
) VALUES
(2, '2024-01-01', 'SMP-007-2024', 100000, 1, 0, NOW()),
(2, '2024-01-01', 'SMP-008-2024', 50000, 7, 0, NOW());

-- 6. INSERT SAMPLE PINJAMAN
-- ========================================
INSERT INTO `pinjaman` (
  `id_member`,
  `tanggal`,
  `jumlah_pinjaman`,
  `no_transaksi`,
  `bunga`,
  `tenor`,
  `biaya_admin`,
  `is_lunas`,
  `jenis_bunga`,
  `id_jenis_pinjaman`,
  `create_time`
) VALUES
(
  1,
  '2024-01-10',
  5000000,
  'PNJ-001-2024',
  1.500,
  12,
  50000,
  0,
  'flat',
  1,
  NOW()
),
(
  1,
  '2024-02-15',
  3000000,
  'PNJ-002-2024',
  1.500,
  6,
  30000,
  1,
  'flat',
  2,
  NOW()
);

-- 7. INSERT SAMPLE PENGAJUAN
-- ========================================
INSERT INTO `pengajuan` (
  `nik`,
  `nama`,
  `jenis`,
  `id_jenis_pinjaman`,
  `tenor`,
  `jumlah`,
  `tanggal`,
  `is_proses`
) VALUES
(
  '1234567890',
  'John Doe',
  'pinjaman',
  1,
  12,
  10000000,
  NOW(),
  0
);

-- 8. VERIFY DATA
-- ========================================

-- Check members
SELECT 
  id, nik, no_anggota, nama, email, status, 
  total_pokok, total_wajib, total_tabungan
FROM member;

-- Check simpanan
SELECT 
  s.id, s.id_member, m.nama, s.tanggal, s.no_transaksi, 
  s.jumlah_simpanan, js.jenis as jenis_simpanan
FROM simpanan s
JOIN member m ON s.id_member = m.id
JOIN jenis_simpanan js ON s.jenis_simpanan = js.id
ORDER BY s.tanggal DESC;

-- Check pinjaman
SELECT 
  p.id, p.id_member, m.nama, p.tanggal, p.no_transaksi,
  p.jumlah_pinjaman, p.tenor, p.is_lunas, jp.nama_pinjaman
FROM pinjaman p
JOIN member m ON p.id_member = m.id
JOIN jenis_pinjaman jp ON p.id_jenis_pinjaman = jp.id
ORDER BY p.tanggal DESC;

-- Check pengajuan
SELECT 
  pe.id, pe.nik, pe.nama, pe.jenis, pe.jumlah, pe.tenor,
  pe.is_proses, jp.nama_pinjaman
FROM pengajuan pe
LEFT JOIN jenis_pinjaman jp ON pe.id_jenis_pinjaman = jp.id
ORDER BY pe.tanggal DESC;

-- 9. UPDATE TOTAL SIMPANAN MEMBER (Optional - untuk sinkronisasi)
-- ========================================
UPDATE member m
SET 
  total_pokok = (
    SELECT COALESCE(SUM(jumlah_simpanan), 0) 
    FROM simpanan 
    WHERE id_member = m.id AND jenis_simpanan = 1
  ),
  total_wajib = (
    SELECT COALESCE(SUM(jumlah_simpanan), 0) 
    FROM simpanan 
    WHERE id_member = m.id AND jenis_simpanan = 7
  ),
  total_tabungan = (
    SELECT COALESCE(SUM(jumlah_simpanan), 0) 
    FROM simpanan 
    WHERE id_member = m.id AND jenis_simpanan = 8
  );

-- 10. CLEANUP (untuk reset data testing)
-- ========================================
/*
-- HATI-HATI! Ini akan menghapus semua data testing

DELETE FROM simpanan WHERE id_member IN (1, 2);
DELETE FROM pinjaman WHERE id_member IN (1, 2);
DELETE FROM pengajuan WHERE nik IN ('1234567890', '0987654321');
DELETE FROM member WHERE nik IN ('1234567890', '0987654321');
DELETE FROM personal_access_tokens;

-- Reset auto increment
ALTER TABLE simpanan AUTO_INCREMENT = 1;
ALTER TABLE pinjaman AUTO_INCREMENT = 1;
ALTER TABLE pengajuan AUTO_INCREMENT = 1;
ALTER TABLE member AUTO_INCREMENT = 1;
*/

-- ========================================
-- NOTES:
-- 1. Password untuk member testing: "password"
-- 2. NIK untuk login: 1234567890 atau 0987654321
-- 3. Pastikan tabel personal_access_tokens sudah dibuat sebelum login
-- ========================================
