/**
 * Toolpillar Creator Marketplace - Database Schema
 * 
 * Complete MySQL schema with all tables needed for:
 * - User authentication (creators, customers, admins)
 * - Tool listings & marketplace
 * - Vetting system
 * - Reviews & ratings
 * - Payments & orders
 * - Payouts & finances
 * - Admin features
 * 
 * Run: mysql -u user -p database < schema.sql
 */

-- ============================================================================
-- USERS & AUTHENTICATION
-- ============================================================================

CREATE TABLE IF NOT EXISTS `users` (
  `id` VARCHAR(36) PRIMARY KEY,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `password_salt` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `role` ENUM('customer', 'creator', 'admin') NOT NULL DEFAULT 'customer',
  `email_verified` BOOLEAN DEFAULT FALSE,
  `email_verified_at` DATETIME,
  `last_login` DATETIME,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME,
  KEY `email` (`email`),
  KEY `role` (`role`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CREATOR PROFILES
-- ============================================================================

CREATE TABLE IF NOT EXISTS `creators` (
  `id` VARCHAR(36) PRIMARY KEY,
  `user_id` VARCHAR(36) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `bio` TEXT,
  `website` VARCHAR(255),
  `avatar_url` VARCHAR(255),
  `tier_id` VARCHAR(36),
  `tier_unlocked_at` DATETIME,
  `tier_expires_at` DATETIME,
  `paypal_email` VARCHAR(255),
  `total_earnings` DECIMAL(12, 2) DEFAULT 0,
  `total_payouts` DECIMAL(12, 2) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  KEY `tier_id` (`tier_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CUSTOMER PROFILES
-- ============================================================================

CREATE TABLE IF NOT EXISTS `customers` (
  `id` VARCHAR(36) PRIMARY KEY,
  `user_id` VARCHAR(36) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `avatar_url` VARCHAR(255),
  `total_purchases` INT DEFAULT 0,
  `total_spent` DECIMAL(12, 2) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TOOL LISTINGS & MARKETPLACE
-- ============================================================================

CREATE TABLE IF NOT EXISTS `tool_listings` (
  `id` VARCHAR(36) PRIMARY KEY,
  `creator_id` VARCHAR(36) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` LONGTEXT NOT NULL,
  `category` VARCHAR(255) NOT NULL,
  `price` DECIMAL(10, 2) NOT NULL DEFAULT 0,
  `tier_required` ENUM('free', 'plus', 'premium', 'elite', 'prime') DEFAULT 'free',
  `code` LONGBLOB,
  `status` ENUM('draft', 'published', 'archived') DEFAULT 'draft',
  `vetting_status` ENUM('pending', 'auto_approved', 'auto_rejected', 'approved', 'rejected') DEFAULT 'pending',
  `vetting_report` JSON,
  `downloads` INT DEFAULT 0,
  `rating` DECIMAL(3, 2),
  `version` VARCHAR(50) DEFAULT '1.0.0',
  `changelog` LONGTEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME,
  FOREIGN KEY (`creator_id`) REFERENCES `creators` (`id`),
  KEY `creator_id` (`creator_id`),
  KEY `status` (`status`),
  KEY `vetting_status` (`vetting_status`),
  KEY `category` (`category`),
  KEY `rating` (`rating`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- VETTING SYSTEM
-- ============================================================================

CREATE TABLE IF NOT EXISTS `vetting_reports` (
  `id` VARCHAR(36) PRIMARY KEY,
  `tool_id` VARCHAR(36) NOT NULL,
  `risk_score` INT NOT NULL DEFAULT 0,
  `findings` JSON,
  `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  `reviewed_by` VARCHAR(36),
  `rejection_reason` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `reviewed_at` DATETIME,
  FOREIGN KEY (`tool_id`) REFERENCES `tool_listings` (`id`),
  FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`),
  KEY `status` (`status`),
  KEY `risk_score` (`risk_score`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- REVIEWS & RATINGS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `internal_reviews` (
  `id` VARCHAR(36) PRIMARY KEY,
  `tool_id` VARCHAR(36) NOT NULL,
  `reviewer_id` VARCHAR(36) NOT NULL,
  `stars` INT NOT NULL CHECK (stars >= 1 AND stars <= 5),
  `notes` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`tool_id`) REFERENCES `tool_listings` (`id`),
  FOREIGN KEY (`reviewer_id`) REFERENCES `users` (`id`),
  KEY `tool_id` (`tool_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `customer_reviews` (
  `id` VARCHAR(36) PRIMARY KEY,
  `tool_id` VARCHAR(36) NOT NULL,
  `customer_id` VARCHAR(36) NOT NULL,
  `rating` INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
  `title` VARCHAR(255),
  `content` LONGTEXT,
  `weight` DECIMAL(3, 2) DEFAULT 1.0,
  `verified_purchase` BOOLEAN DEFAULT FALSE,
  `helpful_count` INT DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`tool_id`) REFERENCES `tool_listings` (`id`),
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  KEY `tool_id` (`tool_id`),
  KEY `rating` (`rating`),
  KEY `created_at` (`created_at`),
  KEY `weight` (`weight`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `review_trust_scores` (
  `id` VARCHAR(36) PRIMARY KEY,
  `tool_id` VARCHAR(36) NOT NULL UNIQUE,
  `avg_rating` DECIMAL(3, 2),
  `review_count` INT DEFAULT 0,
  `badges` JSON,
  `last_updated` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`tool_id`) REFERENCES `tool_listings` (`id`),
  KEY `avg_rating` (`avg_rating`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- ABUSE REPORTS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `abuse_reports` (
  `id` VARCHAR(36) PRIMARY KEY,
  `tool_id` VARCHAR(36) NOT NULL,
  `reporter_id` VARCHAR(36),
  `category` ENUM('malware', 'copyright', 'inappropriate', 'spam', 'misleading', 'buggy', 'other') NOT NULL,
  `description` LONGTEXT,
  `status` ENUM('open', 'investigating', 'resolved', 'dismissed') DEFAULT 'open',
  `priority` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
  `assigned_to` VARCHAR(36),
  `resolution` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` DATETIME,
  FOREIGN KEY (`tool_id`) REFERENCES `tool_listings` (`id`),
  FOREIGN KEY (`reporter_id`) REFERENCES `users` (`id`),
  FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`),
  KEY `status` (`status`),
  KEY `priority` (`priority`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- DISPUTES & APPEALS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `disputes` (
  `id` VARCHAR(36) PRIMARY KEY,
  `tool_id` VARCHAR(36) NOT NULL,
  `creator_id` VARCHAR(36) NOT NULL,
  `reason` TEXT NOT NULL,
  `status` ENUM('open', 'under_review', 'resolved') DEFAULT 'open',
  `resolution` TEXT,
  `resolved_by` VARCHAR(36),
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` DATETIME,
  FOREIGN KEY (`tool_id`) REFERENCES `tool_listings` (`id`),
  FOREIGN KEY (`creator_id`) REFERENCES `creators` (`id`),
  FOREIGN KEY (`resolved_by`) REFERENCES `users` (`id`),
  KEY `status` (`status`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- ORDERS & PURCHASES
-- ============================================================================

CREATE TABLE IF NOT EXISTS `orders` (
  `id` VARCHAR(36) PRIMARY KEY,
  `customer_id` VARCHAR(36) NOT NULL,
  `tool_id` VARCHAR(36) NOT NULL,
  `amount` DECIMAL(10, 2) NOT NULL,
  `status` ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
  `payment_method` VARCHAR(50),
  `paypal_order_id` VARCHAR(255),
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `paid_at` DATETIME,
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  FOREIGN KEY (`tool_id`) REFERENCES `tool_listings` (`id`),
  KEY `status` (`status`),
  KEY `customer_id` (`customer_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchases` (
  `id` VARCHAR(36) PRIMARY KEY,
  `customer_id` VARCHAR(36) NOT NULL,
  `tool_id` VARCHAR(36) NOT NULL,
  `creator_id` VARCHAR(36) NOT NULL,
  `amount` DECIMAL(10, 2) NOT NULL,
  `commission_amount` DECIMAL(10, 2),
  `creator_earnings` DECIMAL(10, 2),
  `status` ENUM('completed', 'refunded') DEFAULT 'completed',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  FOREIGN KEY (`tool_id`) REFERENCES `tool_listings` (`id`),
  FOREIGN KEY (`creator_id`) REFERENCES `creators` (`id`),
  KEY `creator_id` (`creator_id`),
  KEY `status` (`status`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- PAYOUTS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `payouts` (
  `id` VARCHAR(36) PRIMARY KEY,
  `creator_id` VARCHAR(36) NOT NULL,
  `amount` DECIMAL(10, 2) NOT NULL,
  `status` ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
  `paypal_batch_id` VARCHAR(255),
  `period_start` DATE,
  `period_end` DATE,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `payout_date` DATETIME,
  FOREIGN KEY (`creator_id`) REFERENCES `creators` (`id`),
  KEY `status` (`status`),
  KEY `creator_id` (`creator_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SUBSCRIPTIONS & TIERS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `creator_subscriptions` (
  `id` VARCHAR(36) PRIMARY KEY,
  `creator_id` VARCHAR(36) NOT NULL,
  `tier_id` VARCHAR(36) NOT NULL,
  `billing_period` ENUM('monthly', 'yearly') DEFAULT 'monthly',
  `status` ENUM('active', 'paused', 'cancelled') DEFAULT 'active',
  `current_period_start` DATETIME,
  `current_period_end` DATETIME,
  `cancelled_at` DATETIME,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`creator_id`) REFERENCES `creators` (`id`),
  KEY `status` (`status`),
  KEY `tier_id` (`tier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- ACTIVITY LOGGING
-- ============================================================================

CREATE TABLE IF NOT EXISTS `activity_log` (
  `id` VARCHAR(36) PRIMARY KEY,
  `user_id` VARCHAR(36),
  `action` VARCHAR(255) NOT NULL,
  `entity_type` VARCHAR(100),
  `entity_id` VARCHAR(36),
  `details` JSON,
  `ip_address` VARCHAR(45),
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  KEY `user_id` (`user_id`),
  KEY `action` (`action`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- QUARANTINE / BANS
-- ============================================================================

CREATE TABLE IF NOT EXISTS `quarantine` (
  `id` VARCHAR(36) PRIMARY KEY,
  `tool_id` VARCHAR(36) NOT NULL,
  `reason` TEXT NOT NULL,
  `quarantined_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `quarantined_by` VARCHAR(36) NOT NULL,
  `released_at` DATETIME,
  `permanent` BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (`tool_id`) REFERENCES `tool_listings` (`id`),
  FOREIGN KEY (`quarantined_by`) REFERENCES `users` (`id`),
  KEY `tool_id` (`tool_id`),
  KEY `quarantined_at` (`quarantined_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- INDEXES FOR PERFORMANCE
-- ============================================================================

CREATE INDEX idx_tool_listings_status_vetting ON tool_listings(status, vetting_status);
CREATE INDEX idx_tool_listings_created_at_category ON tool_listings(created_at DESC, category);
CREATE INDEX idx_purchases_creator_date ON purchases(creator_id, created_at DESC);
CREATE INDEX idx_customer_reviews_tool_weight ON customer_reviews(tool_id, weight DESC);
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
CREATE INDEX idx_payouts_status_date ON payouts(status, created_at);
CREATE INDEX idx_abuse_reports_status_priority ON abuse_reports(status, priority);

-- ============================================================================
-- VIEWS FOR COMMON QUERIES
-- ============================================================================

CREATE VIEW IF NOT EXISTS `vw_tool_stats` AS
SELECT 
  tl.id,
  tl.name,
  tl.creator_id,
  COUNT(DISTINCT p.id) as purchase_count,
  SUM(p.amount) as total_revenue,
  AVG(cr.rating) as avg_rating,
  COUNT(DISTINCT cr.id) as review_count
FROM tool_listings tl
LEFT JOIN purchases p ON tl.id = p.tool_id
LEFT JOIN customer_reviews cr ON tl.id = cr.tool_id
GROUP BY tl.id;

CREATE VIEW IF NOT EXISTS `vw_creator_earnings` AS
SELECT 
  c.id,
  c.name,
  COUNT(DISTINCT p.id) as tool_count,
  SUM(pu.amount) as total_earnings,
  SUM(CASE WHEN pu.status = 'completed' THEN pu.amount ELSE 0 END) as paid_out,
  SUM(CASE WHEN pu.status = 'pending' THEN pu.amount ELSE 0 END) as pending_payout
FROM creators c
LEFT JOIN tool_listings tl ON c.id = tl.creator_id
LEFT JOIN purchases p ON tl.id = p.tool_id
LEFT JOIN payouts pu ON c.id = pu.creator_id
GROUP BY c.id;

-- ============================================================================
-- INITIALIZATION DATA
-- ============================================================================

-- Create default admin user (change password immediately in production!)
-- Email: admin@toolpillar.local
-- Password: admin123 (CHANGE THIS!)
INSERT IGNORE INTO `users` (
  `id`, `email`, `password_hash`, `password_salt`, `name`, `role`, `created_at`
) VALUES (
  UUID(),
  'admin@toolpillar.local',
  'a34d8c3e4e1b3f2c5d8a9e7f6c4b2a1e3f5d8c9b', -- pbkdf2(admin123)
  'salt123456',
  'Administrator',
  'admin',
  NOW()
);