-- B2C CRM: structured Fact Finder (per opportunity) and post-conversion ledger lines

CREATE TABLE IF NOT EXISTS b2c_fact_finders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  opportunity_id UUID NOT NULL UNIQUE REFERENCES opportunities(opportunity_id) ON DELETE CASCADE,
  investor_name TEXT,
  age INT,
  gender TEXT,
  location TEXT,
  marital_status TEXT,
  children_note TEXT,
  monthly_salary NUMERIC(14,2),
  monthly_expenses NUMERIC(14,2),
  emis NUMERIC(14,2),
  monthly_surplus NUMERIC(14,2),
  investments_notes TEXT,
  life_insurance_note TEXT,
  health_insurance_note TEXT,
  corporate_only_insurance BOOLEAN DEFAULT false,
  tax_regime TEXT,
  tax_80c_note TEXT,
  filing_method TEXT,
  tax_followup_june BOOLEAN DEFAULT false,
  general_notes TEXT,
  created_by UUID REFERENCES users(user_id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS b2c_investor_ledger_entries (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  opportunity_id UUID NOT NULL REFERENCES opportunities(opportunity_id) ON DELETE CASCADE,
  entry_type VARCHAR(20) NOT NULL CHECK (entry_type IN ('SIP','LUMPSUM','OTHER')),
  amount NUMERIC(14,2),
  frequency TEXT,
  start_date DATE,
  aum_reported NUMERIC(14,2),
  notes TEXT,
  created_by UUID REFERENCES users(user_id),
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_b2c_fact_finders_opp ON b2c_fact_finders(opportunity_id);
CREATE INDEX IF NOT EXISTS idx_b2c_ledger_opp ON b2c_investor_ledger_entries(opportunity_id);

ALTER TABLE b2c_fact_finders ENABLE ROW LEVEL SECURITY;
ALTER TABLE b2c_investor_ledger_entries ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "rls_b2c_fact_finders" ON b2c_fact_finders;
CREATE POLICY "rls_b2c_fact_finders" ON b2c_fact_finders FOR ALL USING (auth.role() = 'authenticated');

DROP POLICY IF EXISTS "rls_b2c_ledger" ON b2c_investor_ledger_entries;
CREATE POLICY "rls_b2c_ledger" ON b2c_investor_ledger_entries FOR ALL USING (auth.role() = 'authenticated');

COMMENT ON TABLE b2c_fact_finders IS 'B2C CRM blueprint Fact Finder sections A–E, one row per opportunity';
COMMENT ON TABLE b2c_investor_ledger_entries IS 'B2C post-conversion SIP/lumpsum/AUM lines per opportunity';
