-- InnoInvoice SaaS upgrade migration (non-destructive; MySQL 5.7+/MariaDB 10.4+)
-- Back up the database before applying. Existing users/customers/invoices are preserved.
ALTER TABLE users
  ADD COLUMN account_status ENUM('active','suspended') NOT NULL DEFAULT 'active',
  ADD COLUMN email_verified_at DATETIME NULL;

CREATE TABLE IF NOT EXISTS plans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(80) NOT NULL UNIQUE,
 billing_interval ENUM('monthly','yearly') NOT NULL,
 price_minor BIGINT UNSIGNED NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'NGN',
 invoice_limit INT UNSIGNED NULL,
 customer_limit INT UNSIGNED NULL,
 features_json JSON NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_plans_active_interval(is_active,billing_interval)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 plan_id BIGINT UNSIGNED NOT NULL,
 status ENUM('pending','active','expired','cancelled','past_due','suspended') NOT NULL DEFAULT 'pending',
 start_at DATETIME NULL, expires_at DATETIME NULL, next_renewal_at DATETIME NULL,
 grace_until DATETIME NULL, paystack_subscription_code VARCHAR(120) NULL,
 paystack_email_token VARCHAR(120) NULL, authorization_code VARCHAR(120) NULL,
 auto_renew TINYINT(1) NOT NULL DEFAULT 0,
 cancelled_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_sub_user_status(user_id,status), INDEX idx_sub_expiry(status,expires_at),
 INDEX idx_sub_renewal(next_renewal_at),
 CONSTRAINT fk_sub_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_sub_plan FOREIGN KEY(plan_id) REFERENCES plans(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscription_payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL, subscription_id BIGINT UNSIGNED NULL, plan_id BIGINT UNSIGNED NULL,
 provider VARCHAR(30) NOT NULL DEFAULT 'paystack',
 reference VARCHAR(120) NOT NULL UNIQUE,
 provider_transaction_id BIGINT NULL,
 amount_minor BIGINT UNSIGNED NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'NGN',
 status ENUM('initialized','pending','success','failed','abandoned','refunded') NOT NULL DEFAULT 'initialized',
 paid_at DATETIME NULL, raw_response JSON NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_subpay_user(user_id), INDEX idx_subpay_status_created(status,created_at),
 CONSTRAINT fk_subpay_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_subpay_subscription FOREIGN KEY(subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL,
 CONSTRAINT fk_subpay_plan FOREIGN KEY(plan_id) REFERENCES plans(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS webhook_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 provider VARCHAR(30) NOT NULL, event_key VARCHAR(190) NOT NULL,
 event_type VARCHAR(100) NOT NULL, payload JSON NOT NULL,
 processing_status ENUM('received','processed','failed','ignored') NOT NULL DEFAULT 'received',
 processed_at DATETIME NULL, error_message VARCHAR(500) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_webhook_provider_event(provider,event_key), INDEX idx_webhook_status(processing_status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE,
 password VARCHAR(255) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1,
 last_login_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 actor_type ENUM('admin','user','system') NOT NULL, actor_id BIGINT UNSIGNED NULL,
 action VARCHAR(100) NOT NULL, target_type VARCHAR(80) NULL, target_id BIGINT UNSIGNED NULL,
 details JSON NULL, ip_address VARCHAR(45) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_audit_actor(actor_type,actor_id), INDEX idx_audit_target(target_type,target_id), INDEX idx_audit_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL, recipient VARCHAR(190) NOT NULL, subject VARCHAR(255) NOT NULL,
 template_key VARCHAR(80) NOT NULL, status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
 attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, error_message VARCHAR(500) NULL,
 sent_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_email_status(status,created_at), INDEX idx_email_user(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_reminders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, subscription_id BIGINT UNSIGNED NOT NULL,
 reminder_type VARCHAR(60) NOT NULL, scheduled_for DATETIME NOT NULL,
 status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_reminder_subscription_type(subscription_id,reminder_type,scheduled_for),
 CONSTRAINT fk_reminder_subscription FOREIGN KEY(subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_verification_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 token_hash CHAR(64) NOT NULL UNIQUE, expires_at DATETIME NOT NULL, verified_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_verify_expiry(expires_at),
 CONSTRAINT fk_verify_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS password_resets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 token_hash CHAR(64) NOT NULL UNIQUE, expires_at DATETIME NOT NULL, used_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_pwreset_expiry(expires_at),
 CONSTRAINT fk_pwreset_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_settings (
 setting_key VARCHAR(100) PRIMARY KEY, setting_value TEXT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO plans(name,billing_interval,price_minor,currency,invoice_limit,customer_limit,features_json,is_active)
VALUES
('Starter Monthly','monthly',300000,'NGN',30,100,JSON_OBJECT('quotations',true,'expenses',true),1),
('Professional Monthly','monthly',700000,'NGN',500,2000,JSON_OBJECT('quotations',true,'expenses',true,'reports',true),1),
('Business Monthly','monthly',1500000,'NGN',NULL,NULL,JSON_OBJECT('quotations',true,'expenses',true,'reports',true,'priority_support',true),1),
('Starter Yearly','yearly',3000000,'NGN',30,100,JSON_OBJECT('quotations',true,'expenses',true),1),
('Professional Yearly','yearly',7000000,'NGN',500,2000,JSON_OBJECT('quotations',true,'expenses',true,'reports',true),1),
('Business Yearly','yearly',15000000,'NGN',NULL,NULL,JSON_OBJECT('quotations',true,'expenses',true,'reports',true,'priority_support',true),1);

-- Preserve valid legacy entitlements where the old application recorded an active subscription and future expiry.
INSERT INTO subscriptions(user_id,plan_id,status,start_at,expires_at,next_renewal_at)
SELECT u.id,p.id,'active',COALESCE(u.created_at,NOW()),u.subscription_expires_at,u.subscription_expires_at
FROM users u
JOIN plans p ON p.name=CASE
 WHEN LOWER(u.plan) LIKE '%professional%' THEN CONCAT('Professional ',IF(LOWER(u.plan) LIKE '%year%', 'Yearly','Monthly'))
 WHEN LOWER(u.plan) LIKE '%business%' THEN CONCAT('Business ',IF(LOWER(u.plan) LIKE '%year%', 'Yearly','Monthly'))
 ELSE CONCAT('Starter ',IF(LOWER(u.plan) LIKE '%year%', 'Yearly','Monthly')) END
WHERE u.subscription_status='active' AND u.subscription_expires_at>NOW()
AND NOT EXISTS (SELECT 1 FROM subscriptions s WHERE s.user_id=u.id AND s.status='active');