-- Add TaxCore integration fields to quotations table
-- Quotations can be submitted to TaxCore as Proforma invoices

ALTER TABLE quotations 
ADD COLUMN IF NOT EXISTS taxcore_submission_id VARCHAR(100),
ADD COLUMN IF NOT EXISTS taxcore_submission_id_fk UUID,
ADD COLUMN IF NOT EXISTS taxcore_receipt_data JSONB,
ADD COLUMN IF NOT EXISTS taxcore_submitted_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS taxcore_verification_url TEXT,
ADD COLUMN IF NOT EXISTS taxcore_qr_code TEXT;

-- Add index for TaxCore submission lookups
CREATE INDEX IF NOT EXISTS idx_quotations_taxcore_submission ON quotations(taxcore_submission_id) 
WHERE taxcore_submission_id IS NOT NULL;

-- Comments
COMMENT ON COLUMN quotations.taxcore_submission_id IS 'Fiscal code returned from TaxCore when submitted as Proforma invoice';
COMMENT ON COLUMN quotations.taxcore_submission_id_fk IS 'Foreign key to TaxCore submissions table';
COMMENT ON COLUMN quotations.taxcore_receipt_data IS 'Full TaxCore receipt data including fiscal code, QR code, etc.';
COMMENT ON COLUMN quotations.taxcore_submitted_at IS 'Timestamp when quotation was submitted to TaxCore';
COMMENT ON COLUMN quotations.taxcore_verification_url IS 'URL to verify the TaxCore submission online';
COMMENT ON COLUMN quotations.taxcore_qr_code IS 'QR code data for fiscal receipt';

-- Update existing quotations to have NULL values (explicit for clarity)
UPDATE quotations 
SET 
    taxcore_submission_id = NULL,
    taxcore_submission_id_fk = NULL,
    taxcore_receipt_data = NULL,
    taxcore_submitted_at = NULL,
    taxcore_verification_url = NULL,
    taxcore_qr_code = NULL
WHERE taxcore_submission_id IS NULL;

RAISE NOTICE '✅ TaxCore fields added to quotations table';
RAISE NOTICE 'Quotations can now be submitted to TaxCore as Proforma invoices';
