-- Pinterest Pin Management System - Database Schema
-- Created: 2026-01-23

-- Database creation
CREATE DATABASE IF NOT EXISTS pinterest_manager CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE pinterest_manager;

-- Drop tables if they exist (for clean installation)
DROP TABLE IF EXISTS account_board;
DROP TABLE IF EXISTS affiliate_pins;
DROP TABLE IF EXISTS pins;
DROP TABLE IF EXISTS board_data;
DROP TABLE IF EXISTS stages;
DROP TABLE IF EXISTS categories;

-- Categories table
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pins table (main pin pool)
CREATE TABLE pins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    imagelink TEXT NOT NULL,
    postlink TEXT,
    futerimg TINYINT DEFAULT 0 COMMENT '0 or 1',
    category_id INT NOT NULL,
    inserted_date DATETIME NOT NULL,
    pin_id VARCHAR(255) NULL COMMENT 'Pinterest pin ID after posting',
    acc_username VARCHAR(255) NULL COMMENT 'Account that posted it',
    account_date DATE NULL,
    bord_id VARCHAR(255) NULL COMMENT 'Board ID where pin was posted',
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
    INDEX idx_pin_status (pin_id),
    INDEX idx_category (category_id),
    INDEX idx_account (acc_username),
    INDEX idx_inserted_date (inserted_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Affiliate pins table
CREATE TABLE affiliate_pins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    affiliate_link TEXT NOT NULL,
    imageurl TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Stages table (posting rules)
CREATE TABLE stages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT 'level 1, 2, 3',
    old_day INT NOT NULL COMMENT 'Minimum pin age in days',
    total_pins_perDay INT NOT NULL,
    number_pins INT NOT NULL,
    number_aff_pins INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Board data table (board templates)
CREATE TABLE board_data (
    bid INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
    INDEX idx_category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Account board mapping table
CREATE TABLE account_board (
    id INT AUTO_INCREMENT PRIMARY KEY,
    acc_username VARCHAR(255) NOT NULL,
    bord_id VARCHAR(255) NOT NULL COMMENT 'Pinterest board ID',
    bid INT NOT NULL,
    creation_date DATETIME NOT NULL,
    UNIQUE KEY unique_account_board (acc_username, bid),
    FOREIGN KEY (bid) REFERENCES board_data(bid) ON DELETE RESTRICT,
    INDEX idx_account (acc_username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data for testing


-- Sample stages
INSERT INTO stages (name, old_day, total_pins_perDay, number_pins, number_aff_pins) VALUES
('Level 1', 0, 10, 8, 2),
('Level 2', 7, 20, 15, 5),
('Level 3', 14, 30, 25, 5);


