-- ============================================================
--  TasksPilot Database Schema
--  Run this in your MySQL/phpMyAdmin
-- ============================================================

CREATE DATABASE IF NOT EXISTS taskspilot CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE taskspilot;

-- ---------------------------------------------------------------
-- Table: tasks
-- Stores the task names and their associated URLs
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tasks (
    task_id       INT AUTO_INCREMENT PRIMARY KEY,
    task_name     VARCHAR(255)  NOT NULL,
    task_url      TEXT          NOT NULL,
    is_active     TINYINT(1)    NOT NULL DEFAULT 1,  -- 1 = active, 0 = hidden
    created_at    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sample task rows
INSERT INTO tasks (task_name, task_url) VALUES
  ('Watch Video Ad',   'https://example.com/task/watch-video'),
  ('Fill Survey',      'https://example.com/task/survey'),
  ('Visit Landing Page','https://example.com/landing-offer');

-- ---------------------------------------------------------------
-- Table: proxies
-- Stores SOCKS5 proxy credentials + usage status
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS proxies (
    proxy_id    INT AUTO_INCREMENT PRIMARY KEY,
    proxy_ip    VARCHAR(100)  NOT NULL,
    port        SMALLINT UNSIGNED NOT NULL,
    username    VARCHAR(100)  NOT NULL DEFAULT '',
    password    VARCHAR(100)  NOT NULL DEFAULT '',
    status      TINYINT(1)    NOT NULL DEFAULT 0,   -- 0 = unused, 1 = used/locked
    assigned_at TIMESTAMP     NULL DEFAULT NULL,     -- when the proxy was locked
    created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sample proxy rows  (replace with real proxies)
INSERT INTO proxies (proxy_ip, port, username, password) VALUES
  ('192.168.1.100', 1080, 'user1', 'pass1'),
  ('192.168.1.101', 1080, 'user2', 'pass2'),
  ('192.168.1.102', 1080, 'user3', 'pass3');

-- ---------------------------------------------------------------
-- Table: proxy_sessions  (optional – tracks which device used which proxy)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS proxy_sessions (
    session_id   INT AUTO_INCREMENT PRIMARY KEY,
    device_id    VARCHAR(255)  NOT NULL,
    proxy_id     INT           NOT NULL,
    task_id      INT           NOT NULL,
    locked_at    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    released_at  TIMESTAMP     NULL DEFAULT NULL,
    FOREIGN KEY (proxy_id) REFERENCES proxies(proxy_id),
    FOREIGN KEY (task_id)  REFERENCES tasks(task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
