-- Fortopolio schema
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS profile (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  headline VARCHAR(200) DEFAULT NULL,
  bio TEXT,
  photo VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  description TEXT,
  image VARCHAR(255) DEFAULT NULL,
  link VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS about (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content LONGTEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contact_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL,
  subject VARCHAR(200) DEFAULT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_read TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (username, password_hash) VALUES ('admin', '$2b$10$POv.6f/Me7mLbqo8ja1IR.tmI/B07/ScAIexUJ0lm7P/9G4BT8KJK')
ON DUPLICATE KEY UPDATE username=username;

INSERT INTO profile (full_name, headline, bio, photo) VALUES
('Nama Anda', 'Web Developer & Enthusiast', 'Tuliskan ringkasan profil singkat Anda di sini.', NULL)
ON DUPLICATE KEY UPDATE full_name=VALUES(full_name);

INSERT INTO about (content) VALUES
('Aplikasi Fortopolio: halaman ini dapat Anda edit dari admin untuk menceritakan pengalaman, nilai, dan perjalanan karier Anda.')
ON DUPLICATE KEY UPDATE content=VALUES(content);
