backend/database/migrations/036_invoices.sql

-- Meshkee CMS — invoices (platform / business billing)

-- ---------------------------------------------------------------------------
-- enums
-- ---------------------------------------------------------------------------
DO $$ BEGIN
  CREATE TYPE invoice_owner_scope AS ENUM ('platform', 'business');
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
  CREATE TYPE invoice_status AS ENUM (
    'draft',
    'issued',
    'paid',
    'cancelled'
  );
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

-- ---------------------------------------------------------------------------
-- invoice item templates (predefined line items)
-- ---------------------------------------------------------------------------
CREATE TABLE invoice_item_templates (
  id                BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  owner_scope       invoice_owner_scope NOT NULL,
  business_id       BIGINT,
  title             VARCHAR(255) NOT NULL,
  duration          VARCHAR(100),
  worktime          VARCHAR(100),
  description       TEXT,
  price             NUMERIC(12, 2) NOT NULL DEFAULT 0,
  discounted_price  NUMERIC(12, 2),
  sort_order        INT NOT NULL DEFAULT 0,
  is_active         BOOLEAN NOT NULL DEFAULT TRUE,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT invoice_item_templates_business_id_fkey
    FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
  CONSTRAINT invoice_item_templates_title_nonempty
    CHECK (char_length(trim(title)) > 0),
  CONSTRAINT invoice_item_templates_price_non_negative
    CHECK (price >= 0),
  CONSTRAINT invoice_item_templates_discounted_non_negative
    CHECK (discounted_price IS NULL OR discounted_price >= 0),
  CONSTRAINT invoice_item_templates_scope_business_check
    CHECK (
      (owner_scope = 'platform' AND business_id IS NULL)
      OR (owner_scope = 'business' AND business_id IS NOT NULL)
    )
);

CREATE INDEX idx_invoice_item_templates_owner_scope
  ON invoice_item_templates (owner_scope, sort_order);
CREATE INDEX idx_invoice_item_templates_business_id
  ON invoice_item_templates (business_id, sort_order)
  WHERE business_id IS NOT NULL;

CREATE TRIGGER invoice_item_templates_set_updated_at
  BEFORE UPDATE ON invoice_item_templates
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- ---------------------------------------------------------------------------
-- invoices
-- ---------------------------------------------------------------------------
CREATE TABLE invoices (
  id                   BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  business_id          BIGINT NOT NULL,
  owner_scope          invoice_owner_scope NOT NULL DEFAULT 'platform',
  issuer_business_id   BIGINT,
  status               invoice_status NOT NULL DEFAULT 'issued',
  notes                TEXT,
  issued_by            BIGINT,
  issued_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT invoices_business_id_fkey
    FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
  CONSTRAINT invoices_issuer_business_id_fkey
    FOREIGN KEY (issuer_business_id) REFERENCES businesses (id) ON DELETE SET NULL,
  CONSTRAINT invoices_issued_by_fkey
    FOREIGN KEY (issued_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT invoices_scope_issuer_check
    CHECK (
      (owner_scope = 'platform' AND issuer_business_id IS NULL)
      OR (owner_scope = 'business' AND issuer_business_id IS NOT NULL)
    )
);

CREATE INDEX idx_invoices_business_created
  ON invoices (business_id, created_at DESC);
CREATE INDEX idx_invoices_owner_scope
  ON invoices (owner_scope, created_at DESC);
CREATE INDEX idx_invoices_issuer_business_id
  ON invoices (issuer_business_id, created_at DESC)
  WHERE issuer_business_id IS NOT NULL;
CREATE INDEX idx_invoices_status
  ON invoices (status);

CREATE TRIGGER invoices_set_updated_at
  BEFORE UPDATE ON invoices
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- ---------------------------------------------------------------------------
-- invoice line items
-- ---------------------------------------------------------------------------
CREATE TABLE invoice_items (
  id                BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  invoice_id        BIGINT NOT NULL,
  template_id       BIGINT,
  title             VARCHAR(255) NOT NULL,
  duration          VARCHAR(100),
  worktime          VARCHAR(100),
  description       TEXT,
  price             NUMERIC(12, 2) NOT NULL DEFAULT 0,
  discounted_price  NUMERIC(12, 2),
  sort_order        INT NOT NULL DEFAULT 0,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT invoice_items_invoice_id_fkey
    FOREIGN KEY (invoice_id) REFERENCES invoices (id) ON DELETE CASCADE,
  CONSTRAINT invoice_items_template_id_fkey
    FOREIGN KEY (template_id) REFERENCES invoice_item_templates (id) ON DELETE SET NULL,
  CONSTRAINT invoice_items_title_nonempty
    CHECK (char_length(trim(title)) > 0),
  CONSTRAINT invoice_items_price_non_negative
    CHECK (price >= 0),
  CONSTRAINT invoice_items_discounted_non_negative
    CHECK (discounted_price IS NULL OR discounted_price >= 0)
);

CREATE INDEX idx_invoice_items_invoice_id
  ON invoice_items (invoice_id, sort_order);
CREATE INDEX idx_invoice_items_template_id
  ON invoice_items (template_id)
  WHERE template_id IS NOT NULL;

CREATE TRIGGER invoice_items_set_updated_at
  BEFORE UPDATE ON invoice_items
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- ---------------------------------------------------------------------------
-- permissions (for future business dashboard use)
-- ---------------------------------------------------------------------------
INSERT INTO permissions (name, slug, group_name, description) VALUES
  ('View invoices',   'invoices.read',   'invoices', 'View invoices'),
  ('Create invoices', 'invoices.create', 'invoices', 'Create invoices'),
  ('Update invoices', 'invoices.update', 'invoices', 'Update invoices'),
  ('Delete invoices', 'invoices.delete', 'invoices', 'Delete invoices'),
  ('View invoice templates',   'invoice_templates.read',   'invoices', 'View invoice item templates'),
  ('Manage invoice templates', 'invoice_templates.manage', 'invoices', 'Create, update, and delete invoice item templates')
ON CONFLICT (slug) DO NOTHING;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.slug LIKE 'invoices.%' OR p.slug LIKE 'invoice_templates.%'
WHERE r.slug IN ('business_owner', 'owner', 'admin')
ON CONFLICT DO NOTHING;