-- Supabase SQL Schema for NowJoan Admin Panel
-- Run this in your Supabase SQL Editor

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

-- Sliders table
CREATE TABLE IF NOT EXISTS sliders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  title_en TEXT NOT NULL,
  subtitle TEXT DEFAULT '',
  subtitle_en TEXT DEFAULT '',
  description TEXT NOT NULL,
  description_en TEXT NOT NULL,
  image TEXT NOT NULL,
  cta TEXT DEFAULT '',
  cta_en TEXT DEFAULT '',
  cta_link TEXT DEFAULT '',
  sort_order INTEGER DEFAULT 0,
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Leaders table
CREATE TABLE IF NOT EXISTS leaders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  name_en TEXT NOT NULL,
  position TEXT NOT NULL,
  position_en TEXT NOT NULL,
  location TEXT,
  phone TEXT,
  email TEXT,
  image TEXT NOT NULL,
  bio TEXT,
  bio_en TEXT,
  category TEXT NOT NULL CHECK (category IN ('central', 'divisional', 'district')),
  sort_order INTEGER DEFAULT 0,
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Programs table
CREATE TABLE IF NOT EXISTS programs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  icon TEXT NOT NULL,
  title TEXT NOT NULL,
  title_en TEXT NOT NULL,
  subtitle TEXT DEFAULT '',
  subtitle_en TEXT DEFAULT '',
  points TEXT[] DEFAULT '{}',
  points_en TEXT[] DEFAULT '{}',
  color TEXT NOT NULL CHECK (color IN ('primary-green', 'primary-red')),
  sort_order INTEGER DEFAULT 0,
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Achievements table
CREATE TABLE IF NOT EXISTS achievements (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  number TEXT NOT NULL,
  number_en TEXT NOT NULL,
  label TEXT NOT NULL,
  label_en TEXT NOT NULL,
  icon TEXT NOT NULL,
  sort_order INTEGER DEFAULT 0,
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Humanitarian Works table
CREATE TABLE IF NOT EXISTS humanitarian_works (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  title_en TEXT NOT NULL,
  description TEXT NOT NULL,
  description_en TEXT NOT NULL,
  image TEXT NOT NULL,
  sort_order INTEGER DEFAULT 0,
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Contributions table
CREATE TABLE IF NOT EXISTS contributions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('one-time', 'monthly')),
  payment_method TEXT NOT NULL,
  transaction_id TEXT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'failed')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Memberships table
CREATE TABLE IF NOT EXISTS memberships (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  full_name TEXT NOT NULL,
  full_name_en TEXT NOT NULL,
  father_name TEXT NOT NULL,
  mother_name TEXT NOT NULL,
  date_of_birth DATE NOT NULL,
  nid_number TEXT NOT NULL,
  phone TEXT NOT NULL,
  email TEXT NOT NULL,
  address TEXT NOT NULL,
  district TEXT NOT NULL,
  upazila TEXT NOT NULL,
  occupation TEXT NOT NULL,
  education TEXT NOT NULL,
  membership_type TEXT NOT NULL CHECK (membership_type IN ('general', 'active', 'volunteer')),
  interests TEXT[] DEFAULT '{}',
  photo_url TEXT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Settings table (for manifesto URL, program plan URL, etc.)
CREATE TABLE IF NOT EXISTS settings (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  key TEXT NOT NULL UNIQUE,
  value TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Admin Users table
CREATE TABLE IF NOT EXISTS admin_users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  username TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'editor' CHECK (role IN ('admin', 'editor')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_sliders_active ON sliders(active);
CREATE INDEX IF NOT EXISTS idx_sliders_sort ON sliders(sort_order);
CREATE INDEX IF NOT EXISTS idx_leaders_category ON leaders(category);
CREATE INDEX IF NOT EXISTS idx_leaders_active ON leaders(active);
CREATE INDEX IF NOT EXISTS idx_programs_active ON programs(active);
CREATE INDEX IF NOT EXISTS idx_achievements_active ON achievements(active);
CREATE INDEX IF NOT EXISTS idx_humanitarian_active ON humanitarian_works(active);
CREATE INDEX IF NOT EXISTS idx_contributions_status ON contributions(status);
CREATE INDEX IF NOT EXISTS idx_contributions_date ON contributions(created_at);
CREATE INDEX IF NOT EXISTS idx_memberships_status ON memberships(status);
CREATE INDEX IF NOT EXISTS idx_memberships_type ON memberships(membership_type);
CREATE INDEX IF NOT EXISTS idx_memberships_date ON memberships(created_at);
CREATE INDEX IF NOT EXISTS idx_settings_key ON settings(key);

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply updated_at triggers
CREATE TRIGGER update_sliders_updated_at BEFORE UPDATE ON sliders FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_leaders_updated_at BEFORE UPDATE ON leaders FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_programs_updated_at BEFORE UPDATE ON programs FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_achievements_updated_at BEFORE UPDATE ON achievements FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_humanitarian_updated_at BEFORE UPDATE ON humanitarian_works FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_settings_updated_at BEFORE UPDATE ON settings FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Insert default admin user (password: admin123)
-- Note: In production, use proper password hashing like bcrypt
INSERT INTO admin_users (username, password_hash, role) 
VALUES ('admin', 'admin123', 'admin')
ON CONFLICT (username) DO NOTHING;

-- Insert default achievements
INSERT INTO achievements (number, number_en, label, label_en, icon, sort_order) VALUES
('50,000+', '50,000+', 'সক্রিয় সদস্য', 'Active Members', 'Users', 1),
('120+', '120+', 'জেলা কমিটি', 'District Committees', 'TrendingUp', 2),
('500+', '500+', 'সামাজিক কর্মসূচি', 'Social Programs', 'HandHeart', 3),
('২ বছর', '2 years', 'প্রতিষ্ঠার পর থেকে', 'Years Since Founding', 'Calendar', 4)
ON CONFLICT DO NOTHING;

-- Insert default sliders
INSERT INTO sliders (title, title_en, subtitle, subtitle_en, description, description_en, image, cta, cta_en, cta_link, sort_order) VALUES
('স্বনির্ভর বাংলাদেশ', 'Self-Reliant Bangladesh', 'স্বনির্ভর বাংলাদেশ', 'Self-Reliant Bangladesh', 'নিজস্ব সম্পদ ও প্রযুক্তিতে স্বাবলম্বী জাতি গঠন - কৃষি, শিল্প ও প্রযুক্তিতে আত্মনির্ভরশীল বাংলাদেশ', 'Building a self-sufficient nation with our own resources and technology - A Bangladesh self-reliant in agriculture, industry, and technology', '/assets/vercel-images/slider-self-reliant-bangladesh.jpg', 'আরও জানুন', 'Learn More', '#vision', 1),
('সুশাসন প্রতিষ্ঠা', 'Establishing Good Governance', 'সুশাসন', 'Good Governance', 'স্বচ্ছতা, জবাবদিহিতা ও ন্যায়বিচার নিশ্চিতকরণ - প্রতিটি নাগরিকের অধিকার সংরক্ষণে প্রতিশ্রুতিবদ্ধ', 'Ensuring transparency, accountability, and justice - Committed to protecting every citizen''s rights', '/assets/vercel-images/slider-good-governance.jpg', 'কর্মসূচি দেখুন', 'View Programs', '#programs', 2),
('সার্বভৌমত্ব রক্ষা', 'Protecting Sovereignty', 'সার্বভৌমত্ব', 'Protecting Sovereignty', 'জাতীয় স্বাধীনতা ও মর্যাদা সমুন্নত রাখা - বহিঃশক্তির হস্তক্ষেপমুক্ত স্বাধীন বাংলাদেশ', 'Upholding national independence and dignity - An independent Bangladesh free from external interference', '/assets/vercel-images/slider-sovereignty.jpg', 'দৃষ্টিভঙ্গি দেখুন', 'View Vision', '#vision', 3),
('আমাদের সাথে যোগ দিন', 'Join Us', 'আমাদের সাথে যোগ দিন', 'Join Us', 'একসাথে গড়ি স্বনির্ভর, সুশাসিত ও সার্বভৌম বাংলাদেশ - আপনার কণ্ঠস্বর, আপনার শক্তি, আপনার নওজোয়ান', 'Together we build a self-reliant, well-governed, and sovereign Bangladesh - Your voice, your strength, your NowJoan', '/assets/vercel-images/slider-join-nowjoan.jpg', 'সদস্য হন', 'Become a Member', '/join', 4)
ON CONFLICT DO NOTHING;

-- Enable Row Level Security (RLS) for all tables
ALTER TABLE sliders ENABLE ROW LEVEL SECURITY;
ALTER TABLE leaders ENABLE ROW LEVEL SECURITY;
ALTER TABLE programs ENABLE ROW LEVEL SECURITY;
ALTER TABLE achievements ENABLE ROW LEVEL SECURITY;
ALTER TABLE humanitarian_works ENABLE ROW LEVEL SECURITY;
ALTER TABLE contributions ENABLE ROW LEVEL SECURITY;
ALTER TABLE memberships ENABLE ROW LEVEL SECURITY;
ALTER TABLE settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE admin_users ENABLE ROW LEVEL SECURITY;

-- Create policies for public read access (for frontend)
CREATE POLICY "Public read sliders" ON sliders FOR SELECT USING (active = true);
CREATE POLICY "Public read leaders" ON leaders FOR SELECT USING (active = true);
CREATE POLICY "Public read programs" ON programs FOR SELECT USING (active = true);
CREATE POLICY "Public read achievements" ON achievements FOR SELECT USING (active = true);
CREATE POLICY "Public read humanitarian" ON humanitarian_works FOR SELECT USING (active = true);
CREATE POLICY "Public read settings" ON settings FOR SELECT USING (true);

-- Create policies for authenticated admin access (full CRUD)
-- Note: You may want to use Supabase Auth for proper authentication
-- For now, we'll allow all operations (adjust based on your auth setup)
CREATE POLICY "Admin all sliders" ON sliders FOR ALL USING (true);
CREATE POLICY "Admin all leaders" ON leaders FOR ALL USING (true);
CREATE POLICY "Admin all programs" ON programs FOR ALL USING (true);
CREATE POLICY "Admin all achievements" ON achievements FOR ALL USING (true);
CREATE POLICY "Admin all humanitarian" ON humanitarian_works FOR ALL USING (true);
CREATE POLICY "Admin all contributions" ON contributions FOR ALL USING (true);
CREATE POLICY "Admin all memberships" ON memberships FOR ALL USING (true);
CREATE POLICY "Admin all settings" ON settings FOR ALL USING (true);
CREATE POLICY "Admin all admin_users" ON admin_users FOR ALL USING (true);

-- Allow public to insert memberships and contributions (for form submissions)
CREATE POLICY "Public insert memberships" ON memberships FOR INSERT WITH CHECK (true);
CREATE POLICY "Public insert contributions" ON contributions FOR INSERT WITH CHECK (true);

-- ============================================
-- STORAGE BUCKET SETUP (Run in Supabase Dashboard -> Storage)
-- ============================================
-- 
-- Create a bucket named "images" with the following settings:
-- 1. Go to Supabase Dashboard -> Storage -> New Bucket
-- 2. Name: images
-- 3. Public bucket: Yes (for public access to images)
-- 4. File size limit: 5MB (5242880 bytes)
-- 5. Allowed MIME types: image/jpeg, image/png, image/webp, image/gif
--
-- This bucket will store:
-- - Slider images
-- - Leader photos  
-- - Member photos (in member-photos/ subfolder)
-- - Other site images
--
-- After creating the bucket, add these policies in the Storage section:
--
-- Policy 1: Public read access
-- Name: Public Access
-- Allowed operation: SELECT
-- Policy definition: true
--
-- Policy 2: Authenticated upload (or use anon key for admin panel)
-- Name: Admin Upload
-- Allowed operation: INSERT
-- Policy definition: true
--
-- Policy 3: Admin delete
-- Name: Admin Delete
-- Allowed operation: DELETE
-- Policy definition: true
--
-- ALTERNATIVE: If you prefer separate buckets, create a "memberships" bucket
-- with the same settings for member photos only.
--
-- Note: For production, you should restrict upload/delete to authenticated admin users
-- ============================================