-- =====================================================
-- Referral Sources Table Creation Script
-- For "How did you hear about us" feature
-- =====================================================

-- Drop table if exists (for clean installation)
DROP TABLE IF EXISTS `referral_sources`;

-- Create referral_sources table
CREATE TABLE `referral_sources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  `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_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default referral sources
INSERT INTO `referral_sources` (`name`, `description`, `sort_order`) VALUES
('Social Media', 'Facebook, Instagram, Twitter, LinkedIn, etc.', 1),
('Google Search', 'Found us through Google search', 2),
('Friend/Family Referral', 'Recommended by a friend or family member', 3),
('Advertisement', 'TV, Radio, Print, or Online Advertisement', 4),
('Website', 'Found us through our website', 5),
('Email Marketing', 'Received our email newsletter or promotion', 6),
('Word of Mouth', 'Heard about us from someone', 7),
('Store Visit', 'Discovered us while visiting a partner store', 8),
('Mobile App Store', 'Found our app in Google Play or App Store', 9),
('Other', 'Other source not listed above', 10);

-- =====================================================
-- User Referral Tracking Table
-- =====================================================

-- Drop table if exists
DROP TABLE IF EXISTS `user_referrals`;

-- Create user_referrals table to track how each user heard about us
CREATE TABLE `user_referrals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `referral_source_id` int(11) NOT NULL,
  `referral_details` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_referral` (`user_id`),
  KEY `idx_referral_source_id` (`referral_source_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_user_referrals_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_user_referrals_referral_source_id` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_sources` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Additional indexes for analytics queries
CREATE INDEX `idx_referral_source_active` ON `referral_sources` (`is_active`, `sort_order`);
CREATE INDEX `idx_user_referral_date` ON `user_referrals` (`referral_source_id`, `created_at`);

-- =====================================================
-- Sample Analytics Queries
-- =====================================================

-- Get referral source statistics
-- SELECT 
--     rs.name,
--     COUNT(ur.id) as user_count,
--     ROUND((COUNT(ur.id) * 100.0 / (SELECT COUNT(*) FROM user_referrals)), 2) as percentage
-- FROM referral_sources rs
-- LEFT JOIN user_referrals ur ON rs.id = ur.referral_source_id
-- WHERE rs.is_active = 1
-- GROUP BY rs.id, rs.name
-- ORDER BY user_count DESC;

-- Get monthly referral trends
-- SELECT 
--     rs.name,
--     DATE_FORMAT(ur.created_at, '%Y-%m') as month,
--     COUNT(ur.id) as user_count
-- FROM referral_sources rs
-- LEFT JOIN user_referrals ur ON rs.id = ur.referral_source_id
-- WHERE ur.created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
-- GROUP BY rs.id, rs.name, DATE_FORMAT(ur.created_at, '%Y-%m')
-- ORDER BY month DESC, user_count DESC;

-- =====================================================
-- Usage Notes
-- =====================================================

/*
Table Structure Explanation:

referral_sources:
- id: Primary key
- name: Display name of the referral source
- description: Detailed description for admin reference
- is_active: Whether this source is currently available for selection
- sort_order: Order in which sources should be displayed
- created_at/updated_at: Timestamps for tracking changes

user_referrals:
- id: Primary key
- user_id: Foreign key to users table
- referral_source_id: Foreign key to referral_sources table
- referral_details: Optional additional details (e.g., specific friend's name, ad campaign)
- created_at: When the user selected this referral source

Features:
- Unique constraint ensures one referral source per user
- Foreign key constraints maintain data integrity
- Soft delete capability (is_active flag)
- Sortable referral sources for consistent UI
- Analytics-ready structure for reporting
- Support for additional details/context

API Integration:
- GET /api/v1/referral-sources - Get all active referral sources
- POST /api/v1/user-referral - Set user's referral source
- GET /api/v1/user-referral/{user_id} - Get user's referral source
- Admin endpoints for managing referral sources
*/

