CREATE TABLE users(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,role ENUM('admin','advertiser','participant') NOT NULL,username VARCHAR(80) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,full_name VARCHAR(160) NOT NULL,phone VARCHAR(30),email VARCHAR(190),status ENUM('active','pending','suspended','deleted') DEFAULT 'active',must_change_credentials TINYINT(1) DEFAULT 0,unique_code VARCHAR(30) UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE advertiser_profiles(user_id BIGINT UNSIGNED PRIMARY KEY,business_name VARCHAR(180) NOT NULL,instagram_url VARCHAR(255),city VARCHAR(100),address TEXT,description TEXT,verified TINYINT(1) DEFAULT 0,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE participant_profiles(user_id BIGINT UNSIGNED PRIMARY KEY,instagram_url VARCHAR(255) NOT NULL,followers INT UNSIGNED DEFAULT 0,card_number VARCHAR(20) NOT NULL,city VARCHAR(100),bio TEXT,verified TINYINT(1) DEFAULT 0,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE plans(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(100) NOT NULL,price BIGINT UNSIGNED NOT NULL,duration_days INT UNSIGNED DEFAULT 7,story_target INT UNSIGNED DEFAULT 0,active TINYINT(1) DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE campaigns(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,advertiser_id BIGINT UNSIGNED NOT NULL,plan_id INT UNSIGNED NOT NULL,title VARCHAR(180) NOT NULL,business_name VARCHAR(180) NOT NULL,instagram_url VARCHAR(255),description TEXT,banner_path VARCHAR(255),status ENUM('pending','approved','active','paused','rejected','completed') DEFAULT 'pending',start_at DATETIME,end_at DATETIME,budget BIGINT UNSIGNED DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(advertiser_id) REFERENCES users(id),FOREIGN KEY(plan_id) REFERENCES plans(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE campaign_participants(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,campaign_id BIGINT UNSIGNED NOT NULL,participant_id BIGINT UNSIGNED NOT NULL,status ENUM('assigned','story_submitted','verified','rejected','expired') DEFAULT 'assigned',story_url VARCHAR(500),submitted_at DATETIME,expires_at DATETIME,verified_at DATETIME,reward BIGINT UNSIGNED DEFAULT 0,UNIQUE KEY cp(campaign_id,participant_id),FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,FOREIGN KEY(participant_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE reward_rules(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,min_followers INT UNSIGNED NOT NULL,max_followers INT UNSIGNED,reward BIGINT UNSIGNED NOT NULL,active TINYINT(1) DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE wallet_transactions(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,type ENUM('reward','withdrawal','adjustment','refund') NOT NULL,amount BIGINT NOT NULL,description VARCHAR(255),reference_id VARCHAR(100),status ENUM('pending','approved','rejected') DEFAULT 'approved',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE withdrawals(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,participant_id BIGINT UNSIGNED NOT NULL,amount BIGINT UNSIGNED NOT NULL,card_number VARCHAR(20) NOT NULL,status ENUM('pending','approved','paid','rejected') DEFAULT 'pending',note TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(participant_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE audit_logs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED,action VARCHAR(120) NOT NULL,details TEXT,ip VARCHAR(64),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE settings(`key` VARCHAR(100) PRIMARY KEY,`value` TEXT);
INSERT INTO plans(title,price,duration_days,story_target) VALUES('پلن شروع',20000000,7,200),('پلن رشد',40000000,10,500),('پلن گسترده',80000000,14,1000);
INSERT INTO reward_rules(min_followers,max_followers,reward) VALUES(0,1000,50000),(1001,2000,70000),(2001,3000,100000),(3001,5000,130000),(5001,NULL,160000);
INSERT INTO settings(`key`,`value`) VALUES('platform_instagram','@mojtizer.ir'),('min_withdrawal','500000'),('story_check_minutes','120'),('restore_days','30'),('site_phone',''),('site_email','');