CREATE DATABASE IF NOT EXISTS portfolio_db;
USE portfolio_db;

-- Biodata Table
CREATE TABLE IF NOT EXISTS `biodata` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `address` TEXT NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `birth_date` DATE NULL,
  `last_education` VARCHAR(255) NULL,
  `summary` TEXT NULL,
  `photo` VARCHAR(255) NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Education History Table
CREATE TABLE IF NOT EXISTS `education_history` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `level` ENUM('SD','SMP','SMA','D3','S1','S2','S3') DEFAULT 'SD',
  `school_name` VARCHAR(255) NOT NULL,
  `start_year` YEAR(4) NOT NULL,
  `end_year` YEAR(4) NOT NULL,
  `description` TEXT NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Daily Activities Table
CREATE TABLE IF NOT EXISTS `daily_activities` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL,
  `time` TIME NOT NULL,
  `activity_name` VARCHAR(255) NOT NULL,
  `media_path` VARCHAR(255) NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed Data: Biodata
INSERT INTO `biodata` (`name`, `email`, `phone`, `address`, `birth_date`, `last_education`, `summary`, `photo`, `created_at`, `updated_at`) VALUES
('Aul', 'auliaasandraa17@gmail.com', '+62 812-3456-7890', 'Cisoka Tangerang', '2005-02-17', 'SMA', 'Saya adalah seorang profesional yang berdedikasi dengan passion di bidang teknologi dan desain. Saya senang mempelajari hal-hal baru dan berkontribusi dalam proyek-proyek kreatif.', 'profile_aul_v2.jpg', NOW(), NOW());

-- Seed Data: Education
INSERT INTO `education_history` (`level`, `school_name`, `start_year`, `end_year`, `description`, `created_at`, `updated_at`) VALUES
('SD', 'SD Tigaraksa 4', '2011', '2017', NULL, NOW(), NOW()),
('SMP', 'SMP Negri 3 Solear', '2017', '2020', NULL, NOW(), NOW()),
('SMA', 'SMA Negri 6 Kab Tangerang', '2020', '2023', NULL, NOW(), NOW()),
('S1', 'Universitas Yatsi Madani', '2023', '2027', 'Semester 5', NOW(), NOW());

-- Seed Data: Activities
INSERT INTO `daily_activities` (`date`, `time`, `activity_name`, `media_path`, `created_at`, `updated_at`) VALUES
(CURDATE(), '07:00:00', 'Jogging Pagi', NULL, NOW(), NOW()),
(CURDATE(), '09:00:00', 'Belajar Coding (CodeIgniter 4)', NULL, NOW(), NOW()),
(CURDATE(), '13:00:00', 'Mengerjakan Tugas Kuliah', NULL, NOW(), NOW()),
(CURDATE(), '19:00:00', 'Diskusi Tim Proyek', NULL, NOW(), NOW());
