SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS pengunjung, ucapan, rsvp, galeri, tamu, acara, users;

CREATE TABLE users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE,
 password VARCHAR(255) NOT NULL, created_at DATETIME NULL, updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE acara (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nama_acara VARCHAR(150) NOT NULL, nama_anak VARCHAR(150) NOT NULL,
 tanggal DATE NOT NULL, waktu_mulai TIME NOT NULL, waktu_selesai TIME NOT NULL, lokasi TEXT NOT NULL,
 google_maps_url TEXT NOT NULL, google_maps_embed TEXT NULL, foto_anak VARCHAR(255) NULL, doa TEXT NOT NULL,
 nama_orang_tua VARCHAR(200) NULL, created_at DATETIME NULL, updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE tamu (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nama_tamu VARCHAR(180) NOT NULL, slug VARCHAR(190) NOT NULL UNIQUE,
 nomor_hp VARCHAR(30) NULL, status_kehadiran ENUM('belum','hadir','tidak_hadir') NOT NULL DEFAULT 'belum',
 created_at DATETIME NULL, updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE galeri (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, foto VARCHAR(255) NOT NULL, caption VARCHAR(180) NULL,
 urutan INT NOT NULL DEFAULT 0, created_at DATETIME NULL, updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE rsvp (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tamu_id INT UNSIGNED NULL, nama VARCHAR(180) NOT NULL,
 status ENUM('hadir','tidak_hadir') NOT NULL, jumlah_tamu INT NOT NULL DEFAULT 1, pesan TEXT NULL,
 created_at DATETIME NULL, updated_at DATETIME NULL,
 CONSTRAINT fk_rsvp_tamu FOREIGN KEY (tamu_id) REFERENCES tamu(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE ucapan (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nama_pengirim VARCHAR(180) NOT NULL, ucapan TEXT NOT NULL,
 is_approved TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NULL, updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE pengunjung (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tamu_id INT UNSIGNED NULL, ip_address VARCHAR(45) NOT NULL,
 user_agent TEXT NULL, visited_at DATETIME NOT NULL,
 CONSTRAINT fk_pengunjung_tamu FOREIGN KEY (tamu_id) REFERENCES tamu(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users(name,email,password,created_at,updated_at) VALUES
('Administrator','admin@khitanan.test','$2y$10$0ir7lmIChccj2QNXK0SocOcu3BwWEbwUV9VEkvI8.rtWG.jyw2./G',NOW(),NOW());
INSERT INTO acara(nama_acara,nama_anak,tanggal,waktu_mulai,waktu_selesai,lokasi,google_maps_url,google_maps_embed,doa,nama_orang_tua,created_at,updated_at) VALUES
('Tasyakuran Khitanan','Muhammad Uwas Al Qorni','2026-07-02','10:00:00','17:00:00','Link. Sayabulu RT.04 RW.07 Kelurahan Serang Gg. Rambutan Kota Serang','https://maps.google.com/?q=Link.+Sayabulu+Kota+Serang','https://www.google.com/maps?q=Link.+Sayabulu+Kota+Serang&output=embed','Semoga menjadi anak yang shalih, berbakti kepada orang tua, taat beragama, serta berguna bagi nusa, bangsa, dan agama.','Keluarga Besar Muhammad Uwas Al Qorni',NOW(),NOW());
INSERT INTO tamu(nama_tamu,slug,nomor_hp,status_kehadiran,created_at,updated_at) VALUES
('Bapak Ahmad dan Keluarga','bapak-ahmad-dan-keluarga','081234567890','belum',NOW(),NOW()),
('Bapak/Ibu/Saudara/i','bapak-ibu-saudara-i',NULL,'belum',NOW(),NOW());
SET FOREIGN_KEY_CHECKS=1;
