-- helpers
CREATE OR REPLACE FUNCTION public.set_updated_at() RETURNS TRIGGER
LANGUAGE plpgsql SET search_path = public AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;

-- profiles
CREATE TABLE public.profiles (
  id uuid PRIMARY KEY REFERENCES auth.users ON DELETE CASCADE,
  full_name text,
  email text,
  phone 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 "own profile" ON public.profiles FOR ALL TO authenticated USING (id = auth.uid()) WITH CHECK (id = auth.uid());
CREATE TRIGGER trg_profiles_updated 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, email)
  VALUES (NEW.id, NEW.raw_user_meta_data->>'full_name', NEW.email)
  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();

-- settings (single row key/value)
CREATE TABLE public.settings (
  key text PRIMARY KEY,
  value jsonb NOT NULL DEFAULT '{}'::jsonb,
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.settings TO authenticated;
GRANT ALL ON public.settings TO service_role;
ALTER TABLE public.settings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "settings all" ON public.settings FOR ALL TO authenticated USING (true) WITH CHECK (true);

INSERT INTO public.settings (key, value) VALUES
 ('company', '{"name":"My Studio","legal_name":"","phone":"","email":"","address":"","city":"","tax_number":"","currency":"BDT","currency_symbol":"Tk","default_tax_rate":15,"invoice_prefix":"INV-","invoice_terms":"Payment due within 15 days.","fiscal_year_start_month":1}'::jsonb),
 ('finance', '{"minimum_cash_reserve":0}'::jsonb),
 ('sms', '{"provider":"","api_url":"","api_key":"","username":"","password":"","sender_id":"","method":"GET","enabled":false}'::jsonb);

-- audit log
CREATE TABLE public.audit_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid,
  action text NOT NULL,
  entity_type text NOT NULL,
  entity_id uuid,
  description text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_audit_created ON public.audit_logs(created_at DESC);
CREATE INDEX idx_audit_entity ON public.audit_logs(entity_type, entity_id);
GRANT SELECT, INSERT ON public.audit_logs TO authenticated;
GRANT ALL ON public.audit_logs TO service_role;
ALTER TABLE public.audit_logs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "audit read" ON public.audit_logs FOR SELECT TO authenticated USING (true);
CREATE POLICY "audit insert" ON public.audit_logs FOR INSERT TO authenticated WITH CHECK (true);

-- account types
CREATE TABLE public.account_types (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code text NOT NULL UNIQUE,
  name text NOT NULL,
  category text NOT NULL CHECK (category IN ('asset','liability','equity','revenue','expense')),
  normal_balance text NOT NULL CHECK (normal_balance IN ('debit','credit')),
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.account_types TO authenticated;
GRANT ALL ON public.account_types TO service_role;
ALTER TABLE public.account_types ENABLE ROW LEVEL SECURITY;
CREATE POLICY "account_types all" ON public.account_types FOR ALL TO authenticated USING (true) WITH CHECK (true);

INSERT INTO public.account_types (code, name, category, normal_balance) VALUES
 ('CASH','Cash & Bank','asset','debit'),
 ('AR','Accounts Receivable','asset','debit'),
 ('FIXED','Fixed Asset','asset','debit'),
 ('OTHER_ASSET','Other Asset','asset','debit'),
 ('AP','Accounts Payable','liability','credit'),
 ('TAX','Tax Payable','liability','credit'),
 ('OTHER_LIAB','Other Liability','liability','credit'),
 ('CAPITAL','Owner Capital','equity','credit'),
 ('DRAWINGS','Owner Drawings','equity','debit'),
 ('RETAINED','Retained Earnings','equity','credit'),
 ('REVENUE','Revenue','revenue','credit'),
 ('COS','Cost of Sales','expense','debit'),
 ('EXPENSE','Operating Expense','expense','debit');

-- accounts (chart of accounts)
CREATE TABLE public.accounts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code text NOT NULL UNIQUE,
  name text NOT NULL,
  account_type_id uuid NOT NULL REFERENCES public.account_types(id),
  parent_id uuid REFERENCES public.accounts(id) ON DELETE SET NULL,
  is_active boolean NOT NULL DEFAULT true,
  is_system boolean NOT NULL DEFAULT false,
  description text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_accounts_type ON public.accounts(account_type_id);
CREATE INDEX idx_accounts_code ON public.accounts(code);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.accounts TO authenticated;
GRANT ALL ON public.accounts TO service_role;
ALTER TABLE public.accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "accounts all" ON public.accounts FOR ALL TO authenticated USING (true) WITH CHECK (true);
CREATE TRIGGER trg_accounts_updated BEFORE UPDATE ON public.accounts FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

INSERT INTO public.accounts (code, name, account_type_id, is_system)
SELECT v.code, v.name, t.id, true FROM (VALUES
 ('1000','Cash','CASH'),
 ('1010','Main Cash','CASH'),
 ('1020','Petty Cash','CASH'),
 ('1100','Bank','CASH'),
 ('1200','Accounts Receivable','AR'),
 ('1300','Equipment','FIXED'),
 ('2000','Accounts Payable','AP'),
 ('2100','VAT Payable','TAX'),
 ('2200','Other Liabilities','OTHER_LIAB'),
 ('3000','Owner Capital','CAPITAL'),
 ('3100','Owner Drawings','DRAWINGS'),
 ('3200','Retained Earnings','RETAINED'),
 ('3300','Current Year Profit','RETAINED'),
 ('4000','Studio Revenue','REVENUE'),
 ('4100','Service Revenue','REVENUE'),
 ('4200','Product Revenue','REVENUE'),
 ('4300','Other Revenue','REVENUE'),
 ('5000','Cost of Sales','COS'),
 ('6000','Salary Expense','EXPENSE'),
 ('6100','Rent Expense','EXPENSE'),
 ('6200','Electricity Expense','EXPENSE'),
 ('6300','Internet Expense','EXPENSE'),
 ('6400','Marketing Expense','EXPENSE'),
 ('6500','Maintenance Expense','EXPENSE'),
 ('6600','Transportation Expense','EXPENSE'),
 ('6700','Other Expense','EXPENSE')
) AS v(code, name, type_code)
JOIN public.account_types t ON t.code = v.type_code;

-- fiscal periods
CREATE TABLE public.fiscal_periods (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  start_date date NOT NULL,
  end_date date NOT NULL,
  status text NOT NULL DEFAULT 'open' CHECK (status IN ('open','closed')),
  closed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (start_date, end_date)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.fiscal_periods TO authenticated;
GRANT ALL ON public.fiscal_periods TO service_role;
ALTER TABLE public.fiscal_periods ENABLE ROW LEVEL SECURITY;
CREATE POLICY "fiscal all" ON public.fiscal_periods FOR ALL TO authenticated USING (true) WITH CHECK (true);

-- journal entries
CREATE TABLE public.journal_entries (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entry_number text NOT NULL UNIQUE,
  entry_date date NOT NULL DEFAULT CURRENT_DATE,
  memo text,
  source_type text NOT NULL DEFAULT 'manual',
  source_id uuid,
  status text NOT NULL DEFAULT 'posted' CHECK (status IN ('draft','posted','reversed')),
  reversed_by uuid REFERENCES public.journal_entries(id) ON DELETE SET NULL,
  reverses_entry_id uuid REFERENCES public.journal_entries(id) ON DELETE SET NULL,
  total_debit numeric(18,2) NOT NULL DEFAULT 0,
  total_credit numeric(18,2) NOT NULL DEFAULT 0,
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_je_date ON public.journal_entries(entry_date);
CREATE INDEX idx_je_source ON public.journal_entries(source_type, source_id);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.journal_entries TO authenticated;
GRANT ALL ON public.journal_entries TO service_role;
ALTER TABLE public.journal_entries ENABLE ROW LEVEL SECURITY;
CREATE POLICY "je read" ON public.journal_entries FOR SELECT TO authenticated USING (true);
CREATE POLICY "je insert" ON public.journal_entries FOR INSERT TO authenticated WITH CHECK (true);

CREATE TABLE public.journal_lines (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  journal_entry_id uuid NOT NULL REFERENCES public.journal_entries(id) ON DELETE CASCADE,
  account_id uuid NOT NULL REFERENCES public.accounts(id),
  debit numeric(18,2) NOT NULL DEFAULT 0 CHECK (debit >= 0),
  credit numeric(18,2) NOT NULL DEFAULT 0 CHECK (credit >= 0),
  description text,
  customer_id uuid,
  vendor_id uuid,
  owner_id uuid,
  line_no integer NOT NULL DEFAULT 1,
  created_at timestamptz NOT NULL DEFAULT now(),
  CHECK (NOT (debit > 0 AND credit > 0))
);
CREATE INDEX idx_jl_entry ON public.journal_lines(journal_entry_id);
CREATE INDEX idx_jl_account ON public.journal_lines(account_id);
GRANT SELECT, INSERT ON public.journal_lines TO authenticated;
GRANT ALL ON public.journal_lines TO service_role;
ALTER TABLE public.journal_lines ENABLE ROW LEVEL SECURITY;
CREATE POLICY "jl read" ON public.journal_lines FOR SELECT TO authenticated USING (true);
CREATE POLICY "jl insert" ON public.journal_lines FOR INSERT TO authenticated WITH CHECK (true);

-- immutability of posted entries
CREATE OR REPLACE FUNCTION public.protect_posted_entries() RETURNS TRIGGER
LANGUAGE plpgsql SET search_path = public AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    RAISE EXCEPTION 'Posted journal entries cannot be deleted. Create a reversing entry instead.';
  END IF;
  IF OLD.status = 'posted' AND (NEW.entry_date <> OLD.entry_date OR NEW.total_debit <> OLD.total_debit OR NEW.total_credit <> OLD.total_credit) THEN
    RAISE EXCEPTION 'Posted journal entries cannot be edited. Create a reversing entry instead.';
  END IF;
  RETURN NEW;
END; $$;
CREATE TRIGGER trg_je_protect BEFORE UPDATE OR DELETE ON public.journal_entries FOR EACH ROW EXECUTE FUNCTION public.protect_posted_entries();

CREATE OR REPLACE FUNCTION public.protect_journal_lines() RETURNS TRIGGER
LANGUAGE plpgsql SET search_path = public AS $$
BEGIN
  RAISE EXCEPTION 'Journal lines are immutable. Create a reversing entry instead.';
END; $$;
CREATE TRIGGER trg_jl_protect BEFORE UPDATE OR DELETE ON public.journal_lines FOR EACH ROW EXECUTE FUNCTION public.protect_journal_lines();

-- numbering
CREATE TABLE public.number_sequences (
  key text PRIMARY KEY,
  prefix text NOT NULL DEFAULT '',
  next_value bigint NOT NULL DEFAULT 1
);
GRANT SELECT, INSERT, UPDATE ON public.number_sequences TO authenticated;
GRANT ALL ON public.number_sequences TO service_role;
ALTER TABLE public.number_sequences ENABLE ROW LEVEL SECURITY;
CREATE POLICY "seq all" ON public.number_sequences FOR ALL TO authenticated USING (true) WITH CHECK (true);
INSERT INTO public.number_sequences (key, prefix) VALUES
 ('journal','JE-'),('invoice','INV-'),('payment','PMT-'),('expense','EXP-'),('bill','BILL-'),
 ('customer','CUS-'),('vendor','VEN-'),('booking','BK-'),('project','PRJ-'),('transfer','TRF-'),('billpay','BP-');

CREATE OR REPLACE FUNCTION public.next_number(_key text) RETURNS text
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _prefix text; _val bigint;
BEGIN
  INSERT INTO public.number_sequences(key, prefix) VALUES (_key, upper(left(_key,3))||'-')
  ON CONFLICT (key) DO NOTHING;
  UPDATE public.number_sequences SET next_value = next_value + 1
  WHERE key = _key RETURNING prefix, next_value - 1 INTO _prefix, _val;
  RETURN _prefix || lpad(_val::text, 5, '0');
END; $$;
GRANT EXECUTE ON FUNCTION public.next_number(text) TO authenticated;

-- core posting engine: create a balanced journal entry atomically
CREATE OR REPLACE FUNCTION public.post_journal_entry(
  _entry_date date,
  _memo text,
  _source_type text,
  _source_id uuid,
  _lines jsonb
) RETURNS uuid
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  _je_id uuid; _dr numeric(18,2) := 0; _cr numeric(18,2) := 0; _line jsonb; _i int := 0;
  _closed boolean;
BEGIN
  IF _lines IS NULL OR jsonb_array_length(_lines) < 2 THEN
    RAISE EXCEPTION 'A journal entry requires at least two lines';
  END IF;
  SELECT true INTO _closed FROM public.fiscal_periods
   WHERE status = 'closed' AND _entry_date BETWEEN start_date AND end_date LIMIT 1;
  IF _closed THEN RAISE EXCEPTION 'The fiscal period for % is closed', _entry_date; END IF;

  FOR _line IN SELECT * FROM jsonb_array_elements(_lines) LOOP
    _dr := _dr + COALESCE((_line->>'debit')::numeric, 0);
    _cr := _cr + COALESCE((_line->>'credit')::numeric, 0);
  END LOOP;
  IF round(_dr,2) <> round(_cr,2) THEN
    RAISE EXCEPTION 'Unbalanced journal entry: debit % <> credit %', _dr, _cr;
  END IF;
  IF round(_dr,2) = 0 THEN RAISE EXCEPTION 'Journal entry total cannot be zero'; END IF;

  INSERT INTO public.journal_entries (entry_number, entry_date, memo, source_type, source_id, status, total_debit, total_credit, created_by)
  VALUES (public.next_number('journal'), _entry_date, _memo, COALESCE(_source_type,'manual'), _source_id, 'posted', round(_dr,2), round(_cr,2), auth.uid())
  RETURNING id INTO _je_id;

  FOR _line IN SELECT * FROM jsonb_array_elements(_lines) LOOP
    _i := _i + 1;
    INSERT INTO public.journal_lines (journal_entry_id, account_id, debit, credit, description, customer_id, vendor_id, owner_id, line_no)
    VALUES (
      _je_id,
      (_line->>'account_id')::uuid,
      round(COALESCE((_line->>'debit')::numeric,0),2),
      round(COALESCE((_line->>'credit')::numeric,0),2),
      _line->>'description',
      NULLIF(_line->>'customer_id','')::uuid,
      NULLIF(_line->>'vendor_id','')::uuid,
      NULLIF(_line->>'owner_id','')::uuid,
      _i
    );
  END LOOP;
  RETURN _je_id;
END; $$;
GRANT EXECUTE ON FUNCTION public.post_journal_entry(date, text, text, uuid, jsonb) TO authenticated;

-- reverse an entry
CREATE OR REPLACE FUNCTION public.reverse_journal_entry(_entry_id uuid, _entry_date date DEFAULT NULL, _memo text DEFAULT NULL)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _lines jsonb; _new uuid; _src public.journal_entries;
BEGIN
  SELECT * INTO _src FROM public.journal_entries WHERE id = _entry_id;
  IF _src.id IS NULL THEN RAISE EXCEPTION 'Journal entry not found'; END IF;
  IF _src.status = 'reversed' THEN RAISE EXCEPTION 'Entry already reversed'; END IF;
  SELECT jsonb_agg(jsonb_build_object('account_id', account_id, 'debit', credit, 'credit', debit, 'description', description,
     'customer_id', customer_id, 'vendor_id', vendor_id, 'owner_id', owner_id))
  INTO _lines FROM public.journal_lines WHERE journal_entry_id = _entry_id;
  _new := public.post_journal_entry(COALESCE(_entry_date, CURRENT_DATE),
    COALESCE(_memo, 'Reversal of ' || _src.entry_number), 'reversal', _entry_id, _lines);
  UPDATE public.journal_entries SET status = 'reversed', reversed_by = _new WHERE id = _entry_id;
  UPDATE public.journal_entries SET reverses_entry_id = _entry_id WHERE id = _new;
  RETURN _new;
END; $$;
GRANT EXECUTE ON FUNCTION public.reverse_journal_entry(uuid, date, text) TO authenticated;

-- account balance helpers / GL views
CREATE OR REPLACE VIEW public.v_account_balances AS
SELECT a.id AS account_id, a.code, a.name, t.category, t.normal_balance,
  COALESCE(SUM(l.debit),0) AS total_debit,
  COALESCE(SUM(l.credit),0) AS total_credit,
  CASE WHEN t.normal_balance = 'debit' THEN COALESCE(SUM(l.debit),0) - COALESCE(SUM(l.credit),0)
       ELSE COALESCE(SUM(l.credit),0) - COALESCE(SUM(l.debit),0) END AS balance
FROM public.accounts a
JOIN public.account_types t ON t.id = a.account_type_id
LEFT JOIN public.journal_lines l ON l.account_id = a.id
LEFT JOIN public.journal_entries e ON e.id = l.journal_entry_id AND e.status <> 'draft'
GROUP BY a.id, a.code, a.name, t.category, t.normal_balance;
GRANT SELECT ON public.v_account_balances TO authenticated;

CREATE OR REPLACE VIEW public.v_general_ledger AS
SELECT l.id AS line_id, e.id AS entry_id, e.entry_number, e.entry_date, e.memo, e.source_type, e.source_id, e.status,
  a.id AS account_id, a.code AS account_code, a.name AS account_name, t.category,
  l.debit, l.credit, l.description, l.customer_id, l.vendor_id, l.owner_id
FROM public.journal_lines l
JOIN public.journal_entries e ON e.id = l.journal_entry_id
JOIN public.accounts a ON a.id = l.account_id
JOIN public.account_types t ON t.id = a.account_type_id;
GRANT SELECT ON public.v_general_ledger TO authenticated;