62 lines
2.3 KiB
SQL
62 lines
2.3 KiB
SQL
|
|
-- Projects table to store project information
|
|
CREATE TABLE IF NOT EXISTS `projects` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`client_id` INT NOT NULL,
|
|
`status` VARCHAR(50) DEFAULT 'Pending',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`client_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Assets table for creative assets linked to projects
|
|
CREATE TABLE IF NOT EXISTS `assets` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`project_id` INT NOT NULL,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`file_path` VARCHAR(255) NOT NULL,
|
|
`status` VARCHAR(50) DEFAULT 'Pending Approval',
|
|
`uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Comments on assets
|
|
CREATE TABLE IF NOT EXISTS `asset_comments` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`asset_id` INT NOT NULL,
|
|
`user_id` INT NOT NULL,
|
|
`comment` TEXT NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Courses available for purchase
|
|
CREATE TABLE IF NOT EXISTS `courses` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`name` VARCHAR(255) NOT NULL,
|
|
`description` TEXT,
|
|
`price` DECIMAL(10, 2) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Link table for users who have access to courses
|
|
CREATE TABLE IF NOT EXISTS `user_courses` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`user_id` INT NOT NULL,
|
|
`course_id` INT NOT NULL,
|
|
`purchased_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Invoices for billing
|
|
CREATE TABLE IF NOT EXISTS `invoices` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`client_id` INT NOT NULL,
|
|
`amount` DECIMAL(10, 2) NOT NULL,
|
|
`status` VARCHAR(50) DEFAULT 'Unpaid',
|
|
`due_date` DATE,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`client_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|