CREATE DATABASE IF NOT EXISTS `mambu_admin` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `mambu_admin`;
CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, pass_hash VARCHAR(255) NOT NULL, name VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS profil_desa (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, content MEDIUMTEXT NOT NULL, address VARCHAR(255), phone VARCHAR(50), email VARCHAR(120), lat VARCHAR(50), lng VARCHAR(50)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS lembaga (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, leader VARCHAR(120), description MEDIUMTEXT, contact VARCHAR(120), image VARCHAR(255)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS infografis (id INT AUTO_INCREMENT PRIMARY KEY, label VARCHAR(120) NOT NULL, value INT DEFAULT 0, unit VARCHAR(20), order_index INT DEFAULT 0) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS berita (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, slug VARCHAR(220) UNIQUE, content MEDIUMTEXT, image VARCHAR(255), published_at DATE, status VARCHAR(20) DEFAULT 'draft') ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS galeri (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, image VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS pengumuman (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, content MEDIUMTEXT, start_date DATE, end_date DATE, status VARCHAR(20) DEFAULT 'aktif') ENGINE=InnoDB;
INSERT INTO users (username, pass_hash, name) VALUES ('admin', '$2y$10$wH0v7vY4mVx2a8LwC2q6UOZKQvN1y4sJX9KkWw0JjQq2Vw0fZ0I1a', 'Administrator') ON DUPLICATE KEY UPDATE username=username;