CREATE TABLE IF NOT EXISTS templates (
  id VARCHAR(64) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  fileName VARCHAR(255) NOT NULL,
  fileUrl TEXT NOT NULL,
  driveFileId VARCHAR(255) NOT NULL DEFAULT '',
  createdAt VARCHAR(64) NOT NULL,
  category VARCHAR(100) NOT NULL,
  canGenerate VARCHAR(10) NOT NULL,
  exampleFileName VARCHAR(255) NOT NULL,
  exampleFileUrl TEXT NOT NULL,
  exampleDriveFileId VARCHAR(255) NOT NULL DEFAULT ''
);

CREATE TABLE IF NOT EXISTS submissions (
  id VARCHAR(64) PRIMARY KEY,
  applicantName VARCHAR(255) NOT NULL,
  nim VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  templateId VARCHAR(64) NOT NULL,
  templateName VARCHAR(255) NOT NULL,
  notes TEXT NOT NULL,
  attachmentName VARCHAR(255) NOT NULL,
  attachmentUrl TEXT NOT NULL,
  driveFileId VARCHAR(255) NOT NULL DEFAULT '',
  progress VARCHAR(50) NOT NULL,
  statusNote TEXT NOT NULL,
  createdAt VARCHAR(64) NOT NULL,
  updatedAt VARCHAR(64) NOT NULL,
  entryDate VARCHAR(255) NOT NULL,
  purpose TEXT NOT NULL,
  applicants LONGTEXT NOT NULL,
  letterNumber VARCHAR(255) NOT NULL,
  phone VARCHAR(100) NOT NULL
);

INSERT INTO templates (
  id, name, description, fileName, fileUrl, driveFileId, createdAt,
  category, canGenerate, exampleFileName, exampleFileUrl, exampleDriveFileId
) VALUES
  ('xYORXrkEjy', 'Surat Pemohonan - Pengaktifan Virtual Account', '', 'sample-surat-pengaktifan-va-belum-terisi.doc', '/uploads/templates/1781463781749-sample-surat-pengaktifan-va-belum-terisi.doc', '', '2026-06-14T19:03:01.762Z', 'letterGroup.request', 'false', 'sample-surat-pengaktifan-va-belum-terisi.doc', '/uploads/templates/1781463781749-sample-surat-pengaktifan-va-belum-terisi.doc', ''),
  ('rKHNjKdlub', 'Surat Aktif Kuliah', 'Format contoh untuk pengajuan aktif kuliah', 'README.md', '/uploads/templates/1781439276737-README.md', '', '2026-06-14T12:14:36.739Z', 'letterGroup.request', 'false', 'README.md', '/uploads/templates/1781439276737-README.md', '')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  description = VALUES(description),
  fileName = VALUES(fileName),
  fileUrl = VALUES(fileUrl),
  driveFileId = VALUES(driveFileId),
  createdAt = VALUES(createdAt),
  category = VALUES(category),
  canGenerate = VALUES(canGenerate),
  exampleFileName = VALUES(exampleFileName),
  exampleFileUrl = VALUES(exampleFileUrl),
  exampleDriveFileId = VALUES(exampleDriveFileId);

INSERT INTO submissions (
  id, applicantName, nim, email, templateId, templateName, notes,
  attachmentName, attachmentUrl, driveFileId, progress, statusNote,
  createdAt, updatedAt, entryDate, purpose, applicants, letterNumber, phone
) VALUES
  ('1FPrkH0anH', 'Farizal Gufron Mubarok', '065118208', '-', '', 'Permohonan Surat Keterangan dan KHS Mahasiswa', 'Perihal : Permohonan Surat Keterangan dan KHS Mahasiswa | Nomor surat: 080/ILKOM/FMIPA-UP/IV/2026 | Tanggal masuk: 8 April 2026 | Keperluan: Permohonan Surat Keterangan dan KHS Mahasiswa | Pemohon: Farizal Gufron Mubarok (065118208) | Prodi: Ilmu Komputer', '080 Permohonan Surat Keterangan dan KHS Mahasiswa a.n. Farizal Gufron Mubarok.pdf', '/uploads/submissions/1781445372657-080-Permohonan-Surat-Keterangan-dan-KHS-Mahasiswa-a.n.-Farizal-Gufron-Mubarok.pdf', '', 'prodi', 'Surat dibaca ulang otomatis. Surat terbaca otomatis dan masuk ke prodi', '2026-06-14T13:56:12.662Z', '2026-06-14T14:07:07.045Z', '8 April 2026', 'Permohonan Surat Keterangan dan KHS Mahasiswa', '[{"name":"Farizal Gufron Mubarok","npm":"065118208","phone":""}]', '080/ILKOM/FMIPA-UP/IV/2026', '-'),
  ('CU6XZxEnq7', 'Muhammad Alif Fadillah', '065122109', '-', '', 'Permohonan Pengalihan Dana Seminar Proposal', 'Perihal : Permohonan Pengalihan Dana Seminar Proposal | Nomor surat: 074/ILKOM/FMIPA-UP/III/2026 | Tanggal masuk: 31 Maret 2026 | Keperluan: Permohonan Pengalihan Dana Seminar Proposal | Pemohon: Muhammad Alif Fadillah (065122109, +6282213033344) | Prodi: Ilmu Komputer', '074 Permohonan Pengalihan Dana Seminar Proposal a.n. M. Alif Fadillah.pdf', '/uploads/submissions/1781444943182-074-Permohonan-Pengalihan-Dana-Seminar-Proposal-a.n.-M.-Alif-Fadillah.pdf', '', 'fakultas', 'Surat dibaca ulang otomatis. Surat telah diteruskan ke fakultas', '2026-06-14T13:49:03.184Z', '2026-06-14T14:07:29.858Z', '31 Maret 2026', 'Permohonan Pengalihan Dana Seminar Proposal', '[{"name":"Muhammad Alif Fadillah","npm":"065122109","phone":"+6282213033344"}]', '074/ILKOM/FMIPA-UP/III/2026', '+6282213033344'),
  ('VYZ7z8n0P7', 'Muhamad Arif Wijaya, Rendy Alfian', '065118293, 065119093', '-', '', 'Permohonan Pengaktifan VA Pembayaran SPP SKS', 'Perihal : Permohonan Pengaktifan VA Pembayaran SPP SKS | Nomor surat: 016/ILKOM/FMIPA-UP/I/2026 | Tanggal masuk: 23 Januari 2026 | Keperluan: Permohonan Pengaktifan VA Pembayaran SPP SKS | Pemohon: Muhamad Arif Wijaya (065118293, 081283179377); Rendy Alfian (065119093, 0895330292823) | Prodi: Ilmu Komputer', '016 Permohonan Pengaktifan VA Pembayaran SPP SKS a.n. M. Arif & Rendy Alfian.pdf', '/uploads/submissions/1781444895789-016-Permohonan-Pengaktifan-VA-Pembayaran-SPP-SKS-a.n.-M.-Arif---Rendy-Alfian.pdf', '', 'prodi', 'Surat dibaca ulang otomatis. Surat terbaca otomatis dan masuk ke prodi', '2026-06-14T13:48:15.789Z', '2026-06-14T14:07:32.397Z', '23 Januari 2026', 'Permohonan Pengaktifan VA Pembayaran SPP SKS', '[{"name":"Muhamad Arif Wijaya","npm":"065118293","phone":"081283179377"},{"name":"Rendy Alfian","npm":"065119093","phone":"0895330292823"}]', '016/ILKOM/FMIPA-UP/I/2026', '081283179377')
ON DUPLICATE KEY UPDATE
  applicantName = VALUES(applicantName),
  nim = VALUES(nim),
  email = VALUES(email),
  templateId = VALUES(templateId),
  templateName = VALUES(templateName),
  notes = VALUES(notes),
  attachmentName = VALUES(attachmentName),
  attachmentUrl = VALUES(attachmentUrl),
  driveFileId = VALUES(driveFileId),
  progress = VALUES(progress),
  statusNote = VALUES(statusNote),
  createdAt = VALUES(createdAt),
  updatedAt = VALUES(updatedAt),
  entryDate = VALUES(entryDate),
  purpose = VALUES(purpose),
  applicants = VALUES(applicants),
  letterNumber = VALUES(letterNumber),
  phone = VALUES(phone);