import mysql from 'mysql2/promise';
import bcrypt from 'bcryptjs';
import { seedReferensi } from './referensi-seed.mjs';
import { LANDING_ITEMS_SEED, LANDING_KONTEN_SEED } from './landing-seed.mjs';

const config = {
	host: process.env.MYSQL_HOST ?? 'localhost',
	port: Number(process.env.MYSQL_PORT ?? 3306),
	user: process.env.MYSQL_USER ?? 'root',
	password: process.env.MYSQL_PASSWORD ?? '',
	database: process.env.MYSQL_DATABASE ?? 'catatanwarung'
};

const MEMBER_EMAIL = 'member@catatanwarung.com';
const MEMBER_PASSWORD = 'member123';
const MEMBER_NAME = 'Budi Warung';

const BACKOFFICE_EMAIL = 'backoffice@catatanwarung.com';
const BACKOFFICE_PASSWORD = 'backoffice123';
const BACKOFFICE_NAME = 'Super Admin';

async function initDatabase() {
	const connection = await mysql.createConnection({
		host: config.host,
		port: config.port,
		user: config.user,
		password: config.password,
		multipleStatements: true
	});

	try {
		await connection.query(`CREATE DATABASE IF NOT EXISTS \`${config.database}\``);
		await connection.query(`USE \`${config.database}\``);

		await connection.query(`
			CREATE TABLE IF NOT EXISTS member (
				id INT AUTO_INCREMENT PRIMARY KEY,
				nama VARCHAR(100) NOT NULL,
				email VARCHAR(100) NOT NULL UNIQUE,
				password VARCHAR(255) DEFAULT NULL,
				google_id VARCHAR(100) DEFAULT NULL UNIQUE,
				avatar_url VARCHAR(500) DEFAULT NULL,
				nama_warung VARCHAR(100) DEFAULT NULL,
				telepon VARCHAR(20) DEFAULT NULL,
				alamat TEXT DEFAULT NULL,
				created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		const profileColumns = [
			'nama_warung VARCHAR(100) DEFAULT NULL',
			'telepon VARCHAR(20) DEFAULT NULL',
			'alamat TEXT DEFAULT NULL'
		];

		for (const column of profileColumns) {
			try {
				await connection.query(`ALTER TABLE member ADD COLUMN ${column}`);
			} catch {
				// kolom sudah ada
			}
		}

		try {
			await connection.query(
				`ALTER TABLE member ADD COLUMN role ENUM('member', 'platform_admin') NOT NULL DEFAULT 'member' AFTER password`
			);
		} catch {
			// kolom sudah ada
		}

		const oauthColumns = [
			'google_id VARCHAR(100) DEFAULT NULL UNIQUE',
			'avatar_url VARCHAR(500) DEFAULT NULL'
		];

		for (const column of oauthColumns) {
			try {
				await connection.query(`ALTER TABLE member ADD COLUMN ${column}`);
			} catch {
				// kolom sudah ada
			}
		}

		try {
			await connection.query('ALTER TABLE member MODIFY password VARCHAR(255) DEFAULT NULL');
		} catch {
			// sudah nullable
		}

		await connection.query(`
			CREATE TABLE IF NOT EXISTS paket_langganan (
				id INT AUTO_INCREMENT PRIMARY KEY,
				kode ENUM('free', 'standar', 'premium', 'enterprise') NOT NULL UNIQUE,
				nama VARCHAR(50) NOT NULL,
				deskripsi TEXT,
				urutan INT NOT NULL DEFAULT 0,
				is_aktif TINYINT(1) NOT NULL DEFAULT 1,
				created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		await connection.query(`
			CREATE TABLE IF NOT EXISTS paket_harga (
				id INT AUTO_INCREMENT PRIMARY KEY,
				paket_id INT NOT NULL,
				\`interval\` ENUM('bulanan', 'tahunan') NOT NULL,
				harga DECIMAL(15, 2) NOT NULL DEFAULT 0,
				UNIQUE KEY uk_paket_interval (paket_id, \`interval\`),
				FOREIGN KEY (paket_id) REFERENCES paket_langganan(id) ON DELETE CASCADE
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		await connection.query(`
			CREATE TABLE IF NOT EXISTS paket_privilege (
				id INT AUTO_INCREMENT PRIMARY KEY,
				paket_id INT NOT NULL,
				privilege_kode VARCHAR(50) NOT NULL,
				nilai VARCHAR(50) NOT NULL DEFAULT 'false',
				UNIQUE KEY uk_paket_privilege (paket_id, privilege_kode),
				FOREIGN KEY (paket_id) REFERENCES paket_langganan(id) ON DELETE CASCADE
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		await connection.query(`
			CREATE TABLE IF NOT EXISTS member_langganan (
				id INT AUTO_INCREMENT PRIMARY KEY,
				member_id INT NOT NULL,
				paket_id INT NOT NULL,
				\`interval\` ENUM('bulanan', 'tahunan') NOT NULL DEFAULT 'bulanan',
				status ENUM('aktif', 'kedaluwarsa', 'trial', 'dibatalkan') NOT NULL DEFAULT 'aktif',
				mulai_at DATE NOT NULL,
				berakhir_at DATE DEFAULT NULL,
				keterangan VARCHAR(255) DEFAULT '',
				created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE,
				FOREIGN KEY (paket_id) REFERENCES paket_langganan(id),
				INDEX idx_member_status (member_id, status)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		await connection.query(`
			CREATE TABLE IF NOT EXISTS member_privilege_override (
				id INT AUTO_INCREMENT PRIMARY KEY,
				member_id INT NOT NULL,
				privilege_kode VARCHAR(50) NOT NULL,
				nilai VARCHAR(50) NOT NULL,
				keterangan VARCHAR(255) DEFAULT '',
				UNIQUE KEY uk_member_privilege (member_id, privilege_kode),
				FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		const paketSeed = [
			['free', 'Free', 'Cocok untuk mulai mencatat penjualan harian', 1],
			['standar', 'Standar', 'Inventori stok & hutang piutang untuk warung berkembang', 2],
			['premium', 'Premium', 'Fitur lengkap termasuk opname, laporan PDF & warung publik', 3],
			['enterprise', 'Enterprise', 'Tanpa batas untuk jaringan warung & kebutuhan khusus', 4]
		];

		for (const [kode, nama, deskripsi, urutan] of paketSeed) {
			await connection.query(
				`INSERT INTO paket_langganan (kode, nama, deskripsi, urutan, is_aktif)
				 VALUES (?, ?, ?, ?, 1)
				 ON DUPLICATE KEY UPDATE nama = VALUES(nama), deskripsi = VALUES(deskripsi), urutan = VALUES(urutan)`,
				[kode, nama, deskripsi, urutan]
			);
		}

		const [paketRows] = await connection.query('SELECT id, kode FROM paket_langganan');
		const paketMap = Object.fromEntries(paketRows.map((r) => [r.kode, r.id]));

		const hargaSeed = [
			['free', 'bulanan', 0],
			['free', 'tahunan', 0],
			['standar', 'bulanan', 49000],
			['standar', 'tahunan', 490000],
			['premium', 'bulanan', 99000],
			['premium', 'tahunan', 990000],
			['enterprise', 'bulanan', 299000],
			['enterprise', 'tahunan', 2990000]
		];

		for (const [kode, interval, harga] of hargaSeed) {
			await connection.query(
				`INSERT INTO paket_harga (paket_id, \`interval\`, harga)
				 VALUES (?, ?, ?)
				 ON DUPLICATE KEY UPDATE harga = VALUES(harga)`,
				[paketMap[kode], interval, harga]
			);
		}

		const privilegeSeed = {
			free: {
				inventori_stok: 'false',
				stok_opname: 'false',
				hutang_piutang: 'false',
				laporan_pdf: 'false',
				warung_publik: 'false',
				max_barang: '0',
				max_transaksi_bulan: '100'
			},
			standar: {
				inventori_stok: 'true',
				stok_opname: 'false',
				hutang_piutang: 'true',
				laporan_pdf: 'false',
				warung_publik: 'false',
				max_barang: '50',
				max_transaksi_bulan: '500'
			},
			premium: {
				inventori_stok: 'true',
				stok_opname: 'true',
				hutang_piutang: 'true',
				laporan_pdf: 'true',
				warung_publik: 'true',
				max_barang: '200',
				max_transaksi_bulan: '0'
			},
			enterprise: {
				inventori_stok: 'true',
				stok_opname: 'true',
				hutang_piutang: 'true',
				laporan_pdf: 'true',
				warung_publik: 'true',
				max_barang: '0',
				max_transaksi_bulan: '0'
			}
		};

		for (const [kode, privileges] of Object.entries(privilegeSeed)) {
			for (const [privilegeKode, nilai] of Object.entries(privileges)) {
				await connection.query(
					`INSERT INTO paket_privilege (paket_id, privilege_kode, nilai)
					 VALUES (?, ?, ?)
					 ON DUPLICATE KEY UPDATE nilai = VALUES(nilai)`,
					[paketMap[kode], privilegeKode, nilai]
				);
			}
		}

		try {
			await connection.query(
				`ALTER TABLE backoffice_user ADD COLUMN role ENUM('super_admin', 'admin', 'viewer') NOT NULL DEFAULT 'admin' AFTER password`
			);
		} catch {
			// kolom sudah ada
		}

		await connection.query(`
			CREATE TABLE IF NOT EXISTS backoffice_user (
				id INT AUTO_INCREMENT PRIMARY KEY,
				nama VARCHAR(100) NOT NULL,
				email VARCHAR(100) NOT NULL UNIQUE,
				password VARCHAR(255) NOT NULL,
				role ENUM('super_admin', 'admin', 'viewer') NOT NULL DEFAULT 'admin',
				is_aktif TINYINT(1) NOT NULL DEFAULT 1,
				created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		const hashedMemberPassword = await bcrypt.hash(MEMBER_PASSWORD, 10);
		const hashedBackofficePassword = await bcrypt.hash(BACKOFFICE_PASSWORD, 10);

		await connection.query(
			`INSERT INTO member (nama, email, password, role, nama_warung, telepon, alamat)
			 VALUES (?, ?, ?, 'member', ?, ?, ?)
			 ON DUPLICATE KEY UPDATE
				nama = VALUES(nama),
				password = VALUES(password),
				role = 'member',
				nama_warung = VALUES(nama_warung),
				telepon = VALUES(telepon),
				alamat = VALUES(alamat)`,
			[
				MEMBER_NAME,
				MEMBER_EMAIL,
				hashedMemberPassword,
				'Warung Makmur Sejahtera',
				'081234567890',
				'Jl. Pasar Raya No. 12, Jakarta'
			]
		);

		await connection.query(
			`INSERT INTO backoffice_user (nama, email, password, role, is_aktif)
			 VALUES (?, ?, ?, 'super_admin', 1)
			 ON DUPLICATE KEY UPDATE
				nama = VALUES(nama),
				password = VALUES(password),
				role = 'super_admin',
				is_aktif = 1`,
			[BACKOFFICE_NAME, BACKOFFICE_EMAIL, hashedBackofficePassword]
		);

		const [memberRows] = await connection.query(
			'SELECT id FROM member WHERE email = ? LIMIT 1',
			[MEMBER_EMAIL]
		);
		const memberId = memberRows[0]?.id;

		await connection.query(`
			CREATE TABLE IF NOT EXISTS transaksi (
				id INT AUTO_INCREMENT PRIMARY KEY,
				member_id INT NOT NULL,
				jenis ENUM('pendapatan', 'pengeluaran') NOT NULL,
				sumber ENUM('penjualan', 'catatan') NOT NULL DEFAULT 'catatan',
				kategori VARCHAR(50) NOT NULL,
				deskripsi VARCHAR(255) NOT NULL,
				jumlah DECIMAL(15, 2) NOT NULL,
				tanggal DATE NOT NULL,
				created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE,
				INDEX idx_member_tanggal (member_id, tanggal),
				INDEX idx_member_jenis (member_id, jenis),
				INDEX idx_member_sumber (member_id, sumber)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		try {
			await connection.query(`
				ALTER TABLE transaksi
				ADD COLUMN sumber ENUM('penjualan', 'catatan') NOT NULL DEFAULT 'catatan' AFTER jenis
			`);
		} catch {
			// kolom sudah ada
		}

		await connection.query(`
			CREATE TABLE IF NOT EXISTS warung (
				id INT AUTO_INCREMENT PRIMARY KEY,
				member_id INT NOT NULL UNIQUE,
				nama VARCHAR(150) NOT NULL,
				slug VARCHAR(170) NOT NULL UNIQUE,
				deskripsi TEXT,
				kategori ENUM('jajanan', 'makanan', 'minuman', 'sembako', 'kelontong', 'campuran') NOT NULL DEFAULT 'campuran',
				alamat TEXT NOT NULL,
				kelurahan VARCHAR(100) DEFAULT NULL,
				kecamatan VARCHAR(100) DEFAULT NULL,
				kota VARCHAR(100) DEFAULT NULL,
				provinsi VARCHAR(100) DEFAULT NULL,
				kode_pos VARCHAR(10) DEFAULT NULL,
				telepon VARCHAR(20) DEFAULT NULL,
				whatsapp VARCHAR(20) DEFAULT NULL,
				latitude DECIMAL(10, 8) NOT NULL DEFAULT 0,
				longitude DECIMAL(11, 8) NOT NULL DEFAULT 0,
				jam_buka TIME DEFAULT '08:00:00',
				jam_tutup TIME DEFAULT '21:00:00',
				hari_buka VARCHAR(100) DEFAULT 'Senin-Minggu',
				foto_url VARCHAR(500) DEFAULT NULL,
				is_aktif TINYINT(1) NOT NULL DEFAULT 1,
				is_publik TINYINT(1) NOT NULL DEFAULT 1,
				created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE,
				INDEX idx_publik (is_publik, is_aktif),
				INDEX idx_kota (kota),
				INDEX idx_kategori (kategori),
				INDEX idx_koordinat (latitude, longitude)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		await seedReferensi(connection);

		await connection.query(`
			CREATE TABLE IF NOT EXISTS hutang_piutang (
				id INT AUTO_INCREMENT PRIMARY KEY,
				member_id INT NOT NULL,
				jenis ENUM('hutang', 'piutang') NOT NULL,
				nama VARCHAR(100) NOT NULL,
				deskripsi VARCHAR(255) DEFAULT '',
				jumlah DECIMAL(15, 2) NOT NULL,
				sisa DECIMAL(15, 2) NOT NULL,
				status ENUM('belum_lunas', 'lunas') NOT NULL DEFAULT 'belum_lunas',
				tanggal DATE NOT NULL,
				jatuh_tempo DATE NULL,
				created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE,
				INDEX idx_member_status (member_id, status)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		await connection.query(`
			CREATE TABLE IF NOT EXISTS barang (
				id INT AUTO_INCREMENT PRIMARY KEY,
				member_id INT NOT NULL,
				nama VARCHAR(150) NOT NULL,
				kategori VARCHAR(50) NOT NULL DEFAULT 'Lainnya',
				satuan VARCHAR(30) NOT NULL DEFAULT 'pcs',
				harga_beli DECIMAL(15, 2) DEFAULT NULL,
				harga_jual DECIMAL(15, 2) DEFAULT NULL,
				stok INT NOT NULL DEFAULT 0,
				stok_minimum INT NOT NULL DEFAULT 5,
				deskripsi VARCHAR(255) DEFAULT '',
				is_aktif TINYINT(1) NOT NULL DEFAULT 1,
				created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE,
				INDEX idx_member_aktif (member_id, is_aktif),
				INDEX idx_member_kategori (member_id, kategori)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		await connection.query(`
			CREATE TABLE IF NOT EXISTS stok_mutasi (
				id INT AUTO_INCREMENT PRIMARY KEY,
				member_id INT NOT NULL,
				barang_id INT NOT NULL,
				jenis ENUM('masuk', 'keluar', 'penyesuaian') NOT NULL,
				jumlah INT NOT NULL,
				stok_sebelum INT NOT NULL,
				stok_sesudah INT NOT NULL,
				keterangan VARCHAR(255) DEFAULT '',
				tanggal DATE NOT NULL,
				created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE,
				FOREIGN KEY (barang_id) REFERENCES barang(id) ON DELETE CASCADE,
				INDEX idx_barang_tanggal (barang_id, tanggal)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		await connection.query(`
			CREATE TABLE IF NOT EXISTS stok_opname (
				id INT AUTO_INCREMENT PRIMARY KEY,
				member_id INT NOT NULL,
				tanggal DATE NOT NULL,
				keterangan VARCHAR(255) DEFAULT '',
				jumlah_item INT NOT NULL DEFAULT 0,
				jumlah_selisih INT NOT NULL DEFAULT 0,
				created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE,
				INDEX idx_member_tanggal (member_id, tanggal)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		await connection.query(`
			CREATE TABLE IF NOT EXISTS stok_opname_item (
				id INT AUTO_INCREMENT PRIMARY KEY,
				opname_id INT NOT NULL,
				barang_id INT NOT NULL,
				stok_sistem INT NOT NULL,
				stok_fisik INT NOT NULL,
				selisih INT NOT NULL,
				FOREIGN KEY (opname_id) REFERENCES stok_opname(id) ON DELETE CASCADE,
				FOREIGN KEY (barang_id) REFERENCES barang(id) ON DELETE CASCADE,
				INDEX idx_opname (opname_id)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		const additiveColumns = [
			"ALTER TABLE warung ADD COLUMN mode_inventori ENUM('tanpa_stok', 'dengan_stok') NOT NULL DEFAULT 'tanpa_stok' AFTER is_publik",
			'ALTER TABLE transaksi ADD COLUMN barang_id INT NULL AFTER jumlah',
			'ALTER TABLE transaksi ADD COLUMN qty INT NULL AFTER barang_id'
		];

		for (const sql of additiveColumns) {
			try {
				await connection.query(sql);
			} catch {
				// kolom sudah ada
			}
		}

		if (memberId) {
			await connection.query('DELETE FROM stok_opname WHERE member_id = ?', [memberId]);
			await connection.query('DELETE FROM stok_mutasi WHERE member_id = ?', [memberId]);
			await connection.query('DELETE FROM barang WHERE member_id = ?', [memberId]);
			await connection.query('DELETE FROM transaksi WHERE member_id = ?', [memberId]);
			await connection.query('DELETE FROM hutang_piutang WHERE member_id = ?', [memberId]);
			await connection.query('DELETE FROM warung WHERE member_id = ? OR slug = ?', [
				memberId,
				'warung-makmur-sejahtera'
			]);

			const seedTransactions = [];
			const pendapatanData = [
				{ dayOffset: 6, items: [
					['Makanan', 'Penjualan nasi goreng', 450000],
					['Minuman', 'Es teh & kopi', 180000],
					['Snack', 'Gorengan sore', 95000]
				]},
				{ dayOffset: 5, items: [
					['Makanan', 'Paket nasi uduk pagi', 520000],
					['Minuman', 'Jus buah segar', 210000],
					['Snack', 'Pisang goreng', 75000]
				]},
				{ dayOffset: 4, items: [
					['Makanan', 'Penjualan lauk pauk', 380000],
					['Minuman', 'Air mineral & soda', 145000],
					['Snack', 'Kerupuk & emping', 62000]
				]},
				{ dayOffset: 3, items: [
					['Makanan', 'Nasi box catering', 680000],
					['Minuman', 'Teh manis panas', 165000],
					['Snack', 'Martabak mini', 120000]
				]},
				{ dayOffset: 2, items: [
					['Makanan', 'Penjualan ayam goreng', 590000],
					['Minuman', 'Es campur & cendol', 240000],
					['Snack', 'Pempek & otak-otak', 88000]
				]},
				{ dayOffset: 1, items: [
					['Makanan', 'Nasi padang prasmanan', 710000],
					['Minuman', 'Kopi susu & matcha', 195000],
					['Snack', 'Batagor & siomay', 105000]
				]},
				{ dayOffset: 0, items: [
					['Makanan', 'Penjualan mie ayam', 425000],
					['Minuman', 'Es jeruk & milo', 175000],
					['Snack', 'Bakwan & tempe mendoan', 92000]
				]}
			];

			const pengeluaranData = [
				{ dayOffset: 6, items: [
					['Bahan Baku', 'Beli beras 25kg', 350000],
					['Operasional', 'Gas elpiji 12kg', 185000]
				]},
				{ dayOffset: 4, items: [
					['Bahan Baku', 'Sayur & bumbu dapur', 220000],
					['Operasional', 'Plastik & tissue', 45000]
				]},
				{ dayOffset: 2, items: [
					['Bahan Baku', 'Daging ayam 10kg', 480000],
					['Operasional', 'Listrik & air', 125000]
				]},
				{ dayOffset: 0, items: [
					['Bahan Baku', 'Minyak goreng & tepung', 165000],
					['Operasional', 'Gaji helper harian', 100000]
				]}
			];

			for (const day of pendapatanData) {
				for (const [kategori, deskripsi, jumlah] of day.items) {
					seedTransactions.push([memberId, 'pendapatan', 'penjualan', kategori, deskripsi, jumlah, day.dayOffset]);
				}
			}

			for (const day of pengeluaranData) {
				for (const [kategori, deskripsi, jumlah] of day.items) {
					seedTransactions.push([memberId, 'pengeluaran', 'catatan', kategori, deskripsi, jumlah, day.dayOffset]);
				}
			}

			seedTransactions.push([memberId, 'pendapatan', 'catatan', 'Tip', 'Tip dari pelanggan tetap', 25000, 0]);

			for (const [mId, jenis, sumber, kategori, deskripsi, jumlah, dayOffset] of seedTransactions) {
				await connection.query(
					`INSERT INTO transaksi (member_id, jenis, sumber, kategori, deskripsi, jumlah, tanggal, created_at)
					 VALUES (?, ?, ?, ?, ?, ?, DATE_SUB(CURDATE(), INTERVAL ? DAY), DATE_SUB(NOW(), INTERVAL ? DAY))`,
					[mId, jenis, sumber, kategori, deskripsi, jumlah, dayOffset, dayOffset]
				);
			}

			const hutangPiutangSeed = [
				['hutang', 'Toko Sembako Pak Budi', 'Beli beras bon 50kg', 500000, 3],
				['piutang', 'Ibu Siti', 'Belanja bon seminggu', 175000, 7],
				['hutang', 'PDAM', 'Tagihan air bulan ini', 85000, 14]
			];

			for (const [jenis, nama, deskripsi, jumlah, jatuhOffset] of hutangPiutangSeed) {
				await connection.query(
					`INSERT INTO hutang_piutang (member_id, jenis, nama, deskripsi, jumlah, sisa, tanggal, jatuh_tempo)
					 VALUES (?, ?, ?, ?, ?, ?, CURDATE(), DATE_ADD(CURDATE(), INTERVAL ? DAY))`,
					[memberId, jenis, nama, deskripsi, jumlah, jumlah, jatuhOffset]
				);
			}

			const barangSeed = [
				['Beras Premium 5kg', 'Sembako', 'pack', 65000, 75000, 24, 10, 'Beras pulen untuk nasi uduk'],
				['Minyak Goreng 1L', 'Sembako', 'botol', 18000, 22000, 18, 8, 'Minyak kelapa sawit'],
				['Telur Ayam', 'Bahan Baku', 'kg', 28000, 35000, 6, 3, 'Telur segar harian'],
				['Teh Manis Botol', 'Minuman', 'botol', 3000, 5000, 48, 12, 'Teh manis kemasan'],
				['Kerupuk Udang', 'Snack', 'pack', 8000, 12000, 2, 5, 'Kerupuk renyah'],
				['Es Batu', 'Minuman', 'kg', 2000, 5000, 0, 5, 'Es batu untuk minuman']
			];

			for (const [nama, kategori, satuan, hargaBeli, hargaJual, stok, stokMin, deskripsi] of barangSeed) {
				await connection.query(
					`INSERT INTO barang (member_id, nama, kategori, satuan, harga_beli, harga_jual, stok, stok_minimum, deskripsi)
					 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
					[memberId, nama, kategori, satuan, hargaBeli, hargaJual, stok, stokMin, deskripsi]
				);
			}

			await connection.query(
				`UPDATE member_langganan SET status = 'dibatalkan' WHERE member_id = ? AND status = 'aktif'`,
				[memberId]
			);
			await connection.query(
				`INSERT INTO member_langganan (member_id, paket_id, \`interval\`, status, mulai_at, berakhir_at, keterangan)
				 VALUES (?, ?, 'tahunan', 'aktif', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR), 'Paket demo Premium')`,
				[memberId, paketMap.premium]
			);

			await connection.query(
				`INSERT INTO warung (
					member_id, nama, slug, deskripsi, kategori, alamat,
					kelurahan, kecamatan, kota, provinsi, kode_pos,
					telepon, whatsapp, latitude, longitude,
					jam_buka, jam_tutup, hari_buka, is_aktif, is_publik, mode_inventori
				) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
				[
					memberId,
					'Warung Makmur Sejahtera',
					'warung-makmur-sejahtera',
					'Warung jajanan tradisional: gorengan, es campur, nasi uduk, dan minuman segar.',
					'jajanan',
					'Jl. Pasar Raya No. 12',
					'PASAR MINGGU',
					'PASAR MINGGU',
					'JAKARTA SELATAN',
					'DKI JAKARTA',
					'12520',
					'081234567890',
					'081234567890',
					-6.2889,
					106.8456,
					'07:00:00',
					'21:00:00',
					'Senin-Minggu',
					1,
					1,
					'dengan_stok'
				]
			);
		}

		await connection.query(`
			CREATE TABLE IF NOT EXISTS landing_konten (
				kunci VARCHAR(100) PRIMARY KEY,
				nilai TEXT NOT NULL,
				updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		await connection.query(`
			CREATE TABLE IF NOT EXISTS landing_item (
				id INT AUTO_INCREMENT PRIMARY KEY,
				tipe ENUM('feature', 'step', 'faq', 'stat', 'mobile_highlight') NOT NULL,
				urutan INT NOT NULL DEFAULT 0,
				judul VARCHAR(200) NOT NULL,
				deskripsi TEXT DEFAULT NULL,
				icon VARCHAR(20) DEFAULT NULL,
				warna VARCHAR(100) DEFAULT NULL,
				is_aktif TINYINT(1) NOT NULL DEFAULT 1,
				created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
				updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				INDEX idx_tipe_urutan (tipe, urutan)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		`);

		for (const [kunci, nilai] of Object.entries(LANDING_KONTEN_SEED)) {
			const [existingKonten] = await connection.query(
				'SELECT kunci FROM landing_konten WHERE kunci = ? LIMIT 1',
				[kunci]
			);
			if (existingKonten.length === 0) {
				await connection.query('INSERT INTO landing_konten (kunci, nilai) VALUES (?, ?)', [
					kunci,
					nilai
				]);
			}
		}

		for (const [tipe, urutan, judul, deskripsi, icon, warna] of LANDING_ITEMS_SEED) {
			const [existing] = await connection.query(
				'SELECT id FROM landing_item WHERE tipe = ? AND urutan = ? AND judul = ? LIMIT 1',
				[tipe, urutan, judul]
			);
			if (existing.length === 0) {
				await connection.query(
					`INSERT INTO landing_item (tipe, urutan, judul, deskripsi, icon, warna, is_aktif)
					 VALUES (?, ?, ?, ?, ?, ?, 1)`,
					[tipe, urutan, judul, deskripsi, icon, warna]
				);
			}
		}

		console.log('✅ Database siap digunakan!');
		console.log(`   Database : ${config.database}`);
		console.log(`   Tabel    : member, backoffice_user, warung, paket_langganan, stok, ref_*`);
		console.log('');
		console.log('📮 Import data kodepos lengkap:');
		console.log('   npm run db:kodepos');
		console.log('');
		console.log('👤 Akun demo MEMBER (pemilik warung):');
		console.log(`   URL      : /login`);
		console.log(`   Email    : ${MEMBER_EMAIL}`);
		console.log(`   Password : ${MEMBER_PASSWORD}`);
		console.log('');
		console.log('⚙️  Akun demo BACK OFFICE (admin platform):');
		console.log(`   URL      : /backoffice/login`);
		console.log(`   Email    : ${BACKOFFICE_EMAIL}`);
		console.log(`   Password : ${BACKOFFICE_PASSWORD}`);
	} finally {
		await connection.end();
	}
}

initDatabase().catch((error) => {
	console.error('❌ Gagal inisialisasi database:', error.message);
	process.exit(1);
});
