schema.sql

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Books table
CREATE TABLE books (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  code TEXT UNIQUE NOT NULL,
  author TEXT,
  publisher TEXT,
  publication_year INTEGER,
  category TEXT,
  stock INTEGER DEFAULT 0,
  price_per_day DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Members table
CREATE TABLE members (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  code TEXT UNIQUE NOT NULL,
  phone TEXT,
  email TEXT,
  status TEXT DEFAULT 'active',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Transactions table
CREATE TABLE transactions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  book_id UUID REFERENCES books(id) ON DELETE CASCADE,
  member_id UUID REFERENCES members(id) ON DELETE CASCADE,
  rental_date DATE DEFAULT CURRENT_DATE,
  return_date DATE,
  due_date DATE NOT NULL,
  status TEXT DEFAULT 'borrowed', -- 'borrowed', 'returned', 'overdue'
  total_price DECIMAL(10, 2),
  fine_amount DECIMAL(10, 2) DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Staff/Users table (Supabase handles Auth, but we can have a profile table)
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name TEXT,
  role TEXT DEFAULT 'staff', -- 'admin', 'staff'
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Row Level Security (RLS)
ALTER TABLE books ENABLE ROW LEVEL SECURITY;
ALTER TABLE members ENABLE ROW LEVEL SECURITY;
ALTER TABLE transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Basic Policies (Adjust based on RBAC needs)
CREATE POLICY "Public read access for books" ON books FOR SELECT USING (true);
CREATE POLICY "Admin/Staff full access for books" ON books FOR ALL USING (auth.role() = 'authenticated');

CREATE POLICY "Admin/Staff full access for members" ON members FOR ALL USING (auth.role() = 'authenticated');
CREATE POLICY "Admin/Staff full access for transactions" ON transactions FOR ALL USING (auth.role() = 'authenticated');
CREATE POLICY "Authenticated users can see their own profile" ON profiles FOR SELECT USING (auth.uid() = id);

-- Functions for stock management
CREATE OR REPLACE FUNCTION decrement_stock(book_id UUID)
RETURNS void AS $$
BEGIN
  UPDATE books
  SET stock = stock - 1
  WHERE id = book_id AND stock > 0;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION increment_stock(book_id UUID)
RETURNS void AS $$
BEGIN
  UPDATE books
  SET stock = stock + 1
  WHERE id = book_id;
END;
$$ LANGUAGE plpgsql;