CREATE TABLE services_catalog (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  sat_product_key TEXT,
  sat_unit_key TEXT DEFAULT 'E48',
  tax_rate DECIMAL(4,2) NOT NULL DEFAULT 0.16,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE invoices (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  patient_id UUID NOT NULL REFERENCES patients(id),
  invoice_number TEXT NOT NULL,
  cfdi_uuid TEXT,
  cfdi_status TEXT NOT NULL DEFAULT 'draft' CHECK (cfdi_status IN ('draft','stamped','cancelled')),
  cfdi_xml_url TEXT,
  cfdi_pdf_url TEXT,
  uso_cfdi TEXT DEFAULT 'S01',
  forma_pago TEXT DEFAULT '01',
  metodo_pago TEXT DEFAULT 'PUE',
  subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  total DECIMAL(10,2) NOT NULL DEFAULT 0,
  payment_status TEXT NOT NULL DEFAULT 'pending' CHECK (payment_status IN ('pending','partial','paid')),
  paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  due_date DATE,
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(clinic_id, invoice_number)
);

CREATE INDEX idx_invoices_patient ON invoices(patient_id);
CREATE INDEX idx_invoices_status ON invoices(clinic_id, payment_status);

CREATE TABLE invoice_items (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
  service_id UUID REFERENCES services_catalog(id),
  product_id UUID,
  description TEXT NOT NULL,
  quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
  unit_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  tax_rate DECIMAL(4,2) NOT NULL DEFAULT 0.16,
  total DECIMAL(10,2) NOT NULL DEFAULT 0
);

CREATE TABLE payments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  clinic_id UUID NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
  amount DECIMAL(10,2) NOT NULL,
  payment_method TEXT NOT NULL DEFAULT 'cash' CHECK (payment_method IN ('cash','card','transfer','other')),
  reference TEXT,
  received_by UUID REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE services_catalog ENABLE ROW LEVEL SECURITY;
CREATE POLICY "View clinic services" ON services_catalog FOR SELECT USING (clinic_id = auth.clinic_id());
CREATE POLICY "Manage clinic services" ON services_catalog FOR ALL USING (clinic_id = auth.clinic_id());

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY "View clinic invoices" ON invoices FOR SELECT USING (clinic_id = auth.clinic_id());
CREATE POLICY "Insert clinic invoices" ON invoices FOR INSERT WITH CHECK (clinic_id = auth.clinic_id());
CREATE POLICY "Update clinic invoices" ON invoices FOR UPDATE USING (clinic_id = auth.clinic_id());

ALTER TABLE invoice_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY "View invoice items" ON invoice_items FOR SELECT USING (EXISTS (SELECT 1 FROM invoices WHERE invoices.id = invoice_items.invoice_id AND invoices.clinic_id = auth.clinic_id()));
CREATE POLICY "Manage invoice items" ON invoice_items FOR ALL USING (EXISTS (SELECT 1 FROM invoices WHERE invoices.id = invoice_items.invoice_id AND invoices.clinic_id = auth.clinic_id()));

ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
CREATE POLICY "View clinic payments" ON payments FOR SELECT USING (clinic_id = auth.clinic_id());
CREATE POLICY "Insert clinic payments" ON payments FOR INSERT WITH CHECK (clinic_id = auth.clinic_id());