-- -- ============================================
-- -- DROP EXISTING TABLES
-- -- ============================================

-- DROP TABLE IF EXISTS blood_groups;
-- DROP TABLE IF EXISTS batches;
-- DROP TABLE IF EXISTS departments;
-- DROP TABLE IF EXISTS passing_years;


-- ============================================
-- CREATE TABLES
-- ============================================

CREATE TABLE blood_groups (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE batches (
    id CHAR(36) PRIMARY KEY,
    batch INT NOT NULL UNIQUE
);

CREATE TABLE departments (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE passing_years (
    id CHAR(36) PRIMARY KEY,
    year INT NOT NULL UNIQUE
);


-- ============================================
-- BLOOD GROUPS
-- ============================================

INSERT INTO blood_groups (id, name)
VALUES
    (UUID(), 'A+'),
    (UUID(), 'A-'),
    (UUID(), 'B+'),
    (UUID(), 'B-'),
    (UUID(), 'AB+'),
    (UUID(), 'AB-'),
    (UUID(), 'O+'),
    (UUID(), 'O-');


-- ============================================
-- DEPARTMENTS
-- ============================================

INSERT INTO departments (id, name)
VALUES
    (UUID(), 'Computer Science & Technology'),
    (UUID(), 'Automobile Technology'),
    (UUID(), 'Electrical'),
    (UUID(), 'Mechanical'),
    (UUID(), 'Construction'),
    (UUID(), 'Civil-Wood');


-- ============================================
-- BATCHES
-- 1 - 63
-- ============================================

INSERT INTO batches (id, batch)
WITH RECURSIVE batch_numbers AS (
    SELECT 1 AS batch

    UNION ALL

    SELECT batch + 1
    FROM batch_numbers
    WHERE batch < 63
)
SELECT
    UUID(),
    batch
FROM batch_numbers;


-- ============================================
-- PASSING YEARS
-- 1963 - 2026
-- ============================================

INSERT INTO passing_years (id, year)
WITH RECURSIVE years AS (
    SELECT 1963 AS year

    UNION ALL

    SELECT year + 1
    FROM years
    WHERE year < 2026
)
SELECT
    UUID(),
    year
FROM years;