-- Migration: Quotations and Invoices Enhancement
-- Date: 2025-10-14
-- Description: Comprehensive schema extensions for quotations, invoices, client portal, payment providers, and TaxCore integration
-- Version: 002

BEGIN;

-- ============================================================================
-- 1. QUOTATIONS TABLE EXTENSIONS
-- ============================================================================

-- Extend quotations table with missing columns
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS org_id uuid REFERENCES orgs(org_id);
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS client_id uuid REFERENCES clients(client_id);
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS line_items JSONB DEFAULT '[]'::jsonb;
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS note TEXT;
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS terms TEXT;
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS validity_days INTEGER DEFAULT 30;
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'draft';
  -- Status values: 'draft', 'sent', 'accepted', 'rejected', 'expired', 'converted'
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS total_amount DECIMAL(10,2) DEFAULT 0;
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS tax_amount DECIMAL(10,2) DEFAULT 0;
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS template_id uuid REFERENCES invoice_templates(invoice_template_id);
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS sent_at TIMESTAMPTZ;
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS expires_at TIMESTAMPTZ;
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS accepted_at TIMESTAMPTZ;
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS rejected_at TIMESTAMPTZ;
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS rejection_reason TEXT;
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS converted_invoice_id uuid REFERENCES invoices(invoice_id);
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS created_by uuid REFERENCES users(user_id);

-- Add indexes for performance
CREATE INDEX IF NOT EXISTS idx_quotations_org_id ON quotations(org_id);
CREATE INDEX IF NOT EXISTS idx_quotations_client_id ON quotations(client_id);
CREATE INDEX IF NOT EXISTS idx_quotations_status ON quotations(status);
CREATE INDEX IF NOT EXISTS idx_quotations_created_by ON quotations(created_by);
CREATE INDEX IF NOT EXISTS idx_quotations_date ON quotations(date);

-- Add comment
COMMENT ON TABLE quotations IS 'Enhanced quotations table with full workflow support, line items, and org-specific branding';

-- ============================================================================
-- 2. QUOTATION SETTINGS TABLE
-- ============================================================================

CREATE TABLE IF NOT EXISTS quotation_settings (
  quotation_setting_id  uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  org_id                uuid NOT NULL REFERENCES orgs(org_id),
  template_id           uuid REFERENCES invoice_templates(invoice_template_id),
  logo_document_id      uuid REFERENCES documents(document_id),
  number_format         varchar(50) DEFAULT 'Q-{YYYY}-{0000}',
  starting_number       INTEGER DEFAULT 1,
  current_number        INTEGER DEFAULT 1,
  validity_days         INTEGER DEFAULT 30,
  terms                 text,
  footer_text           text,
  auto_convert_to_invoice BOOLEAN DEFAULT false,
  send_as_proforma      BOOLEAN DEFAULT false,
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  UNIQUE(org_id, template_id)
);

CREATE INDEX IF NOT EXISTS idx_quotation_settings_org_id ON quotation_settings(org_id);

COMMENT ON TABLE quotation_settings IS 'Organization-specific quotation settings including branding, numbering, and workflow options';
COMMENT ON COLUMN quotation_settings.auto_convert_to_invoice IS 'Automatically convert accepted quotations to invoices';
COMMENT ON COLUMN quotation_settings.send_as_proforma IS 'Send quotations as proforma invoices to TaxCore';

-- ============================================================================
-- 3. CLIENT PORTAL ACCESS TABLES
-- ============================================================================

-- Add client role if not exists
INSERT INTO roles (role_name, description) VALUES 
  ('client', 'Client Portal Access')
ON CONFLICT (role_name) DO NOTHING;

-- Token-based access for email magic links
CREATE TABLE IF NOT EXISTS client_portal_tokens (
  token_id            uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  user_id             uuid NOT NULL REFERENCES users(user_id),
  org_id              uuid NOT NULL REFERENCES orgs(org_id),
  token_hash          text NOT NULL UNIQUE,
  purpose             text NOT NULL,
  reference_id        uuid,
  reference_table     text,
  expires_at          timestamptz NOT NULL,
  used_at             timestamptz,
  ip_address          inet,
  user_agent          text,
  created_at          timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_portal_tokens_user_id ON client_portal_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_portal_tokens_token_hash ON client_portal_tokens(token_hash);
CREATE INDEX IF NOT EXISTS idx_portal_tokens_reference ON client_portal_tokens(reference_id, reference_table);
CREATE INDEX IF NOT EXISTS idx_portal_tokens_expires_at ON client_portal_tokens(expires_at);

COMMENT ON TABLE client_portal_tokens IS 'Magic link tokens for client portal access';
COMMENT ON COLUMN client_portal_tokens.purpose IS 'Purpose: quotation_view, invoice_payment, general_access';

-- ============================================================================
-- 4. CLARIFICATION SYSTEM TABLES
-- ============================================================================

-- Clarification requests for quotations/invoices
CREATE TABLE IF NOT EXISTS clarification_requests (
  clarification_id    uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  reference_id        uuid NOT NULL,
  reference_table     text NOT NULL,
  org_id              uuid NOT NULL REFERENCES orgs(org_id),
  client_user_id      uuid NOT NULL REFERENCES users(user_id),
  subject             text NOT NULL,
  message             text NOT NULL,
  status              text DEFAULT 'open',
  priority            text DEFAULT 'normal',
  created_at          timestamptz NOT NULL DEFAULT now(),
  resolved_at         timestamptz
);

CREATE INDEX IF NOT EXISTS idx_clarifications_reference ON clarification_requests(reference_id, reference_table);
CREATE INDEX IF NOT EXISTS idx_clarifications_client ON clarification_requests(client_user_id);
CREATE INDEX IF NOT EXISTS idx_clarifications_status ON clarification_requests(status);
CREATE INDEX IF NOT EXISTS idx_clarifications_org_id ON clarification_requests(org_id);

COMMENT ON TABLE clarification_requests IS 'Client requests for clarification on quotations or invoices';
COMMENT ON COLUMN clarification_requests.status IS 'Status: open, responded, resolved, closed';
COMMENT ON COLUMN clarification_requests.priority IS 'Priority: low, normal, high';

-- Responses to clarifications
CREATE TABLE IF NOT EXISTS clarification_responses (
  response_id         uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  clarification_id    uuid NOT NULL REFERENCES clarification_requests(clarification_id) ON DELETE CASCADE,
  user_id             uuid NOT NULL REFERENCES users(user_id),
  message             text NOT NULL,
  is_internal_note    BOOLEAN DEFAULT false,
  created_at          timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_clarification_responses_clarification ON clarification_responses(clarification_id);
CREATE INDEX IF NOT EXISTS idx_clarification_responses_user ON clarification_responses(user_id);

COMMENT ON TABLE clarification_responses IS 'Responses to clarification requests from staff or clients';
COMMENT ON COLUMN clarification_responses.is_internal_note IS 'Internal staff notes not visible to client';

-- ============================================================================
-- 5. QUOTATION ACTIONS AUDIT LOG
-- ============================================================================

CREATE TABLE IF NOT EXISTS quotation_actions (
  action_id           uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  quotation_id        uuid NOT NULL REFERENCES quotations(quotation_id) ON DELETE CASCADE,
  user_id             uuid REFERENCES users(user_id),
  org_id              uuid NOT NULL REFERENCES orgs(org_id),
  action_type         text NOT NULL,
  notes               text,
  metadata            jsonb,
  ip_address          inet,
  user_agent          text,
  created_at          timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_quotation_actions_quotation_id ON quotation_actions(quotation_id);
CREATE INDEX IF NOT EXISTS idx_quotation_actions_user_id ON quotation_actions(user_id);
CREATE INDEX IF NOT EXISTS idx_quotation_actions_type ON quotation_actions(action_type);
CREATE INDEX IF NOT EXISTS idx_quotation_actions_created_at ON quotation_actions(created_at);

COMMENT ON TABLE quotation_actions IS 'Audit trail for all quotation actions';
COMMENT ON COLUMN quotation_actions.action_type IS 'Action types: created, sent, viewed, accepted, rejected, expired, converted, clarification_requested';

-- ============================================================================
-- 6. PAYMENT PROVIDER CONFIGURATION
-- ============================================================================

-- Payment provider configurations per organization
CREATE TABLE IF NOT EXISTS payment_providers (
  provider_id           uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  org_id                uuid NOT NULL REFERENCES orgs(org_id),
  provider_name         text NOT NULL,
  provider_config       jsonb NOT NULL,
  is_active             BOOLEAN DEFAULT true,
  is_default            BOOLEAN DEFAULT false,
  supported_currencies  text[] DEFAULT ARRAY['FJD'],
  minimum_amount        DECIMAL(10,2),
  maximum_amount        DECIMAL(10,2),
  transaction_fee_type  text,
  transaction_fee_value DECIMAL(10,4),
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  UNIQUE(org_id, provider_name)
);

CREATE INDEX IF NOT EXISTS idx_payment_providers_org_id ON payment_providers(org_id);
CREATE INDEX IF NOT EXISTS idx_payment_providers_active ON payment_providers(is_active);

COMMENT ON TABLE payment_providers IS 'Payment provider configurations per organization';
COMMENT ON COLUMN payment_providers.provider_name IS 'Provider: MPaisa, Digicel, Stripe, BankTransfer';
COMMENT ON COLUMN payment_providers.provider_config IS 'Encrypted credentials and API configuration';
COMMENT ON COLUMN payment_providers.transaction_fee_type IS 'Fee type: percentage, fixed, none';

-- Client-specific payment method restrictions
CREATE TABLE IF NOT EXISTS client_payment_restrictions (
  restriction_id        uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  client_user_id        uuid NOT NULL REFERENCES users(user_id),
  org_id                uuid NOT NULL REFERENCES orgs(org_id),
  allowed_providers     text[],
  blocked_providers     text[],
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  UNIQUE(client_user_id, org_id)
);

CREATE INDEX IF NOT EXISTS idx_client_restrictions_client ON client_payment_restrictions(client_user_id);
CREATE INDEX IF NOT EXISTS idx_client_restrictions_org ON client_payment_restrictions(org_id);

COMMENT ON TABLE client_payment_restrictions IS 'Client-specific payment method restrictions';
COMMENT ON COLUMN client_payment_restrictions.allowed_providers IS 'NULL = all allowed, else specific provider list';

-- ============================================================================
-- 7. PAYMENT TRANSACTIONS LOG
-- ============================================================================

-- Payment transaction lifecycle tracking
CREATE TABLE IF NOT EXISTS payment_transactions (
  transaction_id        uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  payment_id            uuid REFERENCES payments(payment_id),
  invoice_id            uuid REFERENCES invoices(invoice_id),
  quotation_id          uuid REFERENCES quotations(quotation_id),
  client_user_id        uuid NOT NULL REFERENCES users(user_id),
  org_id                uuid NOT NULL REFERENCES orgs(org_id),
  provider_id           uuid NOT NULL REFERENCES payment_providers(provider_id),
  provider_transaction_id text,
  amount                DECIMAL(10,2) NOT NULL,
  currency              text DEFAULT 'FJD',
  transaction_fee       DECIMAL(10,2) DEFAULT 0,
  net_amount            DECIMAL(10,2),
  status                text NOT NULL DEFAULT 'pending',
  payment_method_details jsonb,
  provider_response     jsonb,
  error_message         text,
  retry_count           INTEGER DEFAULT 0,
  metadata              jsonb,
  created_at            timestamptz NOT NULL DEFAULT now(),
  completed_at          timestamptz,
  refunded_at           timestamptz
);

CREATE INDEX IF NOT EXISTS idx_payment_txn_payment_id ON payment_transactions(payment_id);
CREATE INDEX IF NOT EXISTS idx_payment_txn_invoice_id ON payment_transactions(invoice_id);
CREATE INDEX IF NOT EXISTS idx_payment_txn_quotation_id ON payment_transactions(quotation_id);
CREATE INDEX IF NOT EXISTS idx_payment_txn_client ON payment_transactions(client_user_id);
CREATE INDEX IF NOT EXISTS idx_payment_txn_org ON payment_transactions(org_id);
CREATE INDEX IF NOT EXISTS idx_payment_txn_status ON payment_transactions(status);
CREATE INDEX IF NOT EXISTS idx_payment_txn_provider_ref ON payment_transactions(provider_transaction_id);
CREATE INDEX IF NOT EXISTS idx_payment_txn_created_at ON payment_transactions(created_at);

COMMENT ON TABLE payment_transactions IS 'Payment transaction lifecycle tracking for all payment methods';
COMMENT ON COLUMN payment_transactions.status IS 'Status: pending, processing, completed, failed, refunded, cancelled';
COMMENT ON COLUMN payment_transactions.quotation_id IS 'For advance payments on quotations';

-- ============================================================================
-- 8. PAYMENT WEBHOOKS LOG
-- ============================================================================

-- Webhook events from payment providers
CREATE TABLE IF NOT EXISTS payment_webhooks (
  webhook_id            uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  provider_name         text NOT NULL,
  event_type            text NOT NULL,
  payload               jsonb NOT NULL,
  headers               jsonb,
  processed             BOOLEAN DEFAULT false,
  transaction_id        uuid REFERENCES payment_transactions(transaction_id),
  error_message         text,
  retry_count           INTEGER DEFAULT 0,
  created_at            timestamptz NOT NULL DEFAULT now(),
  processed_at          timestamptz
);

CREATE INDEX IF NOT EXISTS idx_webhooks_provider ON payment_webhooks(provider_name);
CREATE INDEX IF NOT EXISTS idx_webhooks_processed ON payment_webhooks(processed);
CREATE INDEX IF NOT EXISTS idx_webhooks_transaction ON payment_webhooks(transaction_id);
CREATE INDEX IF NOT EXISTS idx_webhooks_created_at ON payment_webhooks(created_at);

COMMENT ON TABLE payment_webhooks IS 'Webhook events from payment providers for async status updates';

-- ============================================================================
-- 9. INVOICE EXTENSIONS FOR TAXCORE
-- ============================================================================

-- Add TaxCore fields to invoices table
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS taxcore_submission_id text;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS taxcore_receipt_data jsonb;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS taxcore_submitted_at timestamptz;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS taxcore_verification_url text;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS taxcore_qr_code text;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS is_proforma BOOLEAN DEFAULT false;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS is_advance_payment BOOLEAN DEFAULT false;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS advance_payment_for_invoice_id uuid REFERENCES invoices(invoice_id);
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS is_refund BOOLEAN DEFAULT false;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS refund_for_invoice_id uuid REFERENCES invoices(invoice_id);
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS org_id uuid REFERENCES orgs(org_id);

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_invoices_taxcore_submission ON invoices(taxcore_submission_id);
CREATE INDEX IF NOT EXISTS idx_invoices_org_id ON invoices(org_id);
CREATE INDEX IF NOT EXISTS idx_invoices_is_proforma ON invoices(is_proforma);
CREATE INDEX IF NOT EXISTS idx_invoices_advance_payment_for ON invoices(advance_payment_for_invoice_id);
CREATE INDEX IF NOT EXISTS idx_invoices_refund_for ON invoices(refund_for_invoice_id);

COMMENT ON COLUMN invoices.is_proforma IS 'Proforma invoice from quotation sent to TaxCore';
COMMENT ON COLUMN invoices.is_advance_payment IS 'Advance payment invoice';
COMMENT ON COLUMN invoices.is_refund IS 'Refund invoice';

-- ============================================================================
-- 10. BALANCE UPDATE TRIGGERS
-- ============================================================================

-- Auto-update customer balance on payment changes
CREATE OR REPLACE FUNCTION update_customer_balance()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    -- Decrease balance on new payment (payment reduces amount owed)
    UPDATE customers SET
      balance = balance - NEW.total_amount,
      updatedAt = now()
    WHERE customer_id = (
      SELECT c.customer_id 
      FROM invoices i
      JOIN customers c ON i.reference_id = c.customer_id AND i.reference_table = 'customers'
      WHERE i.invoice_id = NEW.invoice_id
      LIMIT 1
    );
  ELSIF TG_OP = 'UPDATE' THEN
    -- Adjust balance if amount changed
    UPDATE customers SET
      balance = balance + OLD.total_amount - NEW.total_amount,
      updatedAt = now()
    WHERE customer_id = (
      SELECT c.customer_id 
      FROM invoices i
      JOIN customers c ON i.reference_id = c.customer_id AND i.reference_table = 'customers'
      WHERE i.invoice_id = NEW.invoice_id
      LIMIT 1
    );
  ELSIF TG_OP = 'DELETE' THEN
    -- Restore balance on payment deletion
    UPDATE customers SET
      balance = balance + OLD.total_amount,
      updatedAt = now()
    WHERE customer_id = (
      SELECT c.customer_id 
      FROM invoices i
      JOIN customers c ON i.reference_id = c.customer_id AND i.reference_table = 'customers'
      WHERE i.invoice_id = OLD.invoice_id
      LIMIT 1
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_update_customer_balance ON payments;
CREATE TRIGGER trg_update_customer_balance
AFTER INSERT OR UPDATE OR DELETE ON payments
FOR EACH ROW EXECUTE FUNCTION update_customer_balance();

-- Auto-update invoice paid amount and status
CREATE OR REPLACE FUNCTION update_invoice_amount_paid()
RETURNS TRIGGER AS $$
DECLARE
  v_invoice_id uuid;
  v_total_paid DECIMAL(10,2);
  v_invoice_amount DECIMAL(10,2);
BEGIN
  -- Get invoice_id from new or old record
  v_invoice_id := COALESCE(NEW.invoice_id, OLD.invoice_id);
  
  -- Calculate total paid
  SELECT COALESCE(SUM(total_amount), 0) INTO v_total_paid
  FROM payments 
  WHERE invoice_id = v_invoice_id AND status = 'completed';
  
  -- Get invoice amount
  SELECT amount INTO v_invoice_amount
  FROM invoices
  WHERE invoice_id = v_invoice_id;
  
  -- Update invoice
  UPDATE invoices SET
    amount_paid = v_total_paid,
    status = CASE 
      WHEN v_total_paid >= v_invoice_amount THEN 'paid'
      WHEN v_total_paid > 0 THEN 'partially_paid'
      ELSE 'unpaid'
    END,
    updatedAt = now()
  WHERE invoice_id = v_invoice_id;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_update_invoice_amount_paid ON payments;
CREATE TRIGGER trg_update_invoice_amount_paid
AFTER INSERT OR UPDATE OR DELETE ON payments
FOR EACH ROW EXECUTE FUNCTION update_invoice_amount_paid();

-- Auto-update quotation expiry
CREATE OR REPLACE FUNCTION set_quotation_expiry()
RETURNS TRIGGER AS $$
BEGIN
  -- Set expiry date when quotation is sent
  IF NEW.sent_at IS NOT NULL AND OLD.sent_at IS NULL THEN
    NEW.expires_at := NEW.sent_at + (NEW.validity_days || ' days')::interval;
  END IF;
  
  -- Auto-expire if past expiry date
  IF NEW.expires_at IS NOT NULL AND NEW.expires_at < now() AND NEW.status IN ('draft', 'sent') THEN
    NEW.status := 'expired';
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_set_quotation_expiry ON quotations;
CREATE TRIGGER trg_set_quotation_expiry
BEFORE INSERT OR UPDATE ON quotations
FOR EACH ROW EXECUTE FUNCTION set_quotation_expiry();

-- ============================================================================
-- 11. REPORTING VIEWS
-- ============================================================================

-- Invoice aging report
CREATE OR REPLACE VIEW invoice_aging AS
SELECT
  i.invoice_id,
  i.invoice_number,
  i.org_id,
  o.name AS org_name,
  COALESCE(c.name, cl.name) AS client_name,
  c.customer_id,
  i.invoice_date,
  i.amount,
  COALESCE(i.amount_paid, 0) AS amount_paid,
  (i.amount - COALESCE(i.amount_paid, 0)) AS outstanding_balance,
  EXTRACT(DAY FROM now() - i.invoice_date)::INTEGER AS days_outstanding,
  CASE
    WHEN i.status = 'paid' THEN 'paid'
    WHEN EXTRACT(DAY FROM now() - i.invoice_date) <= 30 THEN 'current'
    WHEN EXTRACT(DAY FROM now() - i.invoice_date) <= 60 THEN '30_days'
    WHEN EXTRACT(DAY FROM now() - i.invoice_date) <= 90 THEN '60_days'
    ELSE '90_plus_days'
  END AS aging_bracket,
  i.status
FROM invoices i
LEFT JOIN orgs o ON i.org_id = o.org_id
LEFT JOIN customers c ON i.reference_id = c.customer_id AND i.reference_table = 'customers'
LEFT JOIN clients cl ON i.reference_id = cl.client_id AND i.reference_table = 'clients'
WHERE i.isDeleted = FALSE;

COMMENT ON VIEW invoice_aging IS 'Invoice aging analysis with outstanding balances by aging bracket';

-- Quotation pipeline report
CREATE OR REPLACE VIEW quotation_pipeline AS
SELECT
  q.quotation_id,
  q.quotation_number,
  q.org_id,
  o.name AS org_name,
  u.name AS client_name,
  q.date AS quotation_date,
  q.total_amount,
  q.status,
  q.validity_days,
  q.expires_at,
  CASE 
    WHEN q.status = 'converted' THEN 'Converted to Invoice'
    WHEN q.status = 'expired' THEN 'Expired'
    WHEN q.expires_at < now() THEN 'Expired (Not Updated)'
    WHEN q.status = 'sent' THEN 'Awaiting Response'
    ELSE INITCAP(q.status)
  END AS pipeline_stage,
  EXTRACT(DAY FROM now() - q.date)::INTEGER AS days_since_created,
  CASE 
    WHEN q.sent_at IS NOT NULL THEN EXTRACT(DAY FROM now() - q.sent_at)::INTEGER
    ELSE NULL
  END AS days_since_sent,
  q.converted_invoice_id
FROM quotations q
LEFT JOIN orgs o ON q.org_id = o.org_id
LEFT JOIN users u ON q.client_id = (
  SELECT user_id FROM clients WHERE client_id = q.client_id LIMIT 1
)
WHERE q.isDeleted = FALSE;

COMMENT ON VIEW quotation_pipeline IS 'Quotation pipeline analysis with status and aging';

-- Payment transaction summary
CREATE OR REPLACE VIEW payment_transaction_summary AS
SELECT
  pt.transaction_id,
  pt.org_id,
  o.name AS org_name,
  u.name AS client_name,
  i.invoice_number,
  pp.provider_name,
  pt.amount,
  pt.transaction_fee,
  pt.net_amount,
  pt.currency,
  pt.status,
  pt.created_at,
  pt.completed_at,
  CASE 
    WHEN pt.completed_at IS NOT NULL THEN 
      EXTRACT(EPOCH FROM (pt.completed_at - pt.created_at))/60 
    ELSE NULL
  END AS processing_time_minutes,
  pt.error_message
FROM payment_transactions pt
LEFT JOIN orgs o ON pt.org_id = o.org_id
LEFT JOIN users u ON pt.client_user_id = u.user_id
LEFT JOIN invoices i ON pt.invoice_id = i.invoice_id
LEFT JOIN payment_providers pp ON pt.provider_id = pp.provider_id;

COMMENT ON VIEW payment_transaction_summary IS 'Payment transaction summary with provider and status information';

-- Client balance summary
CREATE OR REPLACE VIEW client_balance_summary AS
SELECT
  c.customer_id,
  c.name AS client_name,
  c.email,
  c.phone_number,
  c.org_id,
  o.name AS org_name,
  c.balance AS current_balance,
  COUNT(DISTINCT i.invoice_id) AS total_invoices,
  COUNT(DISTINCT i.invoice_id) FILTER (WHERE i.status = 'unpaid') AS unpaid_invoices,
  COUNT(DISTINCT i.invoice_id) FILTER (WHERE i.status = 'paid') AS paid_invoices,
  COALESCE(SUM(i.amount) FILTER (WHERE i.status IN ('unpaid', 'partially_paid')), 0) AS total_outstanding,
  COALESCE(SUM(p.total_amount), 0) AS total_paid,
  MAX(p.payment_date) AS last_payment_date
FROM customers c
LEFT JOIN orgs o ON c.org_id = o.org_id
LEFT JOIN invoices i ON c.customer_id = i.reference_id AND i.reference_table = 'customers' AND i.isDeleted = FALSE
LEFT JOIN payments p ON i.invoice_id = p.invoice_id AND p.status = 'completed'
GROUP BY c.customer_id, c.name, c.email, c.phone_number, c.org_id, o.name, c.balance;

COMMENT ON VIEW client_balance_summary IS 'Client balance summary with invoice and payment statistics';

-- ============================================================================
-- 12. ADD TO TENANT_TABLES FOR SYNC
-- ============================================================================

-- Register new tables for sync
INSERT INTO tenant_tables (table_name, sync_enabled) VALUES
  ('quotation_settings', TRUE),
  ('client_portal_tokens', TRUE),
  ('clarification_requests', TRUE),
  ('clarification_responses', TRUE),
  ('quotation_actions', TRUE),
  ('payment_providers', TRUE),
  ('client_payment_restrictions', TRUE),
  ('payment_transactions', TRUE),
  ('payment_webhooks', TRUE)
ON CONFLICT (table_name) DO UPDATE SET sync_enabled = TRUE;

-- ============================================================================
-- MIGRATION COMPLETE
-- ============================================================================

COMMIT;

-- Verification queries
SELECT 'Migration 002 completed successfully' AS status;
SELECT COUNT(*) AS new_quotation_columns FROM information_schema.columns WHERE table_name = 'quotations' AND column_name IN ('org_id', 'line_items', 'status');
SELECT COUNT(*) AS new_tables FROM information_schema.tables WHERE table_name IN ('quotation_settings', 'client_portal_tokens', 'clarification_requests', 'payment_providers', 'payment_transactions', 'payment_webhooks');
SELECT COUNT(*) AS new_views FROM information_schema.views WHERE table_name IN ('invoice_aging', 'quotation_pipeline', 'payment_transaction_summary', 'client_balance_summary');
