CREATE DATABASE IF NOT EXISTS webgis
	CHARACTER SET utf8mb4
	COLLATE utf8mb4_unicode_ci;

USE webgis;

CREATE TABLE IF NOT EXISTS spbu (
	id INT AUTO_INCREMENT PRIMARY KEY,
	nama VARCHAR(200) NOT NULL,
	no VARCHAR(60) NOT NULL,
	status ENUM('Ya','Tidak') NOT NULL DEFAULT 'Tidak',
	lat DECIMAL(10,7) NOT NULL,
	lng DECIMAL(10,7) NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS jalan (
	id INT AUTO_INCREMENT PRIMARY KEY,
	nama VARCHAR(200) NOT NULL,
	status ENUM('Nasional','Provinsi','Kabupaten') NOT NULL DEFAULT 'Kabupaten',
	panjang DECIMAL(12,2) NOT NULL DEFAULT 0,
	koordinat LONGTEXT NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS parsil (
	id INT AUTO_INCREMENT PRIMARY KEY,
	nama VARCHAR(200) NOT NULL,
	pemilik VARCHAR(200) NOT NULL,
	status ENUM('SHM','HGB','Sewa','Lainnya') NOT NULL DEFAULT 'SHM',
	luas DECIMAL(14,2) NOT NULL DEFAULT 0,
	koordinat LONGTEXT NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS kemiskinan (
	id INT AUTO_INCREMENT PRIMARY KEY,
	nama_kk VARCHAR(200) NOT NULL,
	jumlah_kk INT NOT NULL DEFAULT 1,
	alamat TEXT NOT NULL,
	lat DECIMAL(10,7) NOT NULL,
	lng DECIMAL(10,7) NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS masjid (
	id INT AUTO_INCREMENT PRIMARY KEY,
	nama_masjid VARCHAR(200) NOT NULL,
	pic_masjid VARCHAR(200) NOT NULL,
	alamat_masjid TEXT NOT NULL,
	radius INT NOT NULL DEFAULT 500,
	lat DECIMAL(10,7) NOT NULL,
	lng DECIMAL(10,7) NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;