CREATE TABLE IF NOT EXISTS settings (`key` VARCHAR(100) PRIMARY KEY, `value` TEXT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS banners (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(180) NOT NULL,subtitle VARCHAR(255) NULL,button_text VARCHAR(80) NULL,button_link VARCHAR(255) NULL,category VARCHAR(20) NOT NULL DEFAULT 'women',image VARCHAR(500) NULL,sort_order INT DEFAULT 0,active TINYINT(1) DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS content_blocks (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,slot VARCHAR(60) NOT NULL,title VARCHAR(180) NULL,text TEXT NULL,button_text VARCHAR(80) NULL,button_link VARCHAR(255) NULL,image VARCHAR(500) NULL,sort_order INT DEFAULT 0,active TINYINT(1) DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS products (id VARCHAR(36) PRIMARY KEY,name VARCHAR(180) NOT NULL,slug VARCHAR(220) NOT NULL UNIQUE,category VARCHAR(20) NOT NULL,description TEXT,short_description VARCHAR(500),price DECIMAL(14,2) NOT NULL,old_price DECIMAL(14,2) NULL,badge VARCHAR(50),image VARCHAR(500) NULL,active TINYINT(1) DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX(category),INDEX(active)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS product_images (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id VARCHAR(36) NOT NULL,url VARCHAR(500) NOT NULL,sort_order INT DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,CONSTRAINT fk_pi_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS product_variants (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id VARCHAR(36) NOT NULL,color VARCHAR(80) NOT NULL,size VARCHAR(40) NOT NULL,color_hex VARCHAR(20) NULL,sku VARCHAR(100),stock INT NOT NULL DEFAULT 0,image VARCHAR(500) NULL,UNIQUE KEY uniq_variant(product_id,color,size),CONSTRAINT fk_variant_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,INDEX(product_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS customers (id VARCHAR(36) PRIMARY KEY,name VARCHAR(150) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(300) NOT NULL,phone VARCHAR(40),address TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS customer_addresses (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_id VARCHAR(36) NOT NULL,title VARCHAR(80) DEFAULT 'آدرس اصلی',recipient_name VARCHAR(150) NOT NULL,phone VARCHAR(40) NOT NULL,province VARCHAR(100) NOT NULL,city VARCHAR(100) NOT NULL,address TEXT NOT NULL,postal_code VARCHAR(20) NOT NULL,is_default TINYINT(1) DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,CONSTRAINT fk_ca_customer FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,INDEX(customer_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sessions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,token_hash CHAR(64) NOT NULL UNIQUE,user_id VARCHAR(36) NULL,role VARCHAR(20) NOT NULL,expires_at DATETIME NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,last_seen_at DATETIME NULL,ip VARCHAR(64) NULL,user_agent VARCHAR(500) NULL,INDEX(user_id),INDEX(expires_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_no VARCHAR(50) NOT NULL UNIQUE,tracking_token CHAR(64) NOT NULL UNIQUE,customer_id VARCHAR(36) NULL,status VARCHAR(30) NOT NULL DEFAULT 'pending',payment_status VARCHAR(30) NOT NULL DEFAULT 'unpaid',subtotal DECIMAL(14,2) NOT NULL,discount DECIMAL(14,2) NOT NULL DEFAULT 0,shipping_cost DECIMAL(14,2) NOT NULL DEFAULT 0,total DECIMAL(14,2) NOT NULL,shipping_address JSON NOT NULL,coupon_code VARCHAR(80) NULL,inventory_reversed TINYINT(1) DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,paid_at DATETIME NULL,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,CONSTRAINT fk_order_customer FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE SET NULL,INDEX(status),INDEX(payment_status),INDEX(customer_id),INDEX(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS order_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id BIGINT UNSIGNED NOT NULL,product_id VARCHAR(36) NULL,variant_id BIGINT UNSIGNED NULL,product_name VARCHAR(180) NOT NULL,unit_price DECIMAL(14,2) NOT NULL,quantity INT NOT NULL,color VARCHAR(80),size VARCHAR(40),sku VARCHAR(100),CONSTRAINT fk_item_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,CONSTRAINT fk_item_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS payments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id BIGINT UNSIGNED NOT NULL,provider VARCHAR(40) NOT NULL,authority VARCHAR(100) NULL,ref_id VARCHAR(100) NULL,amount DECIMAL(14,2) NOT NULL,status VARCHAR(30) NOT NULL,raw_response JSON NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,CONSTRAINT fk_payment_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,INDEX(order_id),INDEX(authority)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS order_events (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id BIGINT UNSIGNED NOT NULL,status VARCHAR(30) NOT NULL,note VARCHAR(500) NULL,actor VARCHAR(80) NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,CONSTRAINT fk_event_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,INDEX(order_id,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS coupons (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(80) NOT NULL UNIQUE,type VARCHAR(20) NOT NULL,value DECIMAL(14,2) NOT NULL,min_amount DECIMAL(14,2) DEFAULT 0,max_uses INT NULL,used_count INT DEFAULT 0,expires_at DATETIME NULL,active TINYINT(1) DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS newsletter (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,email VARCHAR(190) NOT NULL UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;