-- ============================================================
-- Migración 006 · renewals (renovaciones de hosting)
-- ============================================================
-- Descripción:
--   Crea la tabla `renewals` para registrar las renovaciones de
--   cuentas de hosting realizadas desde el panel de cliente.
--   Al confirmar una renovación, la `expiration_date` de la
--   cuenta se extiende según el ciclo (anual = +1 año).
--
-- Uso:
--   1. Ve a phpMyAdmin → base `astrostudio_astrostudio` → pestaña "SQL".
--   2. Pega todo este contenido y pulsa "Continuar".
-- ============================================================

CREATE TABLE IF NOT EXISTS `renewals` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `renewal_code` varchar(30) NOT NULL COMMENT 'Código de renovación (ej: astro#491381)',
  `user_id` int unsigned NOT NULL COMMENT 'Cliente que renueva',
  `account_id` int unsigned NOT NULL COMMENT 'Cuenta de hosting renovada',
  `plan_id` int unsigned NOT NULL COMMENT 'Plan vigente de la cuenta',
  `domain_name` varchar(255) NOT NULL COMMENT 'Dominio de la cuenta',
  `total` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Total en soles',
  `billing_cycle` varchar(20) NOT NULL DEFAULT 'annual',
  `payment_method` varchar(50) NOT NULL DEFAULT 'transfer_yape_plin',
  `notes` text DEFAULT NULL,
  `terms_accepted` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('pending_validation','confirmed','cancelled') NOT NULL DEFAULT 'pending_validation',
  `client_ip` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `renewals_renewal_code_unique` (`renewal_code`),
  KEY `renewals_user_id_index` (`user_id`),
  KEY `renewals_account_id_index` (`account_id`),
  KEY `renewals_plan_id_index` (`plan_id`),
  CONSTRAINT `fk_renewals_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_renewals_account` FOREIGN KEY (`account_id`) REFERENCES `hosting_accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_renewals_plan` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;