-- SQL Schema for ADA Wizard Application

-- Create the 'users' table
-- This table stores information about registered users.
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create the 'websites' table
-- This table stores information about the websites users add,
-- their widget IDs, and subscription details.
CREATE TABLE IF NOT EXISTS `websites` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `domain_name` VARCHAR(255) NOT NULL,
    `widget_id` VARCHAR(255) NOT NULL UNIQUE,
    `plan_type` ENUM('monthly', 'yearly', 'lifetime') NULL,
    `subscription_status` ENUM('active', 'inactive', 'pending_payment', 'cancelled') DEFAULT 'pending_payment',
    `subscription_start_date` DATE NULL,
    `subscription_end_date` DATE NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional: Add an index on websites.user_id for faster lookups of user's websites
CREATE INDEX IF NOT EXISTS `idx_user_id` ON `websites` (`user_id`);

-- Optional: Add an index on websites.domain_name if you plan to query by domain frequently
-- CREATE INDEX IF NOT EXISTS `idx_domain_name` ON `websites` (`domain_name`);

/*
Instructions:
1. Ensure you have created the database (e.g., 'ada_wizard') as specified in `config/db_config.php`.
2. Connect to your MySQL server.
3. Select the database (e.g., USE ada_wizard;).
4. Execute the SQL commands in this file to create the 'users' and 'websites' tables.
   You can copy and paste these commands into a MySQL client tool or run this file directly
   if your tool supports it (e.g., `mysql -u youruser -p yourdatabase < database_schema.sql`).
*/
