-- Subscription and Payment Management Tables
-- Run this after the main database setup

USE sts_v2;

-- Subscription Plans table
CREATE TABLE IF NOT EXISTS subscription_plans (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  duration_months INT NOT NULL,
  features JSON,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Device Subscriptions table
CREATE TABLE IF NOT EXISTS device_subscriptions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  device_id VARCHAR(20) NOT NULL,
  user_id INT NOT NULL,
  plan_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status ENUM('active', 'expired', 'cancelled', 'suspended') DEFAULT 'active',
  auto_renew BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES subscription_plans(id)
);

-- Invoices table
CREATE TABLE IF NOT EXISTS invoices (
  id INT PRIMARY KEY AUTO_INCREMENT,
  invoice_number VARCHAR(20) UNIQUE NOT NULL,
  user_id INT NOT NULL,
  subscription_id INT,
  amount DECIMAL(10,2) NOT NULL,
  tax_amount DECIMAL(10,2) DEFAULT 0,
  total_amount DECIMAL(10,2) NOT NULL,
  due_date DATE NOT NULL,
  status ENUM('pending', 'paid', 'overdue', 'cancelled') DEFAULT 'pending',
  invoice_date DATE NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (subscription_id) REFERENCES device_subscriptions(id) ON DELETE SET NULL
);

-- Payment Methods table
CREATE TABLE IF NOT EXISTS payment_methods (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  type ENUM('credit_card', 'debit_card', 'bank_transfer', 'upi', 'wallet') NOT NULL,
  provider VARCHAR(50), -- Visa, MasterCard, PayPal, etc.
  last_four VARCHAR(4),
  expiry_month INT,
  expiry_year INT,
  is_default BOOLEAN DEFAULT FALSE,
  status ENUM('active', 'inactive', 'expired') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Payment Transactions table
CREATE TABLE IF NOT EXISTS payment_transactions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  transaction_id VARCHAR(50) UNIQUE NOT NULL,
  invoice_id INT NOT NULL,
  user_id INT NOT NULL,
  payment_method_id INT,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'INR',
  status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled', 'refunded') DEFAULT 'pending',
  gateway VARCHAR(50), -- Razorpay, Stripe, PayPal, etc.
  gateway_transaction_id VARCHAR(100),
  gateway_response JSON,
  processed_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE SET NULL
);

-- Payment History/Logs table
CREATE TABLE IF NOT EXISTS payment_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  transaction_id INT NOT NULL,
  status_from ENUM('pending', 'processing', 'completed', 'failed', 'cancelled', 'refunded'),
  status_to ENUM('pending', 'processing', 'completed', 'failed', 'cancelled', 'refunded'),
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (transaction_id) REFERENCES payment_transactions(id) ON DELETE CASCADE
);

-- Insert sample subscription plans
INSERT IGNORE INTO subscription_plans (name, description, price, duration_months, features) VALUES 
('Basic Monthly', 'Basic GPS tracking for 1 month', 299.00, 1, '["Real-time tracking", "Basic alerts", "Mobile app access"]'),
('Standard Quarterly', 'Standard GPS tracking for 3 months', 799.00, 3, '["Real-time tracking", "Advanced alerts", "Mobile app access", "Route history"]'),
('Premium Annual', 'Premium GPS tracking for 12 months', 2999.00, 12, '["Real-time tracking", "Advanced alerts", "Mobile app access", "Route history", "Geofencing", "Priority support"]'),
('Family Plan', 'Track up to 5 devices for 12 months', 4999.00, 12, '["Real-time tracking", "Advanced alerts", "Mobile app access", "Route history", "Geofencing", "Priority support", "Multiple devices"]');

-- Insert sample device subscriptions for existing devices
INSERT IGNORE INTO device_subscriptions (device_id, user_id, plan_id, start_date, end_date, status) VALUES 
('DEV001', 4, 3, '2024-01-01', '2024-12-31', 'active'),
('DEV002', 4, 3, '2024-01-01', '2024-12-31', 'active');

-- Insert sample invoices
INSERT IGNORE INTO invoices (invoice_number, user_id, subscription_id, amount, tax_amount, total_amount, due_date, status, invoice_date, description) VALUES 
('INV-2024-001', 4, 1, 2999.00, 539.82, 3538.82, '2024-01-15', 'paid', '2024-01-01', 'Premium Annual Subscription - DEV001'),
('INV-2024-002', 4, 2, 2999.00, 539.82, 3538.82, '2024-01-15', 'paid', '2024-01-01', 'Premium Annual Subscription - DEV002');

-- Insert sample payment methods
INSERT IGNORE INTO payment_methods (user_id, type, provider, last_four, expiry_month, expiry_year, is_default) VALUES 
(4, 'credit_card', 'Visa', '1234', 12, 2026, TRUE),
(4, 'upi', 'UPI', NULL, NULL, NULL, FALSE);

-- Insert sample payment transactions
INSERT IGNORE INTO payment_transactions (transaction_id, invoice_id, user_id, payment_method_id, amount, status, gateway, gateway_transaction_id, processed_at) VALUES 
('TXN-2024-001', 1, 4, 1, 3538.82, 'completed', 'Razorpay', 'pay_razorpay_123456', '2024-01-01 10:30:00'),
('TXN-2024-002', 2, 4, 1, 3538.82, 'completed', 'Razorpay', 'pay_razorpay_123457', '2024-01-01 10:35:00');

-- Create indexes for better performance
CREATE INDEX idx_device_subscriptions_device_id ON device_subscriptions(device_id);
CREATE INDEX idx_device_subscriptions_user_id ON device_subscriptions(user_id);
CREATE INDEX idx_device_subscriptions_status ON device_subscriptions(status);
CREATE INDEX idx_invoices_user_id ON invoices(user_id);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_payment_transactions_user_id ON payment_transactions(user_id);
CREATE INDEX idx_payment_transactions_status ON payment_transactions(status);

-- Verify subscription and payment setup
SELECT 'Subscription Plans Count:' as Info, COUNT(*) as Count FROM subscription_plans;
SELECT 'Device Subscriptions Count:' as Info, COUNT(*) as Count FROM device_subscriptions;
SELECT 'Invoices Count:' as Info, COUNT(*) as Count FROM invoices;
SELECT 'Payment Methods Count:' as Info, COUNT(*) as Count FROM payment_methods;
SELECT 'Payment Transactions Count:' as Info, COUNT(*) as Count FROM payment_transactions;

-- Show sample subscription data
SELECT 'Sample Subscription Data:' as Info;
SELECT ds.id, d.id as device_id, u.email, sp.name as plan_name, 
       ds.start_date, ds.end_date, ds.status, sp.price
FROM device_subscriptions ds
JOIN devices d ON ds.device_id = d.id
JOIN users u ON ds.user_id = u.id
JOIN subscription_plans sp ON ds.plan_id = sp.id
WHERE ds.user_id = 4;