-- CRM Workflow Update: Presales → RM → KAM
-- 1. Add KAM role
-- 2. New opportunity stage groups and stages
-- 3. Empanelment form table

-- =============================================================================
-- 1. ADD KAM ROLE
-- =============================================================================
INSERT INTO user_roles (name, sort_order) VALUES ('KAM', 4)
ON CONFLICT (name) DO NOTHING;

-- =============================================================================
-- 2. OPPORTUNITY STAGE GROUPS (Presales, RM, KAM)
-- =============================================================================
CREATE TABLE IF NOT EXISTS opportunity_stage_groups (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(50) NOT NULL UNIQUE,
  sort_order INT DEFAULT 0,
  is_active BOOLEAN DEFAULT true
);

INSERT INTO opportunity_stage_groups (name, sort_order) VALUES
  ('Presales', 1),
  ('RM', 2),
  ('KAM', 3)
ON CONFLICT (name) DO NOTHING;

-- Add group_id to opportunity_stages (nullable for backward compat)
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'opportunity_stages' AND column_name = 'group_id') THEN
    ALTER TABLE opportunity_stages ADD COLUMN group_id UUID REFERENCES opportunity_stage_groups(id);
  END IF;
END $$;

-- New stages (Presales, RM, KAM pipelines)
-- Existing Cold/Email/etc remain for backward compat; new stages use higher sort_order
INSERT INTO opportunity_stages (name, sort_order) VALUES
  ('Interested', 10),
  ('Email Collection', 11),
  ('Virtual Call (Presales)', 12),
  ('Callback (Presales)', 13),
  ('Do Not Call', 14),
  ('RM - Virtual Call', 20),
  ('Proposal Sent', 21),
  ('RM - Callback', 22),
  ('Close', 23),
  ('Not Interested', 25),
  ('KAM - Interested', 30),
  ('Empanelment', 31)
ON CONFLICT (name) DO NOTHING;

-- =============================================================================
-- 3. EMPANELMENT FORM TABLE
-- =============================================================================
CREATE TABLE IF NOT EXISTS empanelment_forms (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  corporate_id UUID NOT NULL REFERENCES corporates(corporate_id) ON DELETE CASCADE,
  opportunity_id UUID REFERENCES opportunities(opportunity_id) ON DELETE SET NULL,

  -- Basic
  corporate_name VARCHAR(255),
  corporate_logo_url TEXT,
  corporate_address TEXT,

  -- Product & SLA
  product_offered VARCHAR(50) CHECK (product_offered IN ('Saventh Learn', 'Saventh Invest', 'Both')),
  sla_signing_date DATE,
  credit_rating VARCHAR(100),
  place_of_sla_signing VARCHAR(255),

  -- Signing authority
  signing_authority_name VARCHAR(255),
  signing_authority_designation VARCHAR(255),
  saventh_signing_authority_name VARCHAR(255),
  saventh_signing_authority_designation VARCHAR(255),
  third_party_signing_name VARCHAR(255),
  third_party_signing_designation VARCHAR(255),

  -- Corporate contact
  contact_person_name VARCHAR(255),
  contact_person_email VARCHAR(255),
  contact_person_phone VARCHAR(100),

  -- Financial
  stamp_duty_amount DECIMAL(15, 2),
  total_employees INT,
  employee_type VARCHAR(50) CHECK (employee_type IN ('Permanent', 'Contractual', 'Both')),
  contractual_amount DECIMAL(15, 2),

  -- Partner
  master_partner VARCHAR(255),
  master_partner_payout VARCHAR(255),

  -- Payroll
  salary_processing_date INT,
  salary_date INT,
  due_date INT,
  salary_deduction_flag VARCHAR(10) CHECK (salary_deduction_flag IN ('Yes', 'No')),

  -- HRMS / Data
  hrms_integration_or_excel VARCHAR(100),
  hrms_name VARCHAR(255),
  consultant_contact_name VARCHAR(255),
  consultant_contact_email VARCHAR(255),
  consultant_contact_phone VARCHAR(100),
  frequency_of_data VARCHAR(100),

  -- Banking
  banking_details TEXT,

  -- Documents (URLs or storage refs)
  sla_agreement_url TEXT,
  aadhar_card_url TEXT,
  pancard_available VARCHAR(10) CHECK (pancard_available IN ('Yes', 'No')),
  alternate_id_proof_url TEXT,
  login_fee_amount DECIMAL(15, 2),
  bank_withdrawal_fee_amount DECIMAL(15, 2),
  late_fee_amount DECIMAL(15, 2),

  -- Partner & ENach
  partner_revenue_share TEXT,
  enach_details TEXT,

  -- Meta
  created_by UUID REFERENCES users(user_id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  status VARCHAR(50) DEFAULT 'Draft'
);

CREATE INDEX IF NOT EXISTS idx_empanelment_corporate ON empanelment_forms(corporate_id);
CREATE INDEX IF NOT EXISTS idx_empanelment_opportunity ON empanelment_forms(opportunity_id);

-- RLS for empanelment_forms (authenticated CRM users can access)
ALTER TABLE empanelment_forms ENABLE ROW LEVEL SECURITY;
CREATE POLICY "empanelment_select" ON empanelment_forms FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "empanelment_insert" ON empanelment_forms FOR INSERT WITH CHECK (auth.role() = 'authenticated');
CREATE POLICY "empanelment_update" ON empanelment_forms FOR UPDATE USING (auth.role() = 'authenticated');
