-- Database: cheapseo_limit

-- 1. Packages Table
CREATE TABLE IF NOT EXISTS `packages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `daily_credits` int(11) NOT NULL DEFAULT 0,
  `daily_csv_rows` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Users Table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amember_login` varchar(255) NOT NULL,
  `package_id` int(11) NOT NULL,
  `status` enum('active','expired') NOT NULL DEFAULT 'active',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `amember_login` (`amember_login`),
  KEY `package_id` (`package_id`),
  CONSTRAINT `fk_users_package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. User Usage Table
CREATE TABLE IF NOT EXISTS `user_usage` (
  `user_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `credits_used` int(11) NOT NULL DEFAULT 0,
  `csv_rows_used` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`,`date`),
  CONSTRAINT `fk_usage_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed Data
INSERT INTO `packages` (`id`, `name`, `daily_credits`, `daily_csv_rows`) VALUES
(1, 'Diamond Plan', 100, 5000),
(2, 'Gold Plan', 80, 3500)
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `daily_credits`=VALUES(`daily_credits`), `daily_csv_rows`=VALUES(`daily_csv_rows`);