-- Views filtering all core Autotask data to CompanyCategory ID 1 (Recurring Revenue Customer)

-- Base: recurring revenue companies
CREATE OR REPLACE VIEW v_mrr_companies AS
SELECT *
FROM companies
WHERE company_category_id = 1
  AND is_active = true;

-- Tickets for recurring revenue companies
CREATE OR REPLACE VIEW v_mrr_tickets AS
SELECT t.*
FROM tickets t
INNER JOIN v_mrr_companies c ON c.id = t.company_id
WHERE t.is_deleted = false;

-- Contacts for recurring revenue companies
CREATE OR REPLACE VIEW v_mrr_contacts AS
SELECT ct.*
FROM contacts ct
INNER JOIN v_mrr_companies c ON c.id = ct.company_id
WHERE ct.is_deleted = false;

-- Contracts for recurring revenue companies
CREATE OR REPLACE VIEW v_mrr_contracts AS
SELECT cn.*
FROM contracts cn
INNER JOIN v_mrr_companies c ON c.id = cn.company_id
WHERE cn.is_deleted = false;

-- Configuration items for recurring revenue companies
CREATE OR REPLACE VIEW v_mrr_configuration_items AS
SELECT ci.*
FROM configuration_items ci
INNER JOIN v_mrr_companies c ON c.id = ci.company_id
WHERE ci.is_deleted = false;

-- Time entries for recurring revenue companies (via ticket)
CREATE OR REPLACE VIEW v_mrr_time_entries AS
SELECT te.*
FROM time_entries te
INNER JOIN v_mrr_tickets t ON t.id = te.ticket_id;

-- Billing items for recurring revenue companies
CREATE OR REPLACE VIEW v_mrr_billing_items AS
SELECT bi.*
FROM billing_items bi
INNER JOIN v_mrr_companies c ON c.id = bi.company_id
WHERE bi.is_deleted = false;