-- 1. Clear old catalogue/assessment data
DELETE FROM public.course_progress;
DELETE FROM public.enrollments;
DELETE FROM public.assessment_responses;
DELETE FROM public.assessment_results;
DELETE FROM public.assessment_questions;
DELETE FROM public.courses;
DELETE FROM public.assessment_categories;

-- 2. Domains
ALTER TABLE public.assessment_categories ADD COLUMN IF NOT EXISTS code text;
INSERT INTO public.assessment_categories (code, slug, name, description, sort_order) VALUES
('D01','strategy','Strategy & Leadership','Direction, positioning, decision-making and the leadership capacity to execute.',1),
('D02','finance','Financial Management','Records, cash flow, margins, controls and funding readiness.',2),
('D03','marketing','Marketing & Sales','Customers, positioning, pipelines, conversion and retention.',3),
('D04','digital','Digital Readiness','Tools, online presence, e-commerce, AI adoption and data protection.',4),
('D05','analytics','Data & Analytics','Using business data to measure performance and decide well.',5),
('D06','operations','Operations','Workflows, productivity, quality, stock, procurement and supply chain.',6),
('D07','people','People & HR','Hiring, managing, developing and retaining a capable team.',7),
('D08','governance','Governance & Compliance','Registration, licences, contracts, controls, risk and continuity.',8),
('D09','market-access','Market Access','Tenders, corporate buyers, partnerships, new markets and export.',9),
('D10','innovation','Innovation','Developing, testing and commercialising new products and services.',10),
('D11','sustainability','Sustainability','Environmental and social responsibility built into how you trade.',11),
('D12','growth','Growth & Transformation','Systems, people and capital required to scale beyond the founder.',12);

-- 3. Questions (3 per domain)
INSERT INTO public.assessment_questions (category_id, prompt, sort_order)
SELECT c.id, q.prompt, q.ord FROM public.assessment_categories c
JOIN (VALUES
('strategy','We have a written strategy with clear goals for the next 12 months.',1),
('strategy','We know exactly who our ideal customer is and why they choose us over alternatives.',2),
('strategy','The business can make key decisions and keep running when the owner is away.',3),
('finance','Business income and expenses are recorded accurately every month.',1),
('finance','We know our profit margin per product or service and use it to set prices.',2),
('finance','We prepare and monitor a cash-flow forecast, and business money is separate from personal money.',3),
('marketing','We have a repeatable way of finding new customers, not just referrals.',1),
('marketing','We track leads, conversions and sales against monthly targets.',2),
('marketing','Existing customers come back and we deliberately manage those relationships.',3),
('digital','The business has an active online presence customers can find and trust.',1),
('digital','We use digital tools (payments, records, messaging, AI) to run the business day to day.',2),
('digital','Customer data and business systems are backed up and protected.',3),
('analytics','We review business numbers (sales, stock, costs) on a regular schedule.',1),
('analytics','We track a small set of KPIs that tell us whether the business is healthy.',2),
('analytics','Major decisions are based on our own data rather than gut feel.',3),
('operations','Our core processes are documented so anyone trained can follow them.',1),
('operations','Stock, materials and losses are controlled and counted.',2),
('operations','We evaluate suppliers and manage delivery, quality and turnaround times.',3),
('people','We hire deliberately, with clear roles and expectations.',1),
('people','Staff have targets and receive regular feedback on performance.',2),
('people','We invest in training and our best people stay with us.',3),
('governance','The business is properly registered with the licences and permits it needs.',1),
('governance','We use written contracts with customers, suppliers and employees.',2),
('governance','We have identified our main risks and have a plan for major disruptions.',3),
('market-access','We actively pursue larger buyers, corporates or institutional customers.',1),
('market-access','We can find, interpret and respond to tenders or procurement opportunities.',2),
('market-access','We are ready — documentation, pricing, capacity — to sell into new or export markets.',3),
('innovation','We regularly test new products, services or ways of serving customers.',1),
('innovation','We gather customer feedback and use it to improve our offer.',2),
('innovation','We have launched something new in the last 12 months that earns revenue.',3),
('sustainability','We manage waste, energy and materials responsibly and cost-effectively.',1),
('sustainability','Our business creates clear social value in the community we operate in.',2),
('sustainability','We can show buyers or funders how we work sustainably.',3),
('growth','We have systems that would let the business double without breaking.',1),
('growth','We have the people and delegation in place to support expansion.',2),
('growth','We have a funded, written plan for the next stage of growth.',3)
) AS q(slug, prompt, ord) ON q.slug = c.slug;

-- 4. Course columns
ALTER TABLE public.courses
  ADD COLUMN IF NOT EXISTS group_code text NOT NULL DEFAULT 'A',
  ADD COLUMN IF NOT EXISTS group_name text NOT NULL DEFAULT 'Entrepreneurship & Strategy',
  ADD COLUMN IF NOT EXISTS domain_code text NOT NULL DEFAULT 'D01',
  ADD COLUMN IF NOT EXISTS duration_weeks integer NOT NULL DEFAULT 4,
  ADD COLUMN IF NOT EXISTS price_kes integer NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS certificate_type text NOT NULL DEFAULT 'UZIA Certificate',
  ADD COLUMN IF NOT EXISTS credential_level text NOT NULL DEFAULT 'Foundation',
  ADD COLUMN IF NOT EXISTS learning_hours integer NOT NULL DEFAULT 20,
  ADD COLUMN IF NOT EXISTS learning_outcomes text[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS prerequisite_course_id uuid REFERENCES public.courses(id) ON DELETE SET NULL;

-- 5. The 50 courses
INSERT INTO public.courses
  (slug, title, summary, description, category_slug, domain_code, group_code, group_name,
   level, credential_level, duration_weeks, duration_minutes, learning_hours, price_kes,
   certificate_type, is_premium, sort_order)
SELECT v.slug, v.title, v.summary, v.summary, v.domain_slug, v.domain_code, v.group_code, v.group_name,
       v.lvl, v.lvl, v.weeks, v.weeks * 300, v.weeks * 5, v.price, v.cert, v.price > 5000, v.ord
FROM (VALUES
('entrepreneurship-new-venture-creation','Entrepreneurship & New Venture Creation','Turn an idea into a viable business: identify opportunities, customers, a revenue model and a launch strategy.','strategy','D01','A','Entrepreneurship & Strategy','Foundation',4,4500,'UZIA Certificate',1),
('msme-strategy-business-growth','MSME Strategy & Business Growth','Develop strategic objectives, competitive positioning, KPIs and a 12-month growth plan.','strategy','D01','A','Entrepreneurship & Strategy','Growth',4,5500,'UZIA Professional Certificate',2),
('business-planning-and-modelling','Business Planning & Business Modelling','Build a practical business plan, business model and set of financial assumptions.','strategy','D01','A','Entrepreneurship & Strategy','Foundation',4,4500,'UZIA Certificate',3),
('small-business-management','Small Business Management','Build systems for managing people, money, customers and operations.','strategy','D01','A','Entrepreneurship & Strategy','Foundation',4,4000,'UZIA Certificate',4),
('strategic-leadership-for-msmes','Strategic Leadership for MSMEs','Strengthen leadership, decision-making, governance and execution.','strategy','D01','A','Entrepreneurship & Strategy','Advanced',5,7500,'UZIA Executive Certificate',5),
('financial-literacy-for-msmes','Financial Literacy for MSMEs','Understand revenue, costs, profit, cash flow, margins and financial statements.','finance','D02','B','Finance & Accounting','Foundation',3,3500,'UZIA Certificate',6),
('financial-management-for-msmes','Financial Management for MSMEs','Budgeting, working capital, cash-flow management, profitability and financial controls.','finance','D02','B','Finance & Accounting','Growth',5,5500,'UZIA Professional Certificate',7),
('practical-accounting-for-business-owners','Practical Accounting for Business Owners','Record transactions and understand P&L, balance sheets and basic accounting controls.','finance','D02','B','Finance & Accounting','Foundation',4,4000,'UZIA Certificate',8),
('financial-modelling-for-entrepreneurs','Financial Modelling for Entrepreneurs','Build spreadsheet forecasts, scenarios, break-even and investment models.','finance','D02','B','Finance & Accounting','Advanced',5,7500,'UZIA Professional Certificate',9),
('investment-and-funding-readiness','Investment & Funding Readiness','Prepare the enterprise for banks, investors, grants and other financing.','finance','D02','B','Finance & Accounting','Growth',4,6000,'UZIA Professional Certificate',10),
('marketing-fundamentals-for-msmes','Marketing Fundamentals for MSMEs','Define target customers, positioning, value proposition and marketing strategy.','marketing','D03','C','Marketing, Sales & Customer Growth','Foundation',3,3500,'UZIA Certificate',11),
('digital-marketing-and-social-media','Digital Marketing & Social Media','Build digital campaigns using social media, content, email and paid advertising.','digital','D04','C','Marketing, Sales & Customer Growth','Growth',5,5500,'UZIA Professional Certificate',12),
('sales-management-and-customer-acquisition','Sales Management & Customer Acquisition','Build sales pipelines, conversion systems, targets and customer acquisition strategies.','marketing','D03','C','Marketing, Sales & Customer Growth','Growth',4,5000,'UZIA Certificate',13),
('customer-relationship-management','Customer Relationship Management','Build customer retention, service and CRM processes.','marketing','D03','C','Marketing, Sales & Customer Growth','Foundation',3,4000,'UZIA Certificate',14),
('brand-building-for-msmes','Brand Building for MSMEs','Develop brand positioning, identity, messaging and customer trust.','marketing','D03','C','Marketing, Sales & Customer Growth','Growth',4,5000,'UZIA Professional Certificate',15),
('digital-readiness-for-msmes','Digital Readiness for MSMEs','Assess technology gaps and create a digital transformation roadmap.','digital','D04','D','Digital Transformation & AI','Foundation',3,3500,'UZIA Certificate',16),
('ai-tools-for-business-owners','AI Tools for Business Owners','Use generative AI for marketing, research, productivity, customer service and planning.','digital','D04','D','Digital Transformation & AI','Growth',4,5000,'UZIA AI Business Certificate',17),
('ai-and-business-automation','AI & Business Automation','Automate repetitive processes and improve operational productivity.','digital','D04','D','Digital Transformation & AI','Advanced',5,7500,'UZIA Professional Certificate',18),
('ecommerce-and-digital-business','E-Commerce & Digital Business','Build and manage online sales channels, payments, customer journeys and fulfilment.','digital','D04','D','Digital Transformation & AI','Growth',4,5000,'UZIA Certificate',19),
('cybersecurity-and-data-protection-for-msmes','Cybersecurity & Data Protection for MSMEs','Protect business systems, customer data and digital assets.','digital','D04','D','Digital Transformation & AI','Foundation',3,4000,'UZIA Certificate',20),
('business-analytics-for-msmes','Business Analytics for MSMEs','Use business data to understand performance and make better decisions.','analytics','D05','E','Data, Analytics & Decision-Making','Growth',5,6000,'UZIA Professional Certificate',21),
('excel-for-business-owners','Excel for Business Owners','Use spreadsheets for budgeting, inventory, sales and financial analysis.','analytics','D05','E','Data, Analytics & Decision-Making','Foundation',4,4000,'UZIA Certificate',22),
('power-bi-for-msmes','Power BI for MSMEs','Build dashboards and visualise business performance.','analytics','D05','E','Data, Analytics & Decision-Making','Advanced',5,7500,'UZIA Professional Certificate',23),
('data-driven-decision-making','Data-Driven Decision Making','Convert operational and customer data into actionable business decisions.','analytics','D05','E','Data, Analytics & Decision-Making','Advanced',4,6000,'UZIA Professional Certificate',24),
('business-intelligence-for-growth','Business Intelligence for Growth','Develop integrated performance dashboards and management information systems.','analytics','D05','E','Data, Analytics & Decision-Making','Executive',6,9500,'UZIA Executive Certificate',25),
('msme-operations-management','MSME Operations Management','Improve workflows, productivity, quality and operational controls.','operations','D06','F','Operations, Procurement & Supply Chain','Foundation',4,4500,'UZIA Certificate',26),
('inventory-management','Inventory Management','Control stock, reduce losses and optimise inventory levels.','operations','D06','F','Operations, Procurement & Supply Chain','Foundation',3,3500,'UZIA Certificate',27),
('procurement-and-supplier-management','Procurement & Supplier Management','Improve sourcing, supplier evaluation, purchasing and cost control.','operations','D06','F','Operations, Procurement & Supply Chain','Growth',4,5000,'UZIA Professional Certificate',28),
('supply-chain-and-logistics-management','Supply Chain & Logistics Management','Manage movement of products, suppliers, distribution and logistics.','operations','D06','F','Operations, Procurement & Supply Chain','Growth',5,6000,'UZIA Professional Certificate',29),
('digital-supply-chains-and-logistics','Digital Supply Chains & Logistics','Use technology and data to improve supply-chain visibility and efficiency.','operations','D06','F','Operations, Procurement & Supply Chain','Advanced',5,7500,'UZIA Professional Certificate',30),
('human-resource-management-for-msmes','Human Resource Management for MSMEs','Recruit, manage and develop employees effectively.','people','D07','G','People, Leadership & Organisation','Foundation',4,4500,'UZIA Certificate',31),
('performance-management-and-kpis','Performance Management & KPIs','Develop employee KPIs, appraisal systems and performance improvement plans.','people','D07','G','People, Leadership & Organisation','Growth',4,5000,'UZIA Professional Certificate',32),
('entrepreneurial-leadership','Entrepreneurial Leadership','Build leadership capacity, accountability and high-performing teams.','strategy','D01','G','People, Leadership & Organisation','Growth',4,5500,'UZIA Professional Certificate',33),
('negotiation-and-conflict-management','Negotiation & Conflict Management','Negotiate with customers, suppliers, employees and partners.','people','D07','G','People, Leadership & Organisation','Foundation',3,3500,'UZIA Certificate',34),
('building-high-performance-teams','Building High-Performance Teams','Develop organisational culture, teamwork, communication and employee engagement.','people','D07','G','People, Leadership & Organisation','Growth',4,5000,'UZIA Professional Certificate',35),
('legal-and-regulatory-compliance-for-msmes','Legal & Regulatory Compliance for MSMEs','Understand business registration, licences, contracts and key regulatory obligations.','governance','D08','H','Legal, Governance & Risk','Foundation',4,4500,'UZIA Compliance Certificate',36),
('corporate-governance-for-growing-businesses','Corporate Governance for Growing Businesses','Establish governance structures, policies, controls and accountability.','governance','D08','H','Legal, Governance & Risk','Advanced',4,6000,'UZIA Professional Certificate',37),
('business-risk-management','Business Risk Management','Identify, assess and mitigate financial, operational and strategic risks.','governance','D08','H','Legal, Governance & Risk','Growth',4,5000,'UZIA Professional Certificate',38),
('business-contracts-and-commercial-negotiation','Business Contracts & Commercial Negotiation','Understand essential commercial agreements and negotiate better terms.','governance','D08','H','Legal, Governance & Risk','Growth',3,4500,'UZIA Certificate',39),
('business-continuity-and-crisis-management','Business Continuity & Crisis Management','Prepare the business to survive disruptions and recover quickly.','governance','D08','H','Legal, Governance & Risk','Advanced',4,5500,'UZIA Professional Certificate',40),
('government-tenders-and-procurement-readiness','Government Tenders & Procurement Readiness','Identify, interpret and respond to procurement opportunities.','market-access','D09','I','Market Access, Tenders & International Business','Growth',4,5000,'UZIA Tender Readiness Certificate',41),
('market-access-and-business-development','Market Access & Business Development','Identify markets, build partnerships and develop business-development pipelines.','market-access','D09','I','Market Access, Tenders & International Business','Growth',4,5000,'UZIA Professional Certificate',42),
('export-readiness-for-african-msmes','Export Readiness for African MSMEs','Prepare products, documentation, pricing and processes for export.','market-access','D09','I','Market Access, Tenders & International Business','Advanced',5,6500,'UZIA Export Certificate',43),
('international-trade-documentation-and-logistics','International Trade Documentation & Logistics','Understand trade documentation, shipping, logistics and international transactions.','market-access','D09','I','Market Access, Tenders & International Business','Advanced',5,6500,'UZIA Professional Certificate',44),
('b2b-sales-and-corporate-market-access','B2B Sales & Corporate Market Access','Win corporate customers through proposals, presentations, account management and partnerships.','market-access','D09','I','Market Access, Tenders & International Business','Growth',4,5500,'UZIA Professional Certificate',45),
('innovation-and-product-development','Innovation & Product Development','Develop, test and commercialise new products and services.','innovation','D10','J','Innovation, Sustainability & Scale','Advanced',5,6500,'UZIA Innovation Certificate',46),
('design-thinking-for-msmes','Design Thinking for MSMEs','Solve customer problems through structured innovation and rapid testing.','innovation','D10','J','Innovation, Sustainability & Scale','Growth',4,5000,'UZIA Certificate',47),
('sustainable-business-and-esg','Sustainable Business & ESG','Integrate sustainability, environmental responsibility and social impact into the business.','sustainability','D11','J','Innovation, Sustainability & Scale','Advanced',4,5500,'UZIA Sustainability Certificate',48),
('scaling-and-managing-a-growing-business','Scaling & Managing a Growing Business','Build the systems, people, capital and processes required for expansion.','growth','D12','J','Innovation, Sustainability & Scale','Executive',6,8500,'UZIA Executive Certificate',49),
('msme-ceo-growth-and-transformation-programme','MSME CEO Growth & Transformation Programme','Integrate strategy, finance, marketing, people, technology, governance and growth into one transformation plan.','growth','D12','J','Innovation, Sustainability & Scale','Executive',8,15000,'UZIA Executive MSME Certificate',50)
) AS v(slug, title, summary, domain_slug, domain_code, group_code, group_name, lvl, weeks, price, cert, ord);

-- 6. Description, outcomes and weekly modules
UPDATE public.courses SET
  description = summary || ' Delivered as a ' || duration_weeks || '-week applied programme for African MSMEs, with weekly work-based assignments you complete using your own business, and a ' || certificate_type || ' on completion.',
  learning_outcomes = ARRAY[
    'Apply ' || title || ' practices directly in your own business',
    'Complete weekly work-based assignments using your real business data',
    'Produce a practical action plan you can implement immediately',
    'Earn the ' || certificate_type
  ],
  lessons = (
    SELECT jsonb_agg(jsonb_build_object(
      'title', 'Module ' || w || ' — Week ' || w,
      'body', 'Week ' || w || ' of ' || courses.title || ': guided study, a worked Kenyan MSME example, and a work-based assignment applying this week''s material to your own business.'
    ) ORDER BY w)
    FROM generate_series(1, courses.duration_weeks) AS w
  );

-- 7. Recommendation matrix
CREATE TABLE public.course_recommendations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  course_id uuid NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
  domain_code text NOT NULL,
  min_score integer NOT NULL DEFAULT 0,
  max_score integer NOT NULL DEFAULT 100,
  priority integer NOT NULL DEFAULT 1,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (course_id, domain_code)
);
GRANT SELECT ON public.course_recommendations TO anon, authenticated;
GRANT ALL ON public.course_recommendations TO service_role;
ALTER TABLE public.course_recommendations ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Recommendation matrix is public" ON public.course_recommendations
  FOR SELECT TO anon, authenticated USING (true);

INSERT INTO public.course_recommendations (course_id, domain_code, min_score, max_score, priority)
SELECT id, domain_code,
  CASE credential_level WHEN 'Foundation' THEN 0 WHEN 'Growth' THEN 0 WHEN 'Advanced' THEN 40 ELSE 60 END,
  CASE credential_level WHEN 'Foundation' THEN 59 WHEN 'Growth' THEN 74 WHEN 'Advanced' THEN 74 ELSE 89 END,
  CASE credential_level WHEN 'Foundation' THEN 1 WHEN 'Growth' THEN 2 WHEN 'Advanced' THEN 3 ELSE 4 END
FROM public.courses;

-- Cross-domain triggers
INSERT INTO public.course_recommendations (course_id, domain_code, min_score, max_score, priority)
SELECT c.id, x.domain_code, x.min_score, x.max_score, x.priority
FROM public.courses c
JOIN (VALUES
('digital-marketing-and-social-media','D03',0,74,2),
('excel-for-business-owners','D02',0,59,1),
('financial-modelling-for-entrepreneurs','D12',40,74,3),
('investment-and-funding-readiness','D12',0,74,2),
('digital-supply-chains-and-logistics','D04',40,74,3),
('ecommerce-and-digital-business','D09',0,74,2),
('government-tenders-and-procurement-readiness','D06',0,74,3),
('ai-tools-for-business-owners','D05',0,74,2),
('business-intelligence-for-growth','D12',0,74,4),
('strategic-leadership-for-msmes','D07',40,74,3),
('entrepreneurial-leadership','D07',0,74,2),
('scaling-and-managing-a-growing-business','D01',60,74,4),
('msme-ceo-growth-and-transformation-programme','D01',60,79,4),
('business-continuity-and-crisis-management','D06',0,59,3),
('b2b-sales-and-corporate-market-access','D03',0,74,2),
('design-thinking-for-msmes','D03',40,74,3)
) AS x(slug, domain_code, min_score, max_score, priority) ON x.slug = c.slug
ON CONFLICT (course_id, domain_code) DO NOTHING;

-- 8. Certificates
CREATE TABLE public.certificates (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  course_id uuid NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
  certificate_number text NOT NULL UNIQUE,
  learner_name text NOT NULL,
  business_name text,
  course_title text NOT NULL,
  credential_level text NOT NULL,
  certificate_type text NOT NULL,
  learning_hours integer NOT NULL DEFAULT 0,
  assessment_score numeric,
  issued_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, course_id)
);
GRANT SELECT, INSERT ON public.certificates TO authenticated;
GRANT ALL ON public.certificates TO service_role;
ALTER TABLE public.certificates ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users read own certificates" ON public.certificates
  FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Users issue own certificates" ON public.certificates
  FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);

CREATE OR REPLACE FUNCTION public.verify_certificate(_number text)
RETURNS TABLE (
  certificate_number text,
  learner_name text,
  business_name text,
  course_title text,
  credential_level text,
  certificate_type text,
  learning_hours integer,
  assessment_score numeric,
  issued_at timestamptz
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT c.certificate_number, c.learner_name, c.business_name, c.course_title,
         c.credential_level, c.certificate_type, c.learning_hours, c.assessment_score, c.issued_at
  FROM public.certificates c
  WHERE upper(c.certificate_number) = upper(trim(_number))
  LIMIT 1;
$$;
REVOKE ALL ON FUNCTION public.verify_certificate(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.verify_certificate(text) TO anon, authenticated;