<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        App\Support\SqliteCompat::statement(<<<'SQL'
CREATE TABLE `whatsapp_group_memberships` (
`id` int UNSIGNED NOT NULL,
  `class_section_id` int NOT NULL,
  `school_year` varchar(25) NOT NULL,
  `semester` varchar(20) NOT NULL,
  `subject_type` enum('primary','second') NOT NULL DEFAULT 'primary',
  `subject_id` int NOT NULL COMMENT 'users.id for primary; parents.id for second',
  `is_member` tinyint(1) NOT NULL DEFAULT '0',
  `verified_by` int DEFAULT NULL,
  `verified_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
SQL
        );

        App\Support\SqliteCompat::statement(<<<'SQL'
ALTER TABLE `whatsapp_group_memberships`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_whatsapp_membership` (`class_section_id`,`school_year`,`semester`,`subject_type`,`subject_id`),
  ADD KEY `class_section_id_school_year_semester` (`class_section_id`,`school_year`,`semester`);
SQL
        );

        App\Support\SqliteCompat::statement(<<<'SQL'
ALTER TABLE `whatsapp_group_memberships`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
SQL
        );
    }

    public function down(): void
    {
        Schema::dropIfExists('whatsapp_group_memberships');
    }
};
