<?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 `families` (
`id` int UNSIGNED NOT NULL,
  `family_code` varchar(64) NOT NULL,
  `household_name` varchar(128) DEFAULT NULL,
  `address_line1` varchar(128) DEFAULT NULL,
  `address_line2` varchar(128) DEFAULT NULL,
  `city` varchar(64) DEFAULT NULL,
  `state` varchar(32) DEFAULT NULL,
  `postal_code` varchar(16) DEFAULT NULL,
  `country` varchar(64) DEFAULT NULL,
  `primary_phone` varchar(32) DEFAULT NULL,
  `preferred_lang` varchar(16) DEFAULT NULL,
  `preferred_contact_method` varchar(32) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `family_comm_prefs`
--


CREATE TABLE `family_comm_prefs` (

`id` int UNSIGNED NOT NULL,

  `family_id` int UNSIGNED NOT NULL,

  `category` varchar(32) NOT NULL,

  `via_email` tinyint(1) NOT NULL DEFAULT '1',

  `via_sms` tinyint(1) NOT NULL DEFAULT '0',

  `cc_all_guardians` tinyint(1) NOT NULL DEFAULT '1',

  `created_at` datetime DEFAULT NULL,

  `updated_at` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `family_guardians`
--


CREATE TABLE `family_guardians` (

`id` int UNSIGNED NOT NULL,

  `family_id` int UNSIGNED NOT NULL,

  `user_id` int UNSIGNED NOT NULL,

  `relation` varchar(32) NOT NULL DEFAULT 'guardian',

  `is_primary` tinyint(1) NOT NULL DEFAULT '0',

  `receive_emails` tinyint(1) NOT NULL DEFAULT '1',

  `receive_sms` tinyint(1) NOT NULL DEFAULT '0',

  `custody_notes` text,

  `created_at` datetime DEFAULT NULL,

  `updated_at` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `family_students`
--


CREATE TABLE `family_students` (

`id` int UNSIGNED NOT NULL,

  `family_id` int UNSIGNED NOT NULL,

  `student_id` int UNSIGNED NOT NULL,

  `is_primary_home` tinyint(1) NOT NULL DEFAULT '0',

  `notes` text,

  `created_at` datetime DEFAULT NULL,

  `updated_at` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `final_exam`
--


CREATE TABLE `final_exam` (

`id` int UNSIGNED NOT NULL,

  `student_id` int NOT NULL,

  `school_id` char(36) NOT NULL,

  `class_section_id` int DEFAULT NULL,

  `updated_by` int NOT NULL,

  `score` float DEFAULT NULL,

  `semester` varchar(25) NOT NULL DEFAULT 'Spring',

  `school_year` varchar(9) DEFAULT NULL,

  `created_at` datetime DEFAULT NULL,

  `updated_at` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `final_score`
--


CREATE TABLE `final_score` (

`id` int UNSIGNED NOT NULL,

  `student_id` int NOT NULL,

  `school_id` char(36) NOT NULL,

  `class_section_id` int DEFAULT NULL,

  `updated_by` int NOT NULL,

  `score` float NOT NULL,

  `score_letter` varchar(9) DEFAULT NULL,

  `comment` text,

  `school_year` varchar(9) NOT NULL,

  `created_at` datetime DEFAULT NULL,

  `updated_at` datetime DEFAULT NULL,

  `semester` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `flag`
--


CREATE TABLE `flag` (

`id` int UNSIGNED NOT NULL,

  `student_id` int NOT NULL,

  `student_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,

  `grade` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,

  `flag` enum('payment','attendance','grade','behavior','talking_off_task','calling_out','minor_disruption','minor_dress_code','forgetting_materials','repeated_defiance','disrespectful_tone','minor_profanity','repeated_tardiness','device_misuse','minor_conflict','bullying_harassment','fighting_aggression','threats','theft','vandalism','sexual_harassment','serious_cyber_incident','contraband','weapons_drugs','other') COLLATE utf8mb4_general_ci NOT NULL,

  `flag_datetime` datetime NOT NULL,

  `flag_state` enum('Closed','Canceled') COLLATE utf8mb4_general_ci NOT NULL,

  `updated_by_open` int DEFAULT NULL,

  `open_description` text COLLATE utf8mb4_general_ci,

  `updated_by_closed` int DEFAULT NULL,

  `close_description` text COLLATE utf8mb4_general_ci,

  `action_taken` text COLLATE utf8mb4_general_ci,

  `updated_by_canceled` int DEFAULT NULL,

  `cancel_description` text COLLATE utf8mb4_general_ci,

  `semester` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,

  `school_year` varchar(9) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,

  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
SQL
        );

        App\Support\SqliteCompat::statement(<<<'SQL'
ALTER TABLE `families`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `family_code` (`family_code`);
SQL
        );

        App\Support\SqliteCompat::statement(<<<'SQL'
ALTER TABLE `families`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=146;
SQL
        );

    }

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