-- lead_requests backs the "professional sends a request to contact a
-- customer's requirement" flow (LeadRequestRepository, crates/db/src/models/
-- lead_request.rs; send_lead_request in crates/contracts/src/profession_shared.rs;
-- list_requests/approve_request/reject_request in apps/customers/src/handlers.rs).
-- The table was only ever defined in disabled (.skip) migrations, and those
-- versions didn't match the columns the current Rust code actually reads/writes
-- (professional_user_id, customer_user_id, remarks, requested_at, resolved_at) —
-- so this flow has never worked. Schema below matches the live Rust code exactly.
-- 20260317195000_profession_specific_profiles.up.sql (already deployed,
-- earlier in the chain) now self-creates a minimal version of this table —
-- CREATE TABLE IF NOT EXISTS here is a no-op in that case, so every column
-- is also added via ADD COLUMN IF NOT EXISTS below to converge regardless
-- of which migration actually created the table.
CREATE TABLE IF NOT EXISTS lead_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    lead_id UUID,
    user_role_profile_id UUID,
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
    tracecoins_reserved INTEGER NOT NULL DEFAULT 25,
    remarks TEXT,
    expires_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '7 days'),
    requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    resolved_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE lead_requests
    ADD COLUMN IF NOT EXISTS lead_id UUID,
    ADD COLUMN IF NOT EXISTS user_role_profile_id UUID,
    ADD COLUMN IF NOT EXISTS professional_user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    ADD COLUMN IF NOT EXISTS customer_user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    ADD COLUMN IF NOT EXISTS status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
    ADD COLUMN IF NOT EXISTS tracecoins_reserved INTEGER NOT NULL DEFAULT 25,
    ADD COLUMN IF NOT EXISTS remarks TEXT,
    ADD COLUMN IF NOT EXISTS expires_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '7 days'),
    ADD COLUMN IF NOT EXISTS requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ADD COLUMN IF NOT EXISTS resolved_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'lead_requests_lead_id_professional_user_id_key') THEN
    ALTER TABLE lead_requests ADD CONSTRAINT lead_requests_lead_id_professional_user_id_key UNIQUE (lead_id, professional_user_id);
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_lead_requests_lead_id ON lead_requests(lead_id);
CREATE INDEX IF NOT EXISTS idx_lead_requests_user_role_profile_id ON lead_requests(user_role_profile_id);
CREATE INDEX IF NOT EXISTS idx_lead_requests_professional_user_id ON lead_requests(professional_user_id);
CREATE INDEX IF NOT EXISTS idx_lead_requests_customer_user_id ON lead_requests(customer_user_id);
CREATE INDEX IF NOT EXISTS idx_lead_requests_status ON lead_requests(status);

-- Human-readable reference number (NXT-LED-YY-000001), same scheme as
-- verifications/payments/job_applications/etc.
CREATE SEQUENCE IF NOT EXISTS lead_request_number_seq;
ALTER TABLE lead_requests ADD COLUMN IF NOT EXISTS reference_number VARCHAR;

CREATE OR REPLACE FUNCTION set_lead_request_reference_number() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.reference_number IS NULL THEN
    NEW.reference_number := 'NXT-LED-' || to_char(NOW(), 'YY') || '-' || lpad(nextval('lead_request_number_seq')::text, 6, '0');
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_lead_requests_reference_number ON lead_requests;
CREATE TRIGGER trg_lead_requests_reference_number
  BEFORE INSERT ON lead_requests
  FOR EACH ROW EXECUTE FUNCTION set_lead_request_reference_number();

DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT id, created_at FROM lead_requests WHERE reference_number IS NULL ORDER BY created_at, id LOOP
    UPDATE lead_requests SET reference_number = 'NXT-LED-' || to_char(r.created_at, 'YY') || '-' || lpad(nextval('lead_request_number_seq')::text, 6, '0') WHERE id = r.id;
  END LOOP;
END $$;

ALTER TABLE lead_requests ALTER COLUMN reference_number SET NOT NULL;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'lead_requests_reference_number_key') THEN
    ALTER TABLE lead_requests ADD CONSTRAINT lead_requests_reference_number_key UNIQUE (reference_number);
  END IF;
END $$;

-- tracecoin_wallets/tracecoin_ledger (needed for the Tracecoin reservation
-- that happens when a lead request is sent) are now created earlier in the
-- chain by 20260318233000_tracecoin_ledger_immutable.up.sql — nothing left
-- to do here.
