
-- 1) investors
CREATE TABLE public.investors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  name_bn TEXT,
  mobile TEXT,
  email TEXT,
  address TEXT,
  nid TEXT,
  photo_url TEXT,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','frozen','inactive')),
  joined_at DATE NOT NULL DEFAULT CURRENT_DATE,
  notes TEXT,
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_investors_status ON public.investors(status);

-- 2) investments
CREATE TABLE public.investments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  investor_id UUID NOT NULL REFERENCES public.investors(id) ON DELETE CASCADE,
  amount NUMERIC(14,2) NOT NULL CHECK (amount > 0),
  invested_at DATE NOT NULL DEFAULT CURRENT_DATE,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','withdrawn','frozen')),
  withdrawn_at DATE,
  notes TEXT,
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_investments_investor ON public.investments(investor_id, status);
CREATE INDEX idx_investments_status ON public.investments(status);

-- 3) company_profits
CREATE TABLE public.company_profits (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  period_month DATE NOT NULL UNIQUE,
  gross_profit NUMERIC(14,2) NOT NULL DEFAULT 0,
  expenses NUMERIC(14,2) NOT NULL DEFAULT 0,
  net_profit NUMERIC(14,2) NOT NULL DEFAULT 0,
  investor_share_percent NUMERIC(5,2) NOT NULL DEFAULT 40 CHECK (investor_share_percent BETWEEN 0 AND 100),
  company_reserve_percent NUMERIC(5,2) NOT NULL DEFAULT 30 CHECK (company_reserve_percent BETWEEN 0 AND 100),
  management_percent NUMERIC(5,2) NOT NULL DEFAULT 20 CHECK (management_percent BETWEEN 0 AND 100),
  emergency_fund_percent NUMERIC(5,2) NOT NULL DEFAULT 10 CHECK (emergency_fund_percent BETWEEN 0 AND 100),
  distributable_amount NUMERIC(14,2) NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','locked','distributed')),
  locked_at TIMESTAMPTZ,
  distributed_at TIMESTAMPTZ,
  notes TEXT,
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_company_profits_status ON public.company_profits(status);

-- 4) investor_profit_distributions
CREATE TABLE public.investor_profit_distributions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_profit_id UUID NOT NULL REFERENCES public.company_profits(id) ON DELETE CASCADE,
  investor_id UUID NOT NULL REFERENCES public.investors(id) ON DELETE CASCADE,
  investment_amount NUMERIC(14,2) NOT NULL,
  investment_ratio NUMERIC(10,6) NOT NULL,
  distributed_amount NUMERIC(14,2) NOT NULL,
  adjustment_amount NUMERIC(14,2) NOT NULL DEFAULT 0,
  adjustment_reason TEXT,
  status TEXT NOT NULL DEFAULT 'paid' CHECK (status IN ('paid','held','adjusted')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (company_profit_id, investor_id)
);
CREATE INDEX idx_ipd_profit ON public.investor_profit_distributions(company_profit_id);
CREATE INDEX idx_ipd_investor ON public.investor_profit_distributions(investor_id);

-- 5) investor_wallets
CREATE TABLE public.investor_wallets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  investor_id UUID NOT NULL UNIQUE REFERENCES public.investors(id) ON DELETE CASCADE,
  total_earned NUMERIC(14,2) NOT NULL DEFAULT 0,
  total_withdrawn NUMERIC(14,2) NOT NULL DEFAULT 0,
  balance NUMERIC(14,2) NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- updated_at triggers
CREATE TRIGGER trg_investors_updated BEFORE UPDATE ON public.investors
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE TRIGGER trg_investments_updated BEFORE UPDATE ON public.investments
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE TRIGGER trg_company_profits_updated BEFORE UPDATE ON public.company_profits
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- Enable RLS
ALTER TABLE public.investors ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.investments ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.company_profits ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.investor_profit_distributions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.investor_wallets ENABLE ROW LEVEL SECURITY;

-- Policies: staff (admin/staff) full access
CREATE POLICY "staff_all_investors" ON public.investors FOR ALL TO authenticated
  USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));
CREATE POLICY "staff_all_investments" ON public.investments FOR ALL TO authenticated
  USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));
CREATE POLICY "staff_all_company_profits" ON public.company_profits FOR ALL TO authenticated
  USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));
CREATE POLICY "staff_all_ipd" ON public.investor_profit_distributions FOR ALL TO authenticated
  USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));
CREATE POLICY "staff_all_iw" ON public.investor_wallets FOR ALL TO authenticated
  USING (public.is_staff(auth.uid())) WITH CHECK (public.is_staff(auth.uid()));

-- Distribution engine
CREATE OR REPLACE FUNCTION public.distribute_company_profit(p_profit_id UUID)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_profit public.company_profits%ROWTYPE;
  v_total_active NUMERIC := 0;
  v_pool NUMERIC := 0;
  v_count INT := 0;
  inv RECORD;
  v_ratio NUMERIC;
  v_amount NUMERIC;
BEGIN
  SELECT * INTO v_profit FROM public.company_profits WHERE id = p_profit_id FOR UPDATE;
  IF v_profit.id IS NULL THEN
    RAISE EXCEPTION 'Profit record not found';
  END IF;
  IF v_profit.status = 'distributed' THEN
    RAISE EXCEPTION 'Already distributed';
  END IF;
  IF v_profit.status <> 'locked' THEN
    RAISE EXCEPTION 'Profit must be locked before distribution';
  END IF;

  v_pool := COALESCE(v_profit.distributable_amount, 0);
  IF v_pool <= 0 THEN
    RAISE EXCEPTION 'Distributable amount is zero';
  END IF;

  SELECT COALESCE(SUM(i.amount), 0) INTO v_total_active
    FROM public.investments i
    JOIN public.investors inv2 ON inv2.id = i.investor_id
   WHERE i.status = 'active' AND inv2.status = 'active';

  IF v_total_active <= 0 THEN
    RAISE EXCEPTION 'No active investments found';
  END IF;

  -- Clear any previous (recalc) rows for this profit
  DELETE FROM public.investor_profit_distributions WHERE company_profit_id = p_profit_id;

  FOR inv IN
    SELECT i.investor_id, SUM(i.amount) AS amt
      FROM public.investments i
      JOIN public.investors inv2 ON inv2.id = i.investor_id
     WHERE i.status = 'active' AND inv2.status = 'active'
     GROUP BY i.investor_id
  LOOP
    v_ratio := inv.amt / v_total_active;
    v_amount := ROUND(v_pool * v_ratio, 2);

    INSERT INTO public.investor_profit_distributions
      (company_profit_id, investor_id, investment_amount, investment_ratio, distributed_amount, status)
    VALUES (p_profit_id, inv.investor_id, inv.amt, v_ratio, v_amount, 'paid');

    INSERT INTO public.investor_wallets (investor_id, total_earned, balance)
    VALUES (inv.investor_id, v_amount, v_amount)
    ON CONFLICT (investor_id) DO UPDATE
      SET total_earned = public.investor_wallets.total_earned + v_amount,
          balance = public.investor_wallets.balance + v_amount,
          updated_at = now();

    v_count := v_count + 1;
  END LOOP;

  UPDATE public.company_profits
     SET status = 'distributed', distributed_at = now()
   WHERE id = p_profit_id;

  RETURN jsonb_build_object('ok', true, 'investors', v_count, 'pool', v_pool, 'total_active', v_total_active);
END;
$$;
