-- ================================================
-- SaaS Platform Database Migration
-- Create organizations, plans, subscriptions tables
-- ================================================

-- Create subscription plans table
CREATE TABLE subscription_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  display_name VARCHAR(100) NOT NULL,
  description TEXT,
  price_monthly DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  price_yearly DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  max_users INT NOT NULL DEFAULT 10,
  max_projects INT NOT NULL DEFAULT 50,
  max_integrations INT NOT NULL DEFAULT 5,
  features JSON, -- Array of feature flags
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  UNIQUE KEY uk_plan_name (name)
);

-- Create organizations table
CREATE TABLE organizations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE, -- For subdomain/URL
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(20),
  website VARCHAR(255),
  logo_url VARCHAR(500),
  timezone VARCHAR(50) DEFAULT 'UTC',
  settings JSON, -- Organization-specific settings
  status ENUM('active', 'suspended', 'cancelled', 'trial') DEFAULT 'trial',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  INDEX idx_org_status (status),
  INDEX idx_org_slug (slug)
);

-- Create subscriptions table
CREATE TABLE subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  organization_id INT NOT NULL,
  plan_id INT NOT NULL,
  status ENUM('active', 'cancelled', 'expired', 'trial', 'past_due') DEFAULT 'trial',
  billing_cycle ENUM('monthly', 'yearly') DEFAULT 'monthly',
  current_period_start DATE,
  current_period_end DATE,
  trial_start DATE,
  trial_end DATE,
  cancelled_at TIMESTAMP NULL,
  payment_gateway VARCHAR(50), -- razorpay, stripe, etc.
  gateway_subscription_id VARCHAR(255),
  gateway_customer_id VARCHAR(255),
  metadata JSON, -- Payment gateway specific data
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES subscription_plans(id),
  INDEX idx_sub_org (organization_id),
  INDEX idx_sub_status (status),
  INDEX idx_sub_gateway (gateway_subscription_id)
);

-- Create organization members table (replaces simple users for multi-tenancy)
CREATE TABLE organization_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  organization_id INT NOT NULL,
  user_id INT NOT NULL, -- References existing users table
  role ENUM('owner', 'admin', 'manager', 'member') DEFAULT 'member',
  status ENUM('active', 'invited', 'suspended') DEFAULT 'invited',
  invited_by INT, -- User ID who sent invitation
  invited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  joined_at TIMESTAMP NULL,
  permissions JSON, -- Specific permissions override
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uk_org_user (organization_id, user_id),
  INDEX idx_org_members (organization_id),
  INDEX idx_member_status (status)
);

-- Create usage tracking table
CREATE TABLE organization_usage (
  id INT AUTO_INCREMENT PRIMARY KEY,
  organization_id INT NOT NULL,
  metric_name VARCHAR(100) NOT NULL, -- 'users_count', 'projects_count', 'tasks_created', etc.
  metric_value INT DEFAULT 0,
  period_start DATE,
  period_end DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_usage_org_metric (organization_id, metric_name),
  INDEX idx_usage_period (period_start, period_end)
);

-- Create billing history table
CREATE TABLE billing_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  organization_id INT NOT NULL,
  subscription_id INT NOT NULL,
  invoice_id VARCHAR(255),
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'INR',
  status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
  billing_date DATE,
  due_date DATE,
  paid_date DATE NULL,
  payment_method VARCHAR(50),
  gateway_transaction_id VARCHAR(255),
  metadata JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE,
  INDEX idx_billing_org (organization_id),
  INDEX idx_billing_status (status),
  INDEX idx_billing_date (billing_date)
);

-- Create feature flags table
CREATE TABLE feature_flags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  organization_id INT,
  feature_name VARCHAR(100) NOT NULL,
  is_enabled BOOLEAN DEFAULT FALSE,
  config JSON, -- Feature-specific configuration
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_features_org (organization_id),
  INDEX idx_features_name (feature_name)
);

-- Insert default subscription plans
INSERT INTO subscription_plans (name, display_name, description, price_monthly, price_yearly, max_users, max_projects, max_integrations, features) VALUES
('starter', 'Starter Plan', 'Perfect for small teams getting started', 29.00, 290.00, 5, 10, 2, 
 '["basic_tasks", "google_sheets_integration", "slack_integration", "basic_reporting"]'),

('professional', 'Professional Plan', 'Ideal for growing teams', 99.00, 990.00, 15, 50, 10, 
 '["basic_tasks", "google_sheets_integration", "slack_integration", "trello_integration", "advanced_reporting", "custom_workflows", "team_analytics", "priority_support"]'),

('enterprise', 'Enterprise Plan', 'For large organizations', 299.00, 2990.00, 100, 500, 50, 
 '["basic_tasks", "google_sheets_integration", "slack_integration", "trello_integration", "advanced_reporting", "custom_workflows", "team_analytics", "priority_support", "sso", "advanced_security", "api_access", "white_label"]'),

('free_trial', 'Free Trial', '14-day free trial for all features', 0.00, 0.00, 3, 5, 1, 
 '["basic_tasks", "google_sheets_integration", "slack_integration"]');

-- Add organization_id to existing tables to support multi-tenancy
ALTER TABLE projects 
  ADD COLUMN organization_id INT,
  ADD FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  ADD INDEX idx_projects_org (organization_id);

ALTER TABLE users 
  ADD COLUMN password_hash VARCHAR(255) AFTER email,
  ADD COLUMN email_verified BOOLEAN DEFAULT FALSE AFTER password_hash,
  ADD COLUMN verification_token VARCHAR(255) AFTER email_verified,
  ADD COLUMN reset_password_token VARCHAR(255) AFTER verification_token,
  ADD COLUMN reset_password_expires TIMESTAMP NULL AFTER reset_password_token,
  ADD COLUMN last_login TIMESTAMP NULL AFTER reset_password_expires,
  ADD COLUMN created_by_org_id INT AFTER last_login,
  ADD FOREIGN KEY (created_by_org_id) REFERENCES organizations(id) ON DELETE SET NULL,
  ADD INDEX idx_users_email_verified (email_verified),
  ADD INDEX idx_users_verification (verification_token);

-- Update user_project_assignments to be organization-aware
ALTER TABLE user_project_assignments 
  ADD COLUMN organization_id INT AFTER project_id,
  ADD FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
  ADD INDEX idx_assignments_org (organization_id);

-- Create super admin users table (for FloChief platform admins)
CREATE TABLE super_admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  permissions JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uk_super_admin_user (user_id)
);