-- ============================================================================
-- Phase 6: TaxCore Enhancement - Database Schema
-- ============================================================================
-- Created: 2025-01-17
-- Purpose: Add TaxCore submission tracking, org-specific configuration,
--          and retry queue for automatic invoice submission
-- ============================================================================

-- ============================================================================
-- Table 1: taxcore_configurations
-- ============================================================================
-- Store org-specific TaxCore configurations and certificates
-- Each organization can have its own TaxCore credentials and settings

CREATE TABLE IF NOT EXISTS taxcore_configurations (
  taxcore_config_id       uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  org_id                  uuid NOT NULL REFERENCES orgs(org_id) ON DELETE CASCADE,
  
  -- Certificate paths (org-specific or shared)
  certificate_path        text NOT NULL,
  certificate_password    text NOT NULL, -- Encrypted at application layer
  
  -- TaxCore API configuration
  api_base_url            text NOT NULL DEFAULT 'https://vsdc-uat.frcs.org.fj',
  api_timeout_seconds     integer NOT NULL DEFAULT 30,
  
  -- Business details (from TaxCore registration)
  tin                     text NOT NULL,
  business_name           text NOT NULL,
  location_name           text DEFAULT NULL,
  mrc                     text DEFAULT NULL,
  pos_number              text DEFAULT 'POS-01',
  
  -- Settings
  is_active               boolean NOT NULL DEFAULT true,
  auto_submit             boolean NOT NULL DEFAULT true, -- Auto-submit invoices?
  submit_delay_seconds    integer DEFAULT 0, -- Delay before submission
  
  -- Status tracking
  last_submission_at      timestamptz DEFAULT NULL,
  certificate_expires_at  date DEFAULT NULL,
  certificate_last_validated_at timestamptz DEFAULT NULL,
  
  created_at              timestamptz NOT NULL DEFAULT now(),
  updated_at              timestamptz NOT NULL DEFAULT now(),
  created_by              uuid REFERENCES users(user_id),
  
  CONSTRAINT uq_taxcore_config_org UNIQUE (org_id)
);

CREATE INDEX idx_taxcore_config_active ON taxcore_configurations(org_id, is_active);
CREATE INDEX idx_taxcore_config_expiry ON taxcore_configurations(certificate_expires_at) 
  WHERE certificate_expires_at IS NOT NULL;

COMMENT ON TABLE taxcore_configurations IS 'Organization-specific TaxCore API configurations and certificates. Each org can have separate credentials for tax compliance.';
COMMENT ON COLUMN taxcore_configurations.certificate_password IS 'Encrypted PFX certificate password. Must be decrypted before use.';
COMMENT ON COLUMN taxcore_configurations.auto_submit IS 'If true, invoices will be automatically submitted to TaxCore when finalized.';
COMMENT ON COLUMN taxcore_configurations.submit_delay_seconds IS 'Optional delay before submission (allows for last-minute corrections).';

-- ============================================================================
-- Table 2: taxcore_submissions
-- ============================================================================
-- Track ALL TaxCore submissions (success and failures)
-- Provides audit trail and enables retry logic

CREATE TABLE IF NOT EXISTS taxcore_submissions (
  submission_id           uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  org_id                  uuid NOT NULL REFERENCES orgs(org_id),
  
  -- Source tracking (what triggered this submission)
  reference_table         text NOT NULL, -- 'sales_receipts', 'invoices', 'quotations'
  reference_id            uuid NOT NULL, -- ID from source table
  
  -- Submission details
  submission_type         text NOT NULL, -- 'Normal', 'Advance', 'Proforma', 'Refund', 'Training', 'Copy'
  invoice_number          text DEFAULT NULL,
  total_amount            numeric(10,2) NOT NULL,
  
  -- Request/Response (full audit trail)
  request_payload         jsonb NOT NULL,
  response_payload        jsonb DEFAULT NULL,
  
  -- TaxCore response fields (extracted for easy querying)
  taxcore_invoice_number  text DEFAULT NULL,
  taxcore_qr_code         text DEFAULT NULL,
  taxcore_verification_url text DEFAULT NULL,
  taxcore_journal         text DEFAULT NULL,
  taxcore_signature       text DEFAULT NULL,
  
  -- Status tracking
  status                  text NOT NULL DEFAULT 'pending', 
    CHECK (status IN ('pending', 'submitted', 'success', 'failed', 'retrying')),
  attempts                integer NOT NULL DEFAULT 0,
  last_attempt_at         timestamptz DEFAULT NULL,
  error_message           text DEFAULT NULL,
  http_status_code        integer DEFAULT NULL,
  
  -- Metadata
  submitted_by            uuid REFERENCES users(user_id),
  submission_source       text DEFAULT 'system', -- 'pos', 'invoice', 'workflow', 'manual', 'retry'
  created_at              timestamptz NOT NULL DEFAULT now(),
  completed_at            timestamptz DEFAULT NULL,
  
  isDeleted               boolean NOT NULL DEFAULT false,
  updatedAt               timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_taxcore_submissions_ref ON taxcore_submissions(reference_table, reference_id);
CREATE INDEX idx_taxcore_submissions_sales_receipt ON taxcore_submissions(reference_id) 
  WHERE reference_table = 'sales_receipts';
CREATE INDEX idx_taxcore_submissions_invoice ON taxcore_submissions(reference_id) 
  WHERE reference_table = 'invoices';
CREATE INDEX idx_taxcore_submissions_status ON taxcore_submissions(status, org_id);
CREATE INDEX idx_taxcore_submissions_created ON taxcore_submissions(created_at DESC);
CREATE INDEX idx_taxcore_submissions_org ON taxcore_submissions(org_id, created_at DESC);
CREATE INDEX idx_taxcore_submissions_pending ON taxcore_submissions(status, created_at) 
  WHERE status IN ('pending', 'retrying');

COMMENT ON TABLE taxcore_submissions IS 'Audit log of all TaxCore API submissions with retry tracking. Records both successful and failed attempts.';
COMMENT ON COLUMN taxcore_submissions.reference_table IS 'Source table that triggered the submission (sales_receipts, invoices, quotations).';
COMMENT ON COLUMN taxcore_submissions.submission_type IS 'TaxCore invoice type: Normal (standard), Advance (prepayment), Proforma (quote), Refund, Training (test), Copy (duplicate).';
COMMENT ON COLUMN taxcore_submissions.submission_source IS 'How submission was triggered: pos (manual from POS), invoice (from invoice module), workflow (automatic), manual (admin retry), retry (queue worker).';

-- ============================================================================
-- Table 3: taxcore_queue
-- ============================================================================
-- Queue for failed submissions that need retry
-- Implements exponential backoff retry strategy

CREATE TABLE IF NOT EXISTS taxcore_queue (
  queue_id                uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  submission_id           uuid NOT NULL REFERENCES taxcore_submissions(submission_id) ON DELETE CASCADE,
  org_id                  uuid NOT NULL REFERENCES orgs(org_id),
  
  -- Retry logic
  retry_count             integer NOT NULL DEFAULT 0,
  max_retries             integer NOT NULL DEFAULT 5,
  next_retry_at           timestamptz NOT NULL,
  backoff_multiplier      numeric(3,1) NOT NULL DEFAULT 2.0, -- Exponential backoff (2^n * base_delay)
  base_delay_minutes      integer NOT NULL DEFAULT 5,
  
  -- Priority (1=highest, 10=lowest)
  priority                integer NOT NULL DEFAULT 5,
  
  -- Status
  status                  text NOT NULL DEFAULT 'queued', 
    CHECK (status IN ('queued', 'processing', 'completed', 'failed', 'cancelled')),
  locked_by               text DEFAULT NULL, -- Process/worker that's processing this
  locked_at               timestamptz DEFAULT NULL,
  last_error              text DEFAULT NULL,
  
  created_at              timestamptz NOT NULL DEFAULT now(),
  updated_at              timestamptz NOT NULL DEFAULT now(),
  processed_at            timestamptz DEFAULT NULL,
  
  isDeleted               boolean NOT NULL DEFAULT false
);

CREATE INDEX idx_taxcore_queue_next_retry ON taxcore_queue(next_retry_at, priority) 
  WHERE status = 'queued';
CREATE INDEX idx_taxcore_queue_status ON taxcore_queue(status, priority, next_retry_at);
CREATE INDEX idx_taxcore_queue_submission ON taxcore_queue(submission_id);
CREATE INDEX idx_taxcore_queue_org ON taxcore_queue(org_id, status);
CREATE INDEX idx_taxcore_queue_locked ON taxcore_queue(locked_by, locked_at) 
  WHERE status = 'processing';

COMMENT ON TABLE taxcore_queue IS 'Retry queue for failed TaxCore submissions with exponential backoff. Processed by cron worker every 5 minutes.';
COMMENT ON COLUMN taxcore_queue.backoff_multiplier IS 'Exponential backoff multiplier. Default 2.0 means delays are: 5min, 10min, 20min, 40min, 80min.';
COMMENT ON COLUMN taxcore_queue.locked_by IS 'Identifier of the worker/process currently processing this item (prevents duplicate processing).';

-- ============================================================================
-- Table 4: Add columns to existing tables
-- ============================================================================

-- Add taxcore_submission_id to sales_receipts (links back to submissions log)
ALTER TABLE sales_receipts 
ADD COLUMN IF NOT EXISTS taxcore_submission_id uuid REFERENCES taxcore_submissions(submission_id);

CREATE INDEX IF NOT EXISTS idx_sales_receipts_submission 
ON sales_receipts(taxcore_submission_id) 
WHERE taxcore_submission_id IS NOT NULL;

COMMENT ON COLUMN sales_receipts.taxcore_submission_id IS 'Links to the taxcore_submissions record that created this receipt.';

-- Add taxcore_submission_id to invoices (links back to submissions log)
ALTER TABLE invoices 
ADD COLUMN IF NOT EXISTS taxcore_submission_id_fk uuid REFERENCES taxcore_submissions(submission_id);

CREATE INDEX IF NOT EXISTS idx_invoices_submission_fk 
ON invoices(taxcore_submission_id_fk) 
WHERE taxcore_submission_id_fk IS NOT NULL;

COMMENT ON COLUMN invoices.taxcore_submission_id_fk IS 'Links to the taxcore_submissions record for this invoice. Note: taxcore_submission_id already exists as text, so using _fk suffix.';

-- ============================================================================
-- Function: Calculate next retry time with exponential backoff
-- ============================================================================

CREATE OR REPLACE FUNCTION taxcore_calculate_next_retry(
  retry_count integer,
  base_delay_minutes integer DEFAULT 5,
  backoff_multiplier numeric DEFAULT 2.0
) RETURNS timestamptz AS $$
BEGIN
  -- Exponential backoff: base_delay * (backoff_multiplier ^ retry_count)
  -- Example with defaults: 5min, 10min, 20min, 40min, 80min
  RETURN now() + (base_delay_minutes * power(backoff_multiplier, retry_count)) * interval '1 minute';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

COMMENT ON FUNCTION taxcore_calculate_next_retry IS 'Calculate next retry timestamp using exponential backoff strategy.';

-- ============================================================================
-- Function: Add submission to retry queue
-- ============================================================================

CREATE OR REPLACE FUNCTION taxcore_queue_for_retry(
  p_submission_id uuid,
  p_org_id uuid,
  p_priority integer DEFAULT 5,
  p_max_retries integer DEFAULT 5
) RETURNS uuid AS $$
DECLARE
  v_queue_id uuid;
  v_retry_count integer;
BEGIN
  -- Check if already in queue
  SELECT retry_count INTO v_retry_count
  FROM taxcore_queue
  WHERE submission_id = p_submission_id 
    AND status IN ('queued', 'processing')
  LIMIT 1;
  
  IF FOUND THEN
    -- Already queued, increment retry count
    v_retry_count := v_retry_count + 1;
    
    UPDATE taxcore_queue
    SET retry_count = v_retry_count,
        next_retry_at = taxcore_calculate_next_retry(v_retry_count),
        status = 'queued',
        locked_by = NULL,
        locked_at = NULL,
        updated_at = now()
    WHERE submission_id = p_submission_id
      AND status IN ('queued', 'processing')
    RETURNING queue_id INTO v_queue_id;
  ELSE
    -- New entry in queue
    INSERT INTO taxcore_queue (
      submission_id,
      org_id,
      retry_count,
      max_retries,
      next_retry_at,
      priority,
      status
    ) VALUES (
      p_submission_id,
      p_org_id,
      0,
      p_max_retries,
      taxcore_calculate_next_retry(0),
      p_priority,
      'queued'
    )
    RETURNING queue_id INTO v_queue_id;
  END IF;
  
  RETURN v_queue_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION taxcore_queue_for_retry IS 'Add or update a submission in the retry queue with automatic backoff calculation.';

-- ============================================================================
-- Trigger: Auto-update timestamps
-- ============================================================================

CREATE OR REPLACE FUNCTION update_taxcore_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_taxcore_config_timestamp
BEFORE UPDATE ON taxcore_configurations
FOR EACH ROW EXECUTE FUNCTION update_taxcore_timestamp();

CREATE TRIGGER trg_taxcore_submissions_timestamp
BEFORE UPDATE ON taxcore_submissions
FOR EACH ROW EXECUTE FUNCTION update_taxcore_timestamp();

CREATE TRIGGER trg_taxcore_queue_timestamp
BEFORE UPDATE ON taxcore_queue
FOR EACH ROW EXECUTE FUNCTION update_taxcore_timestamp();

-- ============================================================================
-- View: TaxCore Submission Summary
-- ============================================================================
-- Convenient view for monitoring TaxCore submissions

CREATE OR REPLACE VIEW taxcore_submission_summary AS
SELECT 
  ts.submission_id,
  ts.org_id,
  o.name as org_name,
  ts.reference_table,
  ts.reference_id,
  ts.submission_type,
  ts.invoice_number,
  ts.total_amount,
  ts.status,
  ts.attempts,
  ts.taxcore_invoice_number,
  ts.submitted_by,
  u.full_name as submitted_by_name,
  ts.submission_source,
  ts.created_at,
  ts.completed_at,
  ts.error_message,
  tq.retry_count,
  tq.next_retry_at,
  tq.status as queue_status,
  CASE 
    WHEN ts.status = 'success' THEN 'Success'
    WHEN ts.status = 'failed' AND tq.retry_count >= tq.max_retries THEN 'Failed (Max Retries)'
    WHEN ts.status = 'failed' AND tq.status = 'queued' THEN 'Queued for Retry'
    WHEN ts.status = 'pending' THEN 'Pending'
    WHEN ts.status = 'retrying' THEN 'Retrying'
    ELSE ts.status
  END as display_status
FROM taxcore_submissions ts
LEFT JOIN orgs o ON ts.org_id = o.org_id
LEFT JOIN users u ON ts.submitted_by = u.user_id
LEFT JOIN taxcore_queue tq ON ts.submission_id = tq.submission_id
WHERE ts.isDeleted = false
ORDER BY ts.created_at DESC;

COMMENT ON VIEW taxcore_submission_summary IS 'Summary view of TaxCore submissions with org/user details and retry status.';

-- ============================================================================
-- Sample Data: Default TaxCore Configuration (DEVELOPMENT ONLY)
-- ============================================================================
-- This is commented out by default - uncomment for development/testing
-- IMPORTANT: Update with real values before using in production

/*
INSERT INTO taxcore_configurations (
  org_id,
  certificate_path,
  certificate_password,
  api_base_url,
  tin,
  business_name,
  location_name,
  mrc,
  pos_number,
  is_active,
  auto_submit,
  certificate_expires_at
)
SELECT 
  org_id,
  '/var/www/html/TAF/FRCS_Certs_Install/LK2VRSH4-DeveloperAuthenticationCertificate.pfx',
  '2CBP6HMW', -- ENCRYPT THIS IN PRODUCTION!
  'http://devesdc.sandbox.vms.frcs.org.fj:8888/20f351f3-9b39-4c63-b9e0-d8a00b6e93fb/api',
  '8000000001', -- Replace with real TIN
  name,
  'Main Branch',
  'MRC001', -- Replace with real MRC
  'POS-01',
  true,
  false, -- Start with manual submission until tested
  '2026-12-31'
FROM orgs
WHERE org_id = (SELECT org_id FROM orgs ORDER BY created_at LIMIT 1)
ON CONFLICT (org_id) DO NOTHING;
*/

-- ============================================================================
-- Migration Complete
-- ============================================================================
-- Next steps:
-- 1. Execute this migration: psql -d tafdb -f migrations/phase6_taxcore_enhancement.sql
-- 2. Create TaxCoreController.php for API endpoints
-- 3. Update Node.js taxcore-service.js with /record-submission endpoint
-- 4. Create retry worker cron job
-- ============================================================================

-- Verify tables created
DO $$
BEGIN
  RAISE NOTICE 'Phase 6 TaxCore Enhancement schema created successfully!';
  RAISE NOTICE 'Tables created: taxcore_configurations, taxcore_submissions, taxcore_queue';
  RAISE NOTICE 'Functions created: taxcore_calculate_next_retry, taxcore_queue_for_retry';
  RAISE NOTICE 'View created: taxcore_submission_summary';
  RAISE NOTICE '';
  RAISE NOTICE 'Next steps:';
  RAISE NOTICE '1. Review and uncomment sample data section if needed';
  RAISE NOTICE '2. Configure organization TaxCore credentials';
  RAISE NOTICE '3. Proceed with Task 2: Node.js Service Enhancement';
END $$;
