-- PROFILES
CREATE TABLE public.profiles (
  id UUID NOT NULL PRIMARY KEY,
  full_name TEXT,
  business_name TEXT,
  sector TEXT,
  business_size TEXT,
  county TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.profiles TO authenticated;
GRANT ALL ON public.profiles TO service_role;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own profile" ON public.profiles FOR ALL TO authenticated USING (auth.uid() = id) WITH CHECK (auth.uid() = id);

CREATE OR REPLACE FUNCTION public.set_updated_at() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql SET search_path = public;
CREATE TRIGGER profiles_updated_at BEFORE UPDATE ON public.profiles FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

CREATE OR REPLACE FUNCTION public.handle_new_user() RETURNS TRIGGER
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name)
  VALUES (NEW.id, NEW.raw_user_meta_data ->> 'full_name')
  ON CONFLICT (id) DO NOTHING;
  RETURN NEW;
END;
$$;
CREATE TRIGGER on_auth_user_created AFTER INSERT ON auth.users FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- CATEGORIES
CREATE TABLE public.assessment_categories (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0
);
GRANT SELECT ON public.assessment_categories TO anon, authenticated;
GRANT ALL ON public.assessment_categories TO service_role;
ALTER TABLE public.assessment_categories ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Categories are public" ON public.assessment_categories FOR SELECT TO anon, authenticated USING (true);

-- QUESTIONS
CREATE TABLE public.assessment_questions (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  category_id UUID NOT NULL REFERENCES public.assessment_categories(id) ON DELETE CASCADE,
  prompt TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0
);
GRANT SELECT ON public.assessment_questions TO anon, authenticated;
GRANT ALL ON public.assessment_questions TO service_role;
ALTER TABLE public.assessment_questions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Questions are public" ON public.assessment_questions FOR SELECT TO anon, authenticated USING (true);

-- RESPONSES
CREATE TABLE public.assessment_responses (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  question_id UUID NOT NULL REFERENCES public.assessment_questions(id) ON DELETE CASCADE,
  score INT NOT NULL CHECK (score BETWEEN 1 AND 5),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, question_id)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.assessment_responses TO authenticated;
GRANT ALL ON public.assessment_responses TO service_role;
ALTER TABLE public.assessment_responses ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own responses" ON public.assessment_responses FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE TRIGGER responses_updated_at BEFORE UPDATE ON public.assessment_responses FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- RESULTS
CREATE TABLE public.assessment_results (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  overall_score NUMERIC(5,2) NOT NULL,
  level TEXT NOT NULL,
  category_scores JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.assessment_results TO authenticated;
GRANT ALL ON public.assessment_results TO service_role;
ALTER TABLE public.assessment_results ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own results" ON public.assessment_results FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

-- COURSES
CREATE TABLE public.courses (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  summary TEXT NOT NULL,
  description TEXT NOT NULL,
  category_slug TEXT NOT NULL,
  level TEXT NOT NULL DEFAULT 'Beginner',
  duration_minutes INT NOT NULL DEFAULT 45,
  is_premium BOOLEAN NOT NULL DEFAULT false,
  lessons JSONB NOT NULL DEFAULT '[]'::jsonb,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT ON public.courses TO anon, authenticated;
GRANT ALL ON public.courses TO service_role;
ALTER TABLE public.courses ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Courses are public" ON public.courses FOR SELECT TO anon, authenticated USING (true);

-- ENROLLMENTS
CREATE TABLE public.enrollments (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  course_id UUID NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, course_id)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.enrollments TO authenticated;
GRANT ALL ON public.enrollments TO service_role;
ALTER TABLE public.enrollments ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own enrollments" ON public.enrollments FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

-- COURSE PROGRESS
CREATE TABLE public.course_progress (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  course_id UUID NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
  lesson_index INT NOT NULL,
  completed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, course_id, lesson_index)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.course_progress TO authenticated;
GRANT ALL ON public.course_progress TO service_role;
ALTER TABLE public.course_progress ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own progress" ON public.course_progress FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

-- SEED CATEGORIES
INSERT INTO public.assessment_categories (slug, name, description, sort_order) VALUES
('strategy','Business Strategy & Vision','Clarity of direction, goals and positioning in your market.',1),
('finance','Financial Management','Bookkeeping, cash flow, pricing and financial controls.',2),
('marketing','Marketing & Sales','How you attract, convert and retain customers.',3),
('operations','Operations & Delivery','Processes, suppliers and consistent delivery quality.',4),
('digital','Digital Transformation','Use of digital tools, payments and online presence.',5),
('people','People & Leadership','Hiring, teams, culture and delegation.',6),
('customer','Customer Experience','Service standards, feedback loops and loyalty.',7),
('compliance','Compliance & Governance','Registration, licences, tax and record-keeping.',8),
('innovation','Innovation & Products','Product development and adapting to market change.',9),
('funding','Funding & Investment Readiness','Access to capital and readiness for investors or lenders.',10),
('risk','Risk & Resilience','Managing shocks, insurance and business continuity.',11),
('sustainability','Sustainability & Impact','Environmental and community impact of your business.',12);

-- SEED QUESTIONS
INSERT INTO public.assessment_questions (category_id, prompt, sort_order)
SELECT c.id, q.prompt, q.sort_order FROM (VALUES
('strategy','We have a written business plan that we review at least twice a year.',1),
('strategy','We can clearly describe who our ideal customer is and why they choose us.',2),
('strategy','We set measurable growth targets and track progress against them.',3),
('finance','We keep separate business and personal accounts and records.',1),
('finance','We know our monthly cash position and can forecast the next three months.',2),
('finance','Our pricing is based on real cost and margin calculations.',3),
('marketing','We have a consistent way of generating new leads every month.',1),
('marketing','We track where our customers come from and what converts best.',2),
('marketing','We follow up with past customers to drive repeat business.',3),
('operations','Our core processes are documented so anyone can follow them.',1),
('operations','We have reliable suppliers and manage stock or capacity well.',2),
('operations','We measure delivery quality and fix recurring problems.',3),
('digital','We have an active online presence customers can find and trust.',1),
('digital','We accept digital payments and reconcile them properly.',2),
('digital','We use digital tools for records, sales or customer communication.',3),
('people','Our roles and responsibilities are clearly defined.',1),
('people','We invest in training and developing our team.',2),
('people','The business can run for a week without the founder present.',3),
('customer','We actively collect and act on customer feedback.',1),
('customer','We have service standards our team is expected to meet.',2),
('customer','We know our customer retention or repeat purchase rate.',3),
('compliance','Our business is properly registered with all required licences.',1),
('compliance','We file tax returns accurately and on time.',2),
('compliance','We keep contracts and statutory records in an organised system.',3),
('innovation','We regularly test new products, services or offers.',1),
('innovation','We monitor competitors and market trends deliberately.',2),
('innovation','We gather customer insight before launching something new.',3),
('funding','We understand what funding options are realistic for our stage.',1),
('funding','Our financial statements are current and presentable to a lender.',2),
('funding','We know exactly what we would use new capital for and the return.',3),
('risk','We have identified the main risks that could disrupt the business.',1),
('risk','We hold savings or insurance that could absorb a major shock.',2),
('risk','We back up critical business data and records.',3),
('sustainability','We consider environmental impact in how we operate.',1),
('sustainability','We contribute positively to the community we operate in.',2),
('sustainability','We consider fairness and ethics in sourcing and employment.',3)
) AS q(cat, prompt, sort_order)
JOIN public.assessment_categories c ON c.slug = q.cat;

-- SEED COURSES
INSERT INTO public.courses (slug, title, summary, description, category_slug, level, duration_minutes, is_premium, sort_order, lessons) VALUES
('business-model-foundations','Business Model Foundations','Turn your idea into a business model you can explain in one page.','Build a clear one-page business model: who you serve, the problem you solve, how you make money and what makes you hard to copy. Practical worksheets designed for Kenyan MSMEs.','strategy','Beginner',60,false,1,'[{"title":"Why a business model beats a business idea","body":"A short lesson on the difference between an idea and a working model, with local examples."},{"title":"Mapping your customer and their problem","body":"Define your ideal customer segment and the job they hire you to do."},{"title":"Your one-page model","body":"Assemble revenue streams, costs and channels into a single page you can act on."}]'::jsonb),
('bookkeeping-basics','Bookkeeping Basics for MSMEs','Simple, reliable records you can keep in 20 minutes a week.','Learn a lightweight bookkeeping routine: separating accounts, recording sales and expenses, and producing a monthly summary you can trust when you need a loan.','finance','Beginner',45,false,2,'[{"title":"Separating business and personal money","body":"Set up accounts and a discipline that keeps your numbers clean."},{"title":"Recording sales and expenses","body":"A weekly 20-minute routine using a notebook, spreadsheet or mobile app."},{"title":"Your monthly summary","body":"Produce a simple profit and cash summary and read what it tells you."}]'::jsonb),
('cash-flow-mastery','Cash Flow Mastery','Forecast, protect and grow the cash in your business.','Cash, not profit, kills small businesses. Build a rolling three-month forecast, tighten collections and plan for lean months with confidence.','finance','Intermediate',75,true,3,'[{"title":"Profit is not cash","body":"Understand the gap between what you earn and what you hold."},{"title":"Building a three-month forecast","body":"A practical template for projecting inflows and outflows."},{"title":"Getting paid faster","body":"Terms, follow-ups and deposits that reduce late payment."}]'::jsonb),
('digital-presence-essentials','Digital Presence Essentials','Get found, trusted and contacted online.','Set up the digital basics that win customers: a findable profile, consistent content, WhatsApp Business flows and digital payments that reconcile.','digital','Beginner',50,false,4,'[{"title":"Where your customers actually look","body":"Choose the two channels that matter for your business and ignore the rest."},{"title":"A profile that converts","body":"Photos, descriptions and proof that turn a visitor into an enquiry."},{"title":"Digital payments done properly","body":"Accept and reconcile mobile payments without losing track."}]'::jsonb),
('marketing-that-sells','Marketing That Sells','Build a repeatable way to attract and convert customers.','Move from random posting to a simple marketing system: an offer, a channel, a follow-up sequence and numbers you review every month.','marketing','Intermediate',65,false,5,'[{"title":"Crafting an offer people say yes to","body":"Structure a clear, specific offer with a reason to act now."},{"title":"One channel, done well","body":"Pick a channel and build a weekly rhythm you can sustain."},{"title":"Follow-up and repeat sales","body":"Capture contacts and turn one-time buyers into regulars."}]'::jsonb),
('operations-and-quality','Operations & Quality Systems','Document your work so quality stops depending on you.','Turn what is in your head into simple checklists and standards, so your team delivers consistently and you can step back.','operations','Intermediate',55,false,6,'[{"title":"Finding your core process","body":"Map the handful of steps that create your value."},{"title":"Checklists and standards","body":"Write standards your team can actually follow."},{"title":"Fixing recurring problems","body":"A simple review loop to stop the same mistake twice."}]'::jsonb),
('compliance-made-clear','Compliance Made Clear','Register, licence and file with confidence.','Understand the compliance essentials for a Kenyan MSME: registration, permits, tax obligations and the records to keep, without the jargon.','compliance','Beginner',40,false,7,'[{"title":"Registration and licences","body":"What you need at your stage and where it applies."},{"title":"Tax obligations without fear","body":"Understand your filings and build a calendar you can keep."},{"title":"Records that protect you","body":"Contracts, receipts and statutory records worth organising."}]'::jsonb),
('investment-readiness','Investment Readiness','Prepare your business for lenders and investors.','Get funding-ready: know your options, prepare presentable financials and build a use-of-funds case that answers a lender''s real questions.','funding','Advanced',80,true,8,'[{"title":"Which capital fits your stage","body":"Grants, loans, asset finance and equity compared honestly."},{"title":"Financials a lender will accept","body":"Prepare statements and supporting evidence."},{"title":"Your use-of-funds case","body":"Show what the money does and how it comes back."}]'::jsonb),
('leading-a-small-team','Leading a Small Team','Hire, delegate and build a team that performs.','Practical leadership for owner-managers: hiring for fit, setting expectations, delegating properly and holding a team accountable with respect.','people','Intermediate',60,false,9,'[{"title":"Hiring your first employees","body":"Define the role before you look for the person."},{"title":"Delegating without losing control","body":"Hand over outcomes, not just tasks."},{"title":"Feedback and accountability","body":"Simple rhythms that keep standards high."}]'::jsonb);