-- =====================================================
-- Receipt Processing System Database Tables
-- Phase 2B - Receipt Processing Infrastructure
-- =====================================================

-- Drop tables if they exist (for clean installation)

-- =====================================================
-- Retailers Table
-- Stores approved retail partners
-- =====================================================
CREATE TABLE `retailers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `brand_name` varchar(255) DEFAULT NULL,
  `identifiers` text DEFAULT NULL COMMENT 'JSON array of store identifiers, names, variations',
  `contact_info` text DEFAULT NULL COMMENT 'Store contact details',
  `address_patterns` text DEFAULT NULL COMMENT 'Common address patterns for this retailer',
  `is_active` tinyint(1) DEFAULT 1,
  `points_multiplier` decimal(3,2) DEFAULT 1.00 COMMENT 'Points multiplier for this retailer',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Receipt Uploads Table
-- Stores uploaded receipt files and basic metadata
-- =====================================================
CREATE TABLE `receipt_uploads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_size` int(11) DEFAULT NULL,
  `file_type` varchar(50) DEFAULT NULL,
  `upload_status` enum('uploaded','processing','completed','failed') DEFAULT 'uploaded',
  `processing_stage` enum('pending','ocr','validation','points_allocation','completed','failed') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_upload_status` (`upload_status`),
  KEY `idx_processing_stage` (`processing_stage`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_receipt_uploads_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Receipt OCR Data Table
-- Stores OCR extracted text and parsed data
-- =====================================================
CREATE TABLE `receipt_ocr_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_id` int(11) NOT NULL,
  `raw_ocr_text` longtext DEFAULT NULL COMMENT 'Raw text extracted from OCR',
  `extracted_data` longtext DEFAULT NULL COMMENT 'JSON formatted extracted data',
  `store_name` varchar(255) DEFAULT NULL,
  `store_address` text DEFAULT NULL,
  `receipt_date` date DEFAULT NULL,
  `receipt_time` time DEFAULT NULL,
  `total_amount` decimal(10,2) DEFAULT NULL,
  `currency` varchar(10) DEFAULT 'ZAR',
  `items_count` int(11) DEFAULT NULL,
  `pos_number` varchar(100) DEFAULT NULL COMMENT 'Point of Sale terminal number',
  `receipt_number` varchar(100) DEFAULT NULL,
  `ocr_confidence` decimal(5,2) DEFAULT NULL COMMENT 'OCR confidence score 0-100',
  `processing_status` enum('pending','completed','failed','manual_review') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_receipt_ocr` (`receipt_id`),
  KEY `idx_processing_status` (`processing_status`),
  KEY `idx_receipt_date` (`receipt_date`),
  KEY `idx_store_name` (`store_name`),
  KEY `idx_total_amount` (`total_amount`),
  CONSTRAINT `fk_receipt_ocr_receipt_id` FOREIGN KEY (`receipt_id`) REFERENCES `receipt_uploads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Receipt Validations Table
-- Stores validation results and business rule checks
-- =====================================================
CREATE TABLE `receipt_validations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_id` int(11) NOT NULL,
  `retailer_id` int(11) DEFAULT NULL,
  `validation_status` enum('pending','approved','rejected','manual_review') DEFAULT 'pending',
  `validation_result` text DEFAULT NULL COMMENT 'JSON formatted validation results',
  `rejection_reason` varchar(500) DEFAULT NULL,
  `duplicate_check_result` text DEFAULT NULL COMMENT 'Duplicate detection results',
  `data_quality_score` decimal(5,2) DEFAULT NULL COMMENT 'Data quality score 0-100',
  `points_awarded` int(11) DEFAULT 0,
  `points_calculation` text DEFAULT NULL COMMENT 'Points calculation breakdown',
  `ai_processed` tinyint(1) DEFAULT 0 COMMENT 'Whether AI assistant processed this receipt',
  `ai_response` text DEFAULT NULL COMMENT 'AI assistant response/reasoning',
  `manual_review_notes` text DEFAULT NULL,
  `processed_by` varchar(100) DEFAULT NULL COMMENT 'System or admin user who processed',
  `processed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_receipt_validation` (`receipt_id`),
  KEY `idx_validation_status` (`validation_status`),
  KEY `idx_retailer_id` (`retailer_id`),
  KEY `idx_points_awarded` (`points_awarded`),
  KEY `idx_processed_at` (`processed_at`),
  CONSTRAINT `fk_receipt_validations_receipt_id` FOREIGN KEY (`receipt_id`) REFERENCES `receipt_uploads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_receipt_validations_retailer_id` FOREIGN KEY (`retailer_id`) REFERENCES `retailers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- User Messages Table
-- Stores messages to be delivered to users
-- =====================================================
CREATE TABLE `user_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `message_type` enum('points_awarded','receipt_rejected','system_notification','promotion','general') DEFAULT 'general',
  `title` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `related_receipt_id` int(11) DEFAULT NULL,
  `points_amount` int(11) DEFAULT NULL COMMENT 'Points related to this message',
  `is_delivered` tinyint(1) DEFAULT 0,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `read_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL COMMENT 'Message expiry date',
  `priority` enum('low','normal','high','urgent') DEFAULT 'normal',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_message_type` (`message_type`),
  KEY `idx_is_delivered` (`is_delivered`),
  KEY `idx_is_read` (`is_read`),
  KEY `idx_related_receipt_id` (`related_receipt_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_user_messages_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_user_messages_receipt_id` FOREIGN KEY (`related_receipt_id`) REFERENCES `receipt_uploads` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Insert Sample Retailers
-- =====================================================
INSERT INTO `retailers` (`name`, `brand_name`, `identifiers`, `contact_info`, `points_multiplier`) VALUES
('Pick n Pay', 'Pick n Pay', '["Pick n Pay", "PnP", "Pick''n Pay", "PICK N PAY"]', '{"website": "https://www.picknpay.co.za", "phone": "0800 11 22 88"}', 1.00),
('Shoprite', 'Shoprite', '["Shoprite", "SHOPRITE", "Shoprite Holdings"]', '{"website": "https://www.shoprite.co.za", "phone": "0800 007 772"}', 1.00),
('Checkers', 'Checkers', '["Checkers", "CHECKERS", "Checkers Hyper"]', '{"website": "https://www.checkers.co.za", "phone": "0800 300 600"}', 1.00),
('Woolworths', 'Woolworths', '["Woolworths", "WOOLWORTHS", "Woolies", "WW"]', '{"website": "https://www.woolworths.co.za", "phone": "0861 022 002"}', 1.20),
('Spar', 'Spar', '["Spar", "SPAR", "SUPERSPAR", "KWIKSPAR"]', '{"website": "https://www.spar.co.za", "phone": "011 415 1000"}', 1.00),
('Game', 'Game', '["Game", "GAME", "Game Stores"]', '{"website": "https://www.game.co.za", "phone": "0860 426 3"}', 1.10),
('Makro', 'Makro', '["Makro", "MAKRO", "Makro Cash & Carry"]', '{"website": "https://www.makro.co.za", "phone": "0860 100 200"}', 1.00),
('Dischem', 'Dis-Chem', '["Dis-Chem", "DISCHEM", "Dis Chem", "DIS-CHEM"]', '{"website": "https://www.dischem.co.za", "phone": "0860 347 243"}', 1.15),
('Clicks', 'Clicks', '["Clicks", "CLICKS", "Clicks Pharmacy"]', '{"website": "https://www.clicks.co.za", "phone": "0860 254 257"}', 1.15),
('Takealot', 'Takealot', '["Takealot", "TAKEALOT", "takealot.com"]', '{"website": "https://www.takealot.com", "phone": "087 362 7000"}', 1.05);

-- =====================================================
-- Indexes for Performance
-- =====================================================

-- Composite indexes for common queries
CREATE INDEX `idx_receipt_user_status` ON `receipt_uploads` (`user_id`, `upload_status`, `created_at`);
CREATE INDEX `idx_ocr_status_date` ON `receipt_ocr_data` (`processing_status`, `receipt_date`);
CREATE INDEX `idx_validation_status_date` ON `receipt_validations` (`validation_status`, `created_at`);
CREATE INDEX `idx_messages_user_delivered` ON `user_messages` (`user_id`, `is_delivered`, `created_at`);

-- Full-text search indexes for OCR data
ALTER TABLE `receipt_ocr_data` ADD FULLTEXT(`raw_ocr_text`);
ALTER TABLE `receipt_ocr_data` ADD FULLTEXT(`store_name`);

-- =====================================================
-- Sample Analytics Views
-- =====================================================

-- Create view for receipt processing statistics
CREATE VIEW `receipt_processing_stats` AS
SELECT 
    DATE(ru.created_at) as date,
    COUNT(ru.id) as total_uploads,
    COUNT(CASE WHEN ru.upload_status = 'completed' THEN 1 END) as completed_uploads,
    COUNT(CASE WHEN ru.upload_status = 'failed' THEN 1 END) as failed_uploads,
    COUNT(CASE WHEN rv.validation_status = 'approved' THEN 1 END) as approved_receipts,
    COUNT(CASE WHEN rv.validation_status = 'rejected' THEN 1 END) as rejected_receipts,
    SUM(CASE WHEN rv.validation_status = 'approved' THEN rv.points_awarded ELSE 0 END) as total_points_awarded,
    AVG(CASE WHEN rod.ocr_confidence IS NOT NULL THEN rod.ocr_confidence END) as avg_ocr_confidence
FROM receipt_uploads ru
LEFT JOIN receipt_ocr_data rod ON ru.id = rod.receipt_id
LEFT JOIN receipt_validations rv ON ru.id = rv.receipt_id
GROUP BY DATE(ru.created_at)
ORDER BY date DESC;

-- Create view for retailer performance
CREATE VIEW `retailer_performance` AS
SELECT 
    r.name as retailer_name,
    COUNT(rv.id) as total_receipts,
    COUNT(CASE WHEN rv.validation_status = 'approved' THEN 1 END) as approved_receipts,
    COUNT(CASE WHEN rv.validation_status = 'rejected' THEN 1 END) as rejected_receipts,
    ROUND(COUNT(CASE WHEN rv.validation_status = 'approved' THEN 1 END) * 100.0 / COUNT(rv.id), 2) as approval_rate,
    SUM(CASE WHEN rv.validation_status = 'approved' THEN rv.points_awarded ELSE 0 END) as total_points_awarded,
    AVG(CASE WHEN rv.validation_status = 'approved' THEN rv.points_awarded END) as avg_points_per_receipt
FROM retailers r
LEFT JOIN receipt_validations rv ON r.id = rv.retailer_id
WHERE r.is_active = 1
GROUP BY r.id, r.name
ORDER BY total_receipts DESC;

-- =====================================================
-- Stored Procedures for Common Operations
-- =====================================================

DELIMITER //

-- Procedure to get user's undelivered messages
CREATE PROCEDURE GetUserMessages(IN p_user_id INT)
BEGIN
    SELECT 
        id,
        message_type,
        title,
        message,
        points_amount,
        priority,
        created_at
    FROM user_messages 
    WHERE user_id = p_user_id 
    AND is_delivered = 0 
    AND (expires_at IS NULL OR expires_at > NOW())
    ORDER BY priority DESC, created_at DESC;
    
    -- Mark messages as delivered
    UPDATE user_messages 
    SET is_delivered = 1, delivered_at = NOW() 
    WHERE user_id = p_user_id 
    AND is_delivered = 0 
    AND (expires_at IS NULL OR expires_at > NOW());
END //

-- Procedure to check for duplicate receipts
CREATE PROCEDURE CheckDuplicateReceipt(
    IN p_store_name VARCHAR(255),
    IN p_receipt_date DATE,
    IN p_receipt_time TIME,
    IN p_total_amount DECIMAL(10,2),
    IN p_pos_number VARCHAR(100),
    OUT p_duplicate_count INT,
    OUT p_duplicate_receipt_ids TEXT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE receipt_id INT;
    DECLARE receipt_ids TEXT DEFAULT '';
    
    DECLARE cur CURSOR FOR 
        SELECT rod.receipt_id
        FROM receipt_ocr_data rod
        JOIN receipt_validations rv ON rod.receipt_id = rv.receipt_id
        WHERE rod.store_name = p_store_name
        AND rod.receipt_date = p_receipt_date
        AND (
            (p_receipt_time IS NOT NULL AND rod.receipt_time = p_receipt_time) OR
            (p_total_amount IS NOT NULL AND rod.total_amount = p_total_amount) OR
            (p_pos_number IS NOT NULL AND rod.pos_number = p_pos_number)
        )
        AND rv.validation_status = 'approved';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    SET p_duplicate_count = 0;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO receipt_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET p_duplicate_count = p_duplicate_count + 1;
        SET receipt_ids = CONCAT(receipt_ids, IF(receipt_ids = '', '', ','), receipt_id);
    END LOOP;
    
    CLOSE cur;
    SET p_duplicate_receipt_ids = receipt_ids;
END //

DELIMITER ;

-- =====================================================
-- Usage Examples and Notes
-- =====================================================

/*
Table Relationships:
- receipt_uploads: Main receipt file storage
- receipt_ocr_data: OCR extracted data (1:1 with receipt_uploads)
- receipt_validations: Business rule validation results (1:1 with receipt_uploads)
- user_messages: Notifications to users (many:1 with users)
- retailers: Approved retail partners (many:1 with receipt_validations)

Processing Flow:
1. User uploads receipt → receipt_uploads
2. OCR processes file → receipt_ocr_data
3. Business rules validate → receipt_validations
4. Points awarded or rejection message → user_messages

Key Features:
- Foreign key constraints ensure data integrity
- Comprehensive indexing for performance
- Full-text search on OCR data
- Analytics views for reporting
- Stored procedures for common operations
- Support for manual review workflow
- Duplicate detection capabilities
- Retailer-specific points multipliers

Security Considerations:
- File paths stored securely
- User data protected by foreign key constraints
- Message expiry to prevent spam
- Audit trail with timestamps
*/

