-- Database: `bharuchbaps_shibir`
--

START TRANSACTION;


-- ============================================================
-- Table: `roles`
-- ============================================================

CREATE TABLE `roles` (
  `id`          int(11)     NOT NULL AUTO_INCREMENT,
  `role`        varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permissions` text        COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_roles_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `roles` (`id`, `role`, `permissions`) VALUES
(1, 'admin', 'all'),
(2, 'yuvak', 'attendance,announcements');

-- ============================================================
-- Table: `mandal`  (referenced by registrar.mandal_id)
-- ============================================================

CREATE TABLE `mandal` (
  `id`          int(11)      NOT NULL AUTO_INCREMENT,
  `mandal_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code`        varchar(10)  COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_mandal_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mandal` (`id`, `mandal_name`, `code`) VALUES
(1, 'Radhakrishna', 'RK'),
(2, 'Manasnagar',   'MN');

-- ============================================================
-- Table: `yuvak_details`
--   `role`        — int FK to roles.id (was role_id, renamed to match code)
--   `mandal`      — string code like 'RK' (was mandal_id int FK, code stores string)
--   `bus_leader_1/2` — shibir_id of the user's bus leader
--   `xetra`       — area/zone grouping field
-- ============================================================

CREATE TABLE `yuvak_details` (
  `yuvak_id`     int(11)      NOT NULL AUTO_INCREMENT,
  `shibir_id`    varchar(20)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `firstname`    varchar(50)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `middlename`   varchar(50)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lastname`     varchar(50)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone_number` varchar(15)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `role`         int(11)      DEFAULT NULL,
  `mandal`       varchar(10)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bus_leader_1` varchar(20)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bus_leader_2` varchar(20)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `xetra`        varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted_at`   datetime     DEFAULT NULL,
  PRIMARY KEY (`yuvak_id`),
  KEY `idx_yuvak_shibir_id`    (`shibir_id`),
  KEY `idx_yuvak_role`         (`role`),
  KEY `idx_yuvak_bus_leader_1` (`bus_leader_1`),
  KEY `idx_yuvak_bus_leader_2` (`bus_leader_2`),
  KEY `idx_yuvak_deleted_at`   (`deleted_at`),
  CONSTRAINT `fk_yuvak_shibir_id`
    FOREIGN KEY (`shibir_id`)    REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_yuvak_role`
    FOREIGN KEY (`role`)         REFERENCES `roles` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_yuvak_bus_leader_1`
    FOREIGN KEY (`bus_leader_1`) REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_yuvak_bus_leader_2`
    FOREIGN KEY (`bus_leader_2`) REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `yuvak_details` (`yuvak_id`, `shibir_id`, `firstname`, `middlename`, `lastname`, `phone_number`, `role`, `mandal`, `bus_leader_1`, `bus_leader_2`, `xetra`, `deleted_at`) VALUES
(1, 'SH001', 'Admin',  'K.', 'Shah',  '9000000001', 1, 'RK', NULL,    NULL,    'Bharuch', NULL),
(2, 'SH002', 'Asha',   'R.', 'Patel', '9000000002', 2, 'RK', 'SH001', NULL,    'Bharuch', NULL),
(3, 'SH003', 'Raj',    'S.', 'Mehta', '9000000003', 2, 'MN', 'SH001', NULL,    'Bharuch', NULL);

-- ============================================================
-- Table: `registrar`
-- ============================================================

CREATE TABLE `registrar` (
  `shibir_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mandal_id` int(11)     DEFAULT NULL,
  PRIMARY KEY (`shibir_id`),
  KEY `idx_registrar_mandal_id` (`mandal_id`),
  CONSTRAINT `fk_registrar_shibir_id`
    FOREIGN KEY (`shibir_id`) REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_registrar_mandal_id`
    FOREIGN KEY (`mandal_id`) REFERENCES `mandal` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `registrar` (`shibir_id`, `mandal_id`) VALUES
('SH001', NULL),
('SH002', 1);

-- ============================================================
-- Table: `bus_leader_details`
--   Referenced by Admin_panel_model::check_leader
-- ============================================================

CREATE TABLE `bus_leader_details` (
  `id`          int(11)     NOT NULL AUTO_INCREMENT,
  `leader_id_1` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `leader_id_2` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bus_leader_id_1` (`leader_id_1`),
  KEY `idx_bus_leader_id_2` (`leader_id_2`),
  CONSTRAINT `fk_bus_leader_id_1`
    FOREIGN KEY (`leader_id_1`) REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_bus_leader_id_2`
    FOREIGN KEY (`leader_id_2`) REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `bus_leader_details` (`id`, `leader_id_1`, `leader_id_2`) VALUES
(1, 'SH001', NULL);

-- ============================================================
-- Table: `announcements`
--   `title` removed — never set or read by active code
-- ============================================================

CREATE TABLE `announcements` (
  `id`           int(11)     NOT NULL AUTO_INCREMENT,
  `announcement` text        COLLATE utf8mb4_unicode_ci,
  `by_user`      varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_time`    datetime    DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_announcements_date_time` (`date_time`),
  KEY `idx_announcements_by_user`   (`by_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `announcements` (`id`, `announcement`, `by_user`, `date_time`) VALUES
(1, 'Welcome to Shibir 2024!',   'SH001', '2024-04-30 09:00:00'),
(2, 'Team leaders meet at 5 PM', 'SH001', '2024-05-01 16:00:00');

-- ============================================================
-- Table: `attendance`
--   Code inserts: shibir_id, mandal (string code), attendance_for (session name).
--   Old columns karyakram_id / mandal_id / present / date_time were never
--   set by any active code path.
-- ============================================================

CREATE TABLE `attendance` (
  `id`             int(11)      NOT NULL AUTO_INCREMENT,
  `shibir_id`      varchar(20)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mandal`         varchar(10)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attendance_for` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_attendance_shibir_id`      (`shibir_id`),
  KEY `idx_attendance_mandal`         (`mandal`),
  KEY `idx_attendance_attendance_for` (`attendance_for`),
  CONSTRAINT `fk_attendance_shibir_id`
    FOREIGN KEY (`shibir_id`) REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `attendance` (`id`, `shibir_id`, `mandal`, `attendance_for`) VALUES
(1, 'SH002', 'RK', 'shibir_session_2'),
(2, 'SH003', 'MN', 'shibir_session_2');

-- ============================================================
-- Table: `qr_code`
--   User_model::get_qr: SELECT api WHERE qr_code = shibir_id
--   (replaces the old unused `data` table)
-- ============================================================

CREATE TABLE `qr_code` (
  `id`      int(11)      NOT NULL AUTO_INCREMENT,
  `qr_code` varchar(20)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `api`     varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_qr_code` (`qr_code`),
  CONSTRAINT `fk_qr_code_shibir_id`
    FOREIGN KEY (`qr_code`) REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `qr_code` (`id`, `qr_code`, `api`) VALUES
(1, 'SH001', 'https://api.qrserver.com/v1/create-qr-code/?size=200x200&data=SH001'),
(2, 'SH002', 'https://api.qrserver.com/v1/create-qr-code/?size=200x200&data=SH002'),
(3, 'SH003', 'https://api.qrserver.com/v1/create-qr-code/?size=200x200&data=SH003');

-- ============================================================
-- Table: `sadgun`
--   Sadgun_model: insert/get; Sadgun.php adds created_at before insert
-- ============================================================

CREATE TABLE `sadgun` (
  `id`         int(11)     NOT NULL AUTO_INCREMENT,
  `shibir_id`  varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sadgun`     text        COLLATE utf8mb4_unicode_ci,
  `created_at` datetime    DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sadgun_shibir_id`  (`shibir_id`),
  KEY `idx_sadgun_created_at` (`created_at`),
  CONSTRAINT `fk_sadgun_shibir_id`
    FOREIGN KEY (`shibir_id`) REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `sadgun` (`id`, `shibir_id`, `sadgun`, `created_at`) VALUES
(1, 'SH002', 'Helped set up chairs before the morning session without being asked.', '2024-05-01 07:45:00'),
(2, 'SH003', 'Volunteered to clean up the dining area after lunch.', '2024-05-01 13:15:00');

-- ============================================================
-- Table: `lost_and_found`
--   Lost_and_found_model: insert/get; Lost_and_found.php adds created_at
-- ============================================================

CREATE TABLE `lost_and_found` (
  `id`          int(11)     NOT NULL AUTO_INCREMENT,
  `shibir_id`   varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text        COLLATE utf8mb4_unicode_ci,
  `created_at`  datetime    DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_laf_shibir_id`  (`shibir_id`),
  KEY `idx_laf_created_at` (`created_at`),
  CONSTRAINT `fk_laf_shibir_id`
    FOREIGN KEY (`shibir_id`) REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `lost_and_found` (`id`, `shibir_id`, `description`, `created_at`) VALUES
(1, 'SH003', 'Found a blue water bottle near the dining hall — has initials RP on it.', '2024-05-01 12:30:00');

-- ============================================================
-- Table: `quiz_place`
-- ============================================================

CREATE TABLE `quiz_place` (
  `id`    int(11)      NOT NULL AUTO_INCREMENT,
  `place` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_quiz_place_place` (`place`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `quiz_place` (`id`, `place`) VALUES
(1,  'bharuch'),
(2,  'dhule'),
(3,  'nashik'),
(4,  'shibir_session_1'),
(5,  'shibir_session_2'),
(6,  'shibir_session_3'),
(7,  'nashik_darshan'),
(8,  'trambakeshwar'),
(9,  'pune'),
(10, 'imagica'),
(11, 'shibir_session_4');

-- ============================================================
-- Table: `quiz`
-- ============================================================

CREATE TABLE `quiz` (
  `id`             int(11)      NOT NULL AUTO_INCREMENT,
  `quiz`           int(11)      DEFAULT NULL,
  `question`       text         COLLATE utf8mb4_unicode_ci NOT NULL,
  `option_1`       varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `option_2`       varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `option_3`       varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `option_4`       varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `correct_answer` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_quiz_quiz_id` (`quiz`),
  CONSTRAINT `fk_quiz_place_id`
    FOREIGN KEY (`quiz`) REFERENCES `quiz_place` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `quiz` (`id`, `quiz`, `question`, `option_1`, `option_2`, `option_3`, `option_4`, `correct_answer`) VALUES
-- Bharuch quiz (quiz_place id=1)
(1,  1, 'Bharuch is situated on the banks of which river?',        'Sabarmati', 'Narmada',      'Tapi',       'Mahi',           'Narmada'),
(2,  1, 'What is the ancient name of Bharuch?',                     'Barygaza',  'Bharatpur',    'Bhrigupur',  'Bharatgarh',     'Barygaza'),
(3,  1, 'Bharuch is located in which state of India?',              'Rajasthan', 'Maharashtra',  'Gujarat',    'Madhya Pradesh', 'Gujarat'),
(4,  1, 'Which famous bridge connects Bharuch across the Narmada?', 'Golden Gate','Golden Bridge','Silver Bridge','Iron Bridge', 'Golden Bridge'),
(5,  1, 'Bharuch is known for which major industry?',               'IT',        'Petrochemical','Textiles',   'Agriculture',    'Petrochemical'),
-- Shibir Session 1 quiz (quiz_place id=4)
(6,  4, 'Who is the founder of BAPS Swaminarayan Sanstha?',         'Swaminarayan Bhagwan','Gunatitanand Swami','Bhagatji Maharaj','Shastriji Maharaj','Shastriji Maharaj'),
(7,  4, 'What does BAPS stand for?',                                'Bochasanwasi Akshar Purushottam Sanstha','Bharuch Akshar Purushottam Sabha','Bochasanwasi Akshar Purushottam Sabha','Bharatiya Akshar Purushottam Sanstha','Bochasanwasi Akshar Purushottam Sanstha'),
(8,  4, 'In which year was BAPS established?',                      '1900','1907','1920','1947', '1907'),
(9,  4, 'What is the spiritual significance of a Shibir?',          'Fundraising event','Spiritual retreat and learning','Sports competition','Cultural festival','Spiritual retreat and learning'),
(10, 4, 'Which scripture is central to Swaminarayan teachings?',    'Bhagavad Gita','Vachanamrut','Ramayan','Upanishads',    'Vachanamrut');

-- ============================================================
-- Table: `quiz_options`
-- ============================================================

CREATE TABLE `quiz_options` (
  `id`              int(11)      NOT NULL AUTO_INCREMENT,
  `question`        int(11)      DEFAULT NULL,
  `shibir_id`       varchar(20)  COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quiz_id`         int(11)      DEFAULT NULL,
  `selected_option` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_quiz_options_question`  (`question`),
  KEY `idx_quiz_options_shibir_id` (`shibir_id`),
  KEY `idx_quiz_options_quiz_id`   (`quiz_id`),
  CONSTRAINT `fk_quiz_options_question`
    FOREIGN KEY (`question`)  REFERENCES `quiz` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_quiz_options_shibir_id`
    FOREIGN KEY (`shibir_id`) REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_quiz_options_quiz_id`
    FOREIGN KEY (`quiz_id`)   REFERENCES `quiz_place` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SH002 answered bharuch quiz (questions 1-5), quiz_id=1
-- SH003 answered shibir_session_1 quiz (questions 6-10), quiz_id=4
INSERT INTO `quiz_options` (`id`, `question`, `shibir_id`, `quiz_id`, `selected_option`) VALUES
(1,  1,  'SH002', 1, 'Narmada'),
(2,  2,  'SH002', 1, 'Barygaza'),
(3,  3,  'SH002', 1, 'Gujarat'),
(4,  4,  'SH002', 1, 'Iron Bridge'),
(5,  5,  'SH002', 1, 'Petrochemical'),
(6,  6,  'SH003', 4, 'Shastriji Maharaj'),
(7,  7,  'SH003', 4, 'Bochasanwasi Akshar Purushottam Sanstha'),
(8,  8,  'SH003', 4, '1907'),
(9,  9,  'SH003', 4, 'Spiritual retreat and learning'),
(10, 10, 'SH003', 4, 'Vachanamrut');

-- ============================================================
-- Table: `quiz_results`
-- ============================================================

CREATE TABLE `quiz_results` (
  `id`        int(11)     NOT NULL AUTO_INCREMENT,
  `shibir_id` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quiz_id`   int(11)     DEFAULT NULL,
  `marks`     int(11)     DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_quiz_results_shibir_id` (`shibir_id`),
  KEY `idx_quiz_results_quiz_id`   (`quiz_id`),
  CONSTRAINT `fk_quiz_results_shibir_id`
    FOREIGN KEY (`shibir_id`) REFERENCES `shibir_users` (`shibir_id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_quiz_results_quiz_id`
    FOREIGN KEY (`quiz_id`)   REFERENCES `quiz_place` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SH002: bharuch — 4/5 (got question 4 wrong)
-- SH003: shibir_session_1 — 5/5
INSERT INTO `quiz_results` (`id`, `shibir_id`, `quiz_id`, `marks`) VALUES
(1, 'SH002', 1, 4),
(2, 'SH003', 4, 5);

-- ============================================================

COMMIT;

