<?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 `competitions` (
`id` int UNSIGNED NOT NULL,
  `title` varchar(150) NOT NULL,
  `semester` varchar(30) DEFAULT NULL,
  `school_year` varchar(20) DEFAULT NULL,
  `class_section_id` int DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `winners_count` int NOT NULL DEFAULT '3',
  `prize_per_point` decimal(10,2) NOT NULL DEFAULT '1.00',
  `is_published` tinyint(1) NOT NULL DEFAULT '0',
  `published_at` datetime DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `is_locked` tinyint(1) NOT NULL DEFAULT '0',
  `locked_at` datetime DEFAULT NULL,
  `locked_by` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

--
-- Table structure for table `competition_class_winners`
--


CREATE TABLE `competition_class_winners` (

`id` int UNSIGNED NOT NULL,

  `competition_id` int UNSIGNED NOT NULL,

  `class_section_id` int NOT NULL,

  `winners_override` int DEFAULT NULL,

  `created_at` datetime DEFAULT NULL,

  `updated_at` datetime DEFAULT NULL,

  `question_count` int DEFAULT NULL,

  `prize_1` decimal(10,2) DEFAULT NULL,

  `prize_2` decimal(10,2) DEFAULT NULL,

  `prize_3` decimal(10,2) DEFAULT NULL,

  `prize_4` decimal(10,2) DEFAULT NULL,

  `prize_5` decimal(10,2) DEFAULT NULL,

  `prize_6` decimal(10,2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

--
-- Table structure for table `competition_scores`
--


CREATE TABLE `competition_scores` (

`id` int UNSIGNED NOT NULL,

  `competition_id` int UNSIGNED NOT NULL,

  `student_id` int UNSIGNED NOT NULL,

  `class_section_id` int DEFAULT NULL,

  `score` decimal(6,2) NOT NULL DEFAULT '0.00',

  `notes` varchar(255) DEFAULT NULL,

  `created_at` datetime DEFAULT NULL,

  `updated_at` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

--
-- Table structure for table `competition_winners`
--


CREATE TABLE `competition_winners` (

`id` int UNSIGNED NOT NULL,

  `competition_id` int UNSIGNED NOT NULL,

  `student_id` int UNSIGNED NOT NULL,

  `class_section_id` int DEFAULT NULL,

  `rank` int NOT NULL,

  `score` decimal(6,2) NOT NULL DEFAULT '0.00',

  `prize_amount` decimal(10,2) NOT NULL DEFAULT '0.00',

  `created_at` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

--
-- Table structure for table `configuration`
--


CREATE TABLE `configuration` (

`id` int UNSIGNED NOT NULL,

  `config_key` varchar(100) NOT NULL,

  `config_value` varchar(100) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `configuration`
--

INSERT INTO `configuration` (`id`, `config_key`, `config_value`) VALUES
(1, 'date_age_reference', '2025-12-31'),
(2, 'stickerWidth', '102'),
(3, 'stickerHeight', '34'),
(4, 'pageWidth', '216'),
(5, 'pageHeight', '280'),
(6, 'leftMargin', '4'),
(7, 'topMargin', '19'),
(8, 'horizontalGap', '5'),
(9, 'verticalGap', '0'),
(10, 'enable_attendance', '0'),
(11, 'due_date', '2025-09-21'),
(12, 'max_emergency', '1'),
(13, 'pass_score', '60'),
(14, 'weeks_study', '35'),
(15, 'last_school_day', '2026-06-01'),
(16, 'max_age', '18'),
(17, 'min_age', '5'),
(18, 'trophy_score', '94'),
(19, 'max_kids', '5'),
(20, 'cleanup_timeout', '900'),
(21, 'enrollment_deadline', '2025-10-06'),
(22, 'school_year', '2025-2026'),
(23, 'Youth_fee', '200'),
(24, 'second_student_fee', '220'),
(25, 'first_student_fee', '370'),
(26, 'total_semester1_days', '14'),
(27, 'semester', 'Spring'),
(28, 'refund_deadline', '2025-12-31'),
(29, 'total_semester2_days', '13'),
(30, 'delete_email_notify', 'support@alrahmaisgl.org, melabidi@alrahmaisgl.org'),
(31, 'fall_semester_start', '2025-09-21'),
(32, 'sandbox_secret_paypal', 'Ar0Br3ZhBukQwwdSCxIcSveiu70-Ekt-qqF0nSyYNr3eRAYQKcWTTlhCAKncZiYXmBoPeo4IceV6FjT'),
(33, 'sandbox_verifylink_paypal', 'https://api-m.sandbox.paypal.com/v1/notifications/verify-webhook-signature'),
(34, 'sandbox_tokenurl_paypal', 'https://api-m.sandbox.paypal.com/v1/oauth2/token'),
(35, 'sandbox_clientid_paypal', 'AYsiOekZUvrjgx9C5c554ZeSQ4W6yd0ZX-OHE93_D0fWoa4YXrMmroEeLiAjjdCKkELH8EVZR_yGMLPS'),
(36, 'sandbox_webhookid_paypal', '0EU27043LC060650D'),
(37, 'production_verifylink_paypal', 'https://api-m.paypal.com/v1/notifications/verify-webhook-signature'),
(38, 'production_webhookid_paypal', '0H5876426L0092829'),
(39, 'production_secret_paypal', 'KKvmE-0cvwx8rEHnEcFaAvn1Rlc3cy07IGIjqGGPR8EqxABbmPodM_JrjRQq2SF2903A4q0DBjjUWai'),
(40, 'production_clientid_paypal', 'AQhCgmypaitgLgretMqNrh_7mEG5fAmwCECxCxevuLDQfsQVwECFyQOU50byzPzFrwd0cVVD8r2lujIB'),
(41, 'production_tokenurl_paypal', 'https://api-m.paypal.com/v1/oauth2/token'),
(42, 'comment_reviewer', 'administrator, principal'),
(43, 'paypal_mode', 'sandbox'),
(44, 'grade_fee', '10'),
(45, 'installment_date', '2026-03-01'),
(46, 'spring_semester_start', '2026-01-25'),
(47, 'parent_scores_released', '0'),
(48, 'last_day_of_school', '2026-05-31'),
(49, 'parent_scores_released_fall', '1'),
(50, 'parent_scores_released_spring', '0'),
(51, 'parent_report_cutoff', '09');

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

--
-- Table structure for table `contactus`
--


CREATE TABLE `contactus` (

`id` int UNSIGNED NOT NULL,

  `sender_id` int UNSIGNED NOT NULL,

  `reciever_id` int UNSIGNED NOT NULL,

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

  `message` text COLLATE utf8mb4_general_ci NOT NULL,

  `created_at` datetime DEFAULT NULL,

  `updated_at` datetime DEFAULT NULL,

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

  `school_year` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL

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

        App\Support\SqliteCompat::statement(<<<'SQL'
ALTER TABLE `competitions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `class_section_id` (`class_section_id`),
  ADD KEY `is_published` (`is_published`);
SQL
        );

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

    }

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