CREATE DATABASE IF NOT EXISTS catatanwarung;
USE catatanwarung;

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,
	role ENUM('member', 'platform_admin') NOT NULL DEFAULT 'member',
	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;

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;

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;

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;

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;

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;

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;

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,
	mode_inventori ENUM('tanpa_stok', 'dengan_stok') NOT NULL DEFAULT 'tanpa_stok',
	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;

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,
	barang_id INT NULL,
	qty INT 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;

-- Data kodepos: import via `npm run db:kodepos`
-- Sumber: https://github.com/edwin/database-kodepos-seluruh-indonesia
CREATE TABLE IF NOT EXISTS tbl_kodepos (
	id INT NOT NULL AUTO_INCREMENT,
	kelurahan VARCHAR(100) NOT NULL,
	kecamatan VARCHAR(100) NOT NULL,
	kabupaten VARCHAR(100) NOT NULL,
	provinsi VARCHAR(100) NOT NULL,
	kodepos VARCHAR(5) NOT NULL,
	PRIMARY KEY (id),
	INDEX ix_kodepos (kodepos),
	INDEX ix_provinsi (provinsi),
	INDEX ix_kabupaten (provinsi, kabupaten),
	INDEX ix_kecamatan (provinsi, kabupaten, kecamatan)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ref_kategori_warung (
	id INT AUTO_INCREMENT PRIMARY KEY,
	kode VARCHAR(30) NOT NULL UNIQUE,
	nama VARCHAR(50) NOT NULL,
	icon VARCHAR(10) NOT NULL DEFAULT '📦',
	urutan INT NOT NULL DEFAULT 0,
	is_aktif TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ref_hari_buka (
	id INT AUTO_INCREMENT PRIMARY KEY,
	kode VARCHAR(30) NOT NULL UNIQUE,
	label VARCHAR(50) NOT NULL,
	urutan INT NOT NULL DEFAULT 0,
	is_aktif TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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;

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;

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;

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;

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;

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;

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;