-- Companies, company-scoped users, portal channel, message source, ticket activity ordering

CREATE TABLE companies (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name             VARCHAR(500) NOT NULL,
  normalized_name  VARCHAR(500) NOT NULL UNIQUE,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_companies_created_at ON companies (created_at DESC);

ALTER TABLE users ADD COLUMN company_id UUID REFERENCES companies(id);

ALTER TABLE contacts ADD COLUMN company_id UUID REFERENCES companies(id);

CREATE INDEX idx_contacts_company_id ON contacts (company_id) WHERE company_id IS NOT NULL;

-- PG 15+: safe re-run
ALTER TYPE user_role ADD VALUE IF NOT EXISTS 'company_user';

ALTER TABLE users ADD CONSTRAINT users_role_company_consistency
  CHECK (
    (role = 'company_user'::user_role AND company_id IS NOT NULL)
    OR (role <> 'company_user'::user_role AND company_id IS NULL)
  );

ALTER TABLE tickets ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();

UPDATE tickets t
SET updated_at = GREATEST(
  t.created_at,
  COALESCE((SELECT MAX(m.created_at) FROM messages m WHERE m.ticket_id = t.id), t.created_at)
);

CREATE INDEX idx_tickets_updated_at ON tickets (updated_at DESC);

CREATE TYPE message_source AS ENUM ('whatsapp', 'portal');

ALTER TABLE messages ADD COLUMN source message_source NOT NULL DEFAULT 'whatsapp';

-- Keep ticket.updated_at in sync when messages arrive
CREATE OR REPLACE FUNCTION messages_touch_ticket_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE tickets SET updated_at = NOW() WHERE id = NEW.ticket_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tr_messages_touch_ticket_updated_at ON messages;
CREATE TRIGGER tr_messages_touch_ticket_updated_at
  AFTER INSERT ON messages
  FOR EACH ROW EXECUTE FUNCTION messages_touch_ticket_updated_at();

-- Synthetic web chat channel (no Baileys — always available; settings follow primary WhatsApp channel in API)
INSERT INTO channels (name, phone_number, status, last_connected_at)
SELECT 'Web chat', 'portal:system', 'connected'::channel_status, NOW()
WHERE NOT EXISTS (SELECT 1 FROM channels WHERE phone_number = 'portal:system');
