supabase_schema.sql

-- ============================================================
-- OJT Attendance System — Full Supabase Schema
-- ISUFST Dingle Campus | CICT Department
-- ============================================================
-- Run this entire file in your Supabase SQL Editor.
-- It is safe to re-run (uses IF NOT EXISTS / OR REPLACE).
-- ============================================================


-- ────────────────────────────────────────────────────────────
-- 1. INTERNS TABLE
-- ────────────────────────────────────────────────────────────
create table if not exists public.interns (
    id              bigint generated by default as identity primary key,
    uuid            uuid default gen_random_uuid() not null unique,
    full_name       text not null,
    username        text not null unique,
    password        text not null,
    required_hours  numeric default 600 not null,
    created_at      timestamp with time zone default timezone('utc', now()) not null,
    updated_at      timestamp with time zone default timezone('utc', now()) not null
);

-- Index for fast UUID lookups (used by QR scanner)
create index if not exists interns_uuid_idx on public.interns (uuid);
-- Index for username lookups (used by login)
create index if not exists interns_username_idx on public.interns (username);

-- Row Level Security
alter table public.interns enable row level security;

drop policy if exists "Allow all operations on interns" on public.interns;
create policy "Allow all operations on interns"
on public.interns for all
to public
using (true)
with check (true);


-- ────────────────────────────────────────────────────────────
-- 2. ATTENDANCE TABLE
-- ────────────────────────────────────────────────────────────
create table if not exists public.attendance (
    id          bigint generated by default as identity primary key,
    intern_id   bigint not null references public.interns(id) on delete cascade,
    time_in     timestamp with time zone not null,
    time_out    timestamp with time zone,
    created_at  timestamp with time zone default timezone('utc', now()) not null
);

-- Index for common query patterns
create index if not exists attendance_intern_id_idx on public.attendance (intern_id);
create index if not exists attendance_time_in_idx   on public.attendance (time_in);

-- Row Level Security
alter table public.attendance enable row level security;

drop policy if exists "Allow all operations on attendance" on public.attendance;
create policy "Allow all operations on attendance"
on public.attendance for all
to public
using (true)
with check (true);


-- ────────────────────────────────────────────────────────────
-- 3. LEAVE REQUESTS TABLE
-- ────────────────────────────────────────────────────────────
create table if not exists public.leave_requests (
    id              uuid default gen_random_uuid() primary key,
    intern_id       bigint not null references public.interns(id) on delete cascade,

    -- Request details
    date_of_leave   date not null,
    reason          text not null,
    status          text not null default 'pending'
                        check (status in ('pending', 'approved', 'rejected')),

    -- Admin response
    admin_notes     text,

    -- Audit
    created_at      timestamp with time zone default timezone('utc', now()) not null,
    updated_at      timestamp with time zone default timezone('utc', now()) not null
);

-- Indexes
create index if not exists leave_requests_intern_id_idx on public.leave_requests (intern_id);
create index if not exists leave_requests_status_idx    on public.leave_requests (status);

-- Row Level Security
alter table public.leave_requests enable row level security;

drop policy if exists "Allow all operations on leave_requests" on public.leave_requests;
create policy "Allow all operations on leave_requests"
on public.leave_requests for all
to public
using (true)
with check (true);


-- ────────────────────────────────────────────────────────────
-- 4. OPTIONAL: Auto-update updated_at trigger
--    (Makes interns.updated_at and leave_requests.updated_at
--     update automatically on every row change)
-- ────────────────────────────────────────────────────────────
create or replace function public.set_updated_at()
returns trigger language plpgsql as $$
begin
    new.updated_at := timezone('utc', now());
    return new;
end;
$$;

-- Attach trigger to interns
drop trigger if exists trg_interns_updated_at on public.interns;
create trigger trg_interns_updated_at
    before update on public.interns
    for each row execute function public.set_updated_at();

-- Attach trigger to leave_requests
drop trigger if exists trg_leave_requests_updated_at on public.leave_requests;
create trigger trg_leave_requests_updated_at
    before update on public.leave_requests
    for each row execute function public.set_updated_at();


-- ────────────────────────────────────────────────────────────
-- DONE ✓
-- Tables: interns, attendance, leave_requests
-- ────────────────────────────────────────────────────────────