-- ============================================
-- Supabase Migration: Add Role & Announcements
-- Run this in Supabase SQL Editor
-- ============================================

-- 1. Add role column to public.users
ALTER TABLE public.users
ADD COLUMN IF NOT EXISTS role VARCHAR(20) DEFAULT 'user'
CHECK (role IN ('user', 'admin'));

-- Add comment
COMMENT ON COLUMN public.users.role IS 'User role: user or admin';

-- 2. Create announcements table
CREATE TABLE IF NOT EXISTS public.announcements (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_by UUID REFERENCES public.users(id) ON DELETE SET NULL,
  title VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  type VARCHAR(50) DEFAULT 'info' CHECK (type IN ('info', 'warning', 'maintenance', 'promo', 'urgent')),
  is_active BOOLEAN DEFAULT true,
  starts_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add comments
COMMENT ON TABLE public.announcements IS 'Broadcast announcements from admin to users';
COMMENT ON COLUMN public.announcements.type IS 'Announcement type: info, warning, maintenance, promo, urgent';
COMMENT ON COLUMN public.announcements.is_active IS 'Whether announcement is currently visible';
COMMENT ON COLUMN public.announcements.starts_at IS 'When announcement should start showing';
COMMENT ON COLUMN public.announcements.expires_at IS 'When announcement should stop showing (null = no expiry)';

-- 3. Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_announcements_active ON public.announcements(is_active, starts_at DESC)
WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_announcements_type ON public.announcements(type);
CREATE INDEX IF NOT EXISTS idx_announcements_dates ON public.announcements(starts_at, expires_at);

-- 4. Enable Row Level Security (RLS)
ALTER TABLE public.announcements ENABLE ROW LEVEL SECURITY;

-- 5. RLS Policies
-- Anyone can read active announcements
CREATE POLICY "Active announcements are viewable by everyone"
ON public.announcements FOR SELECT
USING (
  is_active = true
  AND (starts_at IS NULL OR starts_at <= NOW())
  AND (expires_at IS NULL OR expires_at > NOW())
);

-- Only admins can insert announcements
CREATE POLICY "Admins can insert announcements"
ON public.announcements FOR INSERT
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.users
    WHERE users.id = auth.uid()
    AND users.role = 'admin'
  )
);

-- Only admins can update announcements
CREATE POLICY "Admins can update announcements"
ON public.announcements FOR UPDATE
USING (
  EXISTS (
    SELECT 1 FROM public.users
    WHERE users.id = auth.uid()
    AND users.role = 'admin'
  )
);

-- Only admins can delete announcements
CREATE POLICY "Admins can delete announcements"
ON public.announcements FOR DELETE
USING (
  EXISTS (
    SELECT 1 FROM public.users
    WHERE users.id = auth.uid()
    AND users.role = 'admin'
  )
);

-- 6. Create function to update updated_at timestamp
CREATE OR REPLACE FUNCTION public.update_announcements_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 7. Create trigger for updated_at
DROP TRIGGER IF EXISTS announcements_updated_at ON public.announcements;
CREATE TRIGGER announcements_updated_at
BEFORE UPDATE ON public.announcements
FOR EACH ROW
EXECUTE FUNCTION public.update_announcements_updated_at();

-- 8. (Optional) Set first admin user - CHANGE THIS EMAIL
-- Uncomment and run with your admin email
UPDATE public.users
SET role = 'admin'
WHERE email = 'your-admin-email@example.com';

-- ============================================
-- Verification Queries
-- ============================================

-- Check if role column exists
SELECT column_name, data_type, column_default
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'role';

-- Check announcements table
-- SELECT * FROM public.announcements LIMIT 0;

-- Check RLS policies
-- SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
-- FROM pg_policies
-- WHERE tablename = 'announcements';

-- ============================================
-- Sample Data (for testing)
-- ============================================

-- Insert sample announcement
INSERT INTO public.announcements (created_by, title, message, type, is_active)
VALUES (
  (SELECT id FROM public.users WHERE role = 'admin' LIMIT 1),
  'Selamat Datang!',
  'Terima kasih telah menggunakan aplikasi kami. Kami akan terus meningkatkan layanan.',
  'info',
  true
);