database.sql

-- AwesomeSoftware.lk Database Schema
-- Compatible with MySQL 5.7+ / MariaDB 10.3+

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

-- Internship positions table
CREATE TABLE IF NOT EXISTS internship_positions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL UNIQUE,
    title VARCHAR(200) NOT NULL,
    department VARCHAR(100) NOT NULL,
    type ENUM('Frontend','Backend','Cloud','UI/UX','Full Stack') NOT NULL,
    salary_min INT NOT NULL,
    salary_max INT NOT NULL,
    duration VARCHAR(50) DEFAULT '3 months',
    seats INT DEFAULT 2,
    skills_required TEXT,
    description TEXT,
    responsibilities TEXT,
    requirements TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Applications table
CREATE TABLE IF NOT EXISTS applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    position_id INT NOT NULL,
    position_title VARCHAR(200),
    full_name VARCHAR(200) NOT NULL,
    email VARCHAR(200) NOT NULL,
    phone VARCHAR(30),
    university VARCHAR(200),
    gpa VARCHAR(10),
    year_of_study VARCHAR(50),
    portfolio_url VARCHAR(500),
    github_url VARCHAR(500),
    linkedin_url VARCHAR(500),
    cover_letter TEXT,
    cv_filename VARCHAR(300),
    -- Quiz answers stored as JSON
    quiz_answers TEXT,
    quiz_score INT DEFAULT 0,
    quiz_total INT DEFAULT 0,
    status ENUM('pending','reviewing','invited','rejected','hired') DEFAULT 'pending',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (position_id) REFERENCES internship_positions(id)
);

-- Quiz questions table
CREATE TABLE IF NOT EXISTS quiz_questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    position_type ENUM('Frontend','Backend','Cloud','UI/UX','Full Stack','General') NOT NULL,
    question TEXT NOT NULL,
    option_a TEXT NOT NULL,
    option_b TEXT NOT NULL,
    option_c TEXT NOT NULL,
    option_d TEXT NOT NULL,
    correct_answer CHAR(1) NOT NULL,
    explanation TEXT,
    difficulty ENUM('easy','medium','hard') DEFAULT 'medium',
    sort_order INT DEFAULT 0
);

-- Contact messages
CREATE TABLE IF NOT EXISTS contact_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(200),
    email VARCHAR(200),
    company VARCHAR(200),
    message TEXT,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_read TINYINT(1) DEFAULT 0
);

-- ========================
-- SEED: Internship Positions
-- ========================
INSERT INTO internship_positions (slug, title, department, type, salary_min, salary_max, duration, seats, skills_required, description, responsibilities, requirements) VALUES

('frontend-developer-intern', 'Frontend Developer Intern', 'Engineering', 'Frontend', 25000, 30000, '3 months (extendable)', 3,
'HTML5, CSS3, JavaScript, React or Vue, Git, Responsive Design',
'Join our dynamic frontend team and build stunning, performant user interfaces for enterprise clients across Asia-Pacific. You will work directly with senior engineers and our Head of Design on real production projects.',
'• Build responsive UI components using React/Vue\n• Collaborate with designers to implement pixel-perfect designs\n• Optimize web performance (Core Web Vitals)\n• Write unit tests with Jest\n• Participate in code reviews',
'• Pursuing BSc in CS, IT, or related field (Year 2+)\n• Strong understanding of HTML5, CSS3, JavaScript (ES6+)\n• Familiarity with React or Vue.js\n• Portfolio or GitHub showing personal projects\n• Good communication skills in English'),

('backend-developer-intern', 'Backend Developer Intern', 'Engineering', 'Backend', 28000, 35000, '3 months (extendable)', 3,
'Java or Python or Node.js, REST APIs, SQL, Git, Basic Docker',
'Work alongside our senior backend engineers building scalable APIs, microservices, and data pipelines that process millions of transactions daily for our fintech and healthcare clients.',
'• Design and develop RESTful APIs\n• Work with relational & NoSQL databases\n• Build and optimize database queries\n• Write integration and unit tests\n• Deploy services via Docker/CI pipelines',
'• Pursuing BSc in CS, IT, Software Engineering (Year 2+)\n• Proficiency in Java, Python, or Node.js\n• Understanding of REST, SQL basics\n• Knowledge of Git version control\n• Logical problem-solving mindset'),

('cloud-devops-intern', 'Cloud & DevOps Intern', 'Infrastructure', 'Cloud', 30000, 38000, '3-6 months', 2,
'Linux, AWS/Azure basics, Docker, CI/CD, Bash scripting, Git',
'Gain hands-on experience with enterprise-grade cloud infrastructure. You will provision resources, build CI/CD pipelines, and work with Kubernetes clusters — skills that are in extremely high demand globally.',
'• Provision cloud resources on AWS or Azure\n• Build and maintain CI/CD pipelines (GitHub Actions, Jenkins)\n• Monitor infrastructure with Grafana/Prometheus\n• Write Infrastructure-as-Code (Terraform/CloudFormation)\n• Assist in Kubernetes cluster operations',
'• Pursuing BSc in CS, IT, Network Engineering (Year 2+)\n• Basic Linux/command line skills\n• Familiarity with AWS or Azure free tier\n• Understanding of Docker containers\n• AWS Cloud Practitioner (bonus)'),

('uiux-design-intern', 'UI/UX Design Intern', 'Design', 'UI/UX', 25000, 32000, '3 months (extendable)', 2,
'Figma, User Research, Wireframing, Prototyping, Adobe XD, Basic HTML/CSS',
'Work with Ravindu Silva (Head of Design) to create beautiful, user-centred digital experiences. Your designs will be implemented in real products used by hundreds of thousands of users across Sri Lanka.',
'• Design wireframes and interactive prototypes in Figma\n• Conduct user research and usability testing\n• Create and maintain design systems\n• Collaborate with frontend developers for handoff\n• Prepare motion & micro-interaction specs',
'• Pursuing BSc/BA in Design, HCI, CS, or related field\n• Strong Figma skills (portfolio required)\n• Understanding of UX principles & user psychology\n• Eye for typography, spacing, and visual hierarchy\n• Knowledge of WCAG accessibility (bonus)'),

('fullstack-developer-intern', 'Full Stack Developer Intern', 'Engineering', 'Full Stack', 30000, 40000, '3-6 months', 3,
'React or Vue, Node.js or Python, SQL, REST APIs, Git, Docker basics',
'Our most competitive internship — you will build complete features end-to-end, from database design to pixel-perfect UI. Reserved for highly motivated students who want to grow fast.',
'• Develop full features: database → API → UI\n• Build React/Next.js frontends\n• Create Node.js or Python backend services\n• Database design with PostgreSQL/MySQL\n• Deploy on cloud infrastructure\n• Present work in weekly demos',
'• Pursuing BSc in CS, IT, Software Engineering (Year 2+)\n• Comfortable in both frontend and backend code\n• Knowledge of SQL and REST\n• GitHub portfolio with full-stack projects (preferred)\n• Self-starter attitude, fast learner');

-- ========================
-- SEED: Quiz Questions
-- ========================

-- General questions (shown to all)
INSERT INTO quiz_questions (position_type, question, option_a, option_b, option_c, option_d, correct_answer, explanation, difficulty, sort_order) VALUES

('General', 'What does REST stand for in web development?', 
'Remote Execution Standard Technology', 'Representational State Transfer', 'Remote Service Technology', 'Redundant State Transfer',
'B', 'REST (Representational State Transfer) is an architectural style for designing networked applications.', 'easy', 1),

('General', 'Which HTTP method is used to UPDATE an existing resource?',
'GET', 'POST', 'PUT', 'DELETE',
'C', 'PUT (or PATCH) is used to update existing resources. POST creates, GET reads, DELETE removes.', 'easy', 2),

('General', 'What is the purpose of Git version control?',
'To design UI mockups', 'To track code changes and collaborate', 'To host websites', 'To test APIs',
'B', 'Git tracks changes to source code and enables collaboration among developers.', 'easy', 3),

('General', 'What does API stand for?',
'Application Programming Interface', 'Automated Process Integration', 'Application Process Interlink', 'Advanced Protocol Interface',
'A', 'API stands for Application Programming Interface — a way for software to communicate with other software.', 'easy', 4),

('General', 'Which of the following is NOT a programming paradigm?',
'Object-Oriented Programming', 'Functional Programming', 'Protocol Programming', 'Procedural Programming',
'C', 'Protocol Programming is not a recognized programming paradigm. OOP, FP, and Procedural are the main ones.', 'medium', 5);

-- Frontend questions
INSERT INTO quiz_questions (position_type, question, option_a, option_b, option_c, option_d, correct_answer, explanation, difficulty, sort_order) VALUES

('Frontend', 'What does CSS "Flexbox" primarily help with?',
'Database connectivity', 'Page layout and alignment', 'Server-side rendering', 'API calls',
'B', 'Flexbox is a CSS layout module that helps with aligning and distributing items within a container.', 'easy', 1),

('Frontend', 'In React, what hook is used to manage component state?',
'useEffect', 'useContext', 'useState', 'useRef',
'C', 'useState is the primary React hook for adding state to functional components.', 'easy', 2),

('Frontend', 'What is the Virtual DOM in React?',
'A direct copy of the browser DOM stored in memory', 'A lightweight JavaScript representation of the real DOM', 'A CSS framework', 'A database',
'B', 'React''s Virtual DOM is an in-memory representation that React uses to compute the minimal changes needed to update the real DOM.', 'medium', 3),

('Frontend', 'Which CSS property creates a smooth transition between values?',
'animation', 'transform', 'transition', 'keyframe',
'C', 'The CSS transition property enables smooth changes between property values over a specified duration.', 'easy', 4),

('Frontend', 'What is the purpose of "semantic HTML"?',
'To make HTML load faster', 'To use tags that convey meaning about content structure', 'To style elements', 'To run JavaScript',
'B', 'Semantic HTML uses meaningful tags (like <article>, <nav>, <header>) that describe the content''s purpose for accessibility and SEO.', 'medium', 5);

-- Backend questions
INSERT INTO quiz_questions (position_type, question, option_a, option_b, option_c, option_d, correct_answer, explanation, difficulty, sort_order) VALUES

('Backend', 'What is the difference between SQL and NoSQL databases?',
'SQL is newer than NoSQL', 'SQL uses structured tables; NoSQL uses flexible document/key-value stores', 'NoSQL is always faster', 'They are the same',
'B', 'SQL databases use structured relational tables with schemas; NoSQL databases offer flexible data models like documents, key-value, or graphs.', 'medium', 1),

('Backend', 'What does "N+1 query problem" mean in database access?',
'Database has more than 1 connection', 'Executing 1 query then N additional queries for related data instead of a JOIN', 'Using more than N+1 tables', 'A SQL syntax error',
'B', 'The N+1 problem occurs when code loads a list (1 query) and then fetches related data for each item (N queries), causing poor performance.', 'hard', 2),

('Backend', 'Which HTTP status code indicates a successful resource creation?',
'200 OK', '201 Created', '204 No Content', '400 Bad Request',
'B', '201 Created is the correct status code to return when a new resource has been successfully created.', 'easy', 3),

('Backend', 'What is the purpose of database indexing?',
'To encrypt data', 'To speed up data retrieval queries', 'To backup data', 'To validate data types',
'B', 'Indexes create data structures that allow the database engine to find rows much faster without scanning the entire table.', 'medium', 4),

('Backend', 'In a microservices architecture, services communicate via:',
'Shared database only', 'Direct function calls', 'APIs or message queues', 'SSH tunnels',
'C', 'Microservices communicate through well-defined APIs (REST, gRPC) or asynchronous message queues (Kafka, RabbitMQ).', 'medium', 5);

-- Cloud questions
INSERT INTO quiz_questions (position_type, question, option_a, option_b, option_c, option_d, correct_answer, explanation, difficulty, sort_order) VALUES

('Cloud', 'What does "IaC" stand for in DevOps?',
'Internet and Coding', 'Infrastructure as Code', 'Integration and Compliance', 'Internet as a Cloud',
'B', 'Infrastructure as Code (IaC) means managing and provisioning infrastructure through machine-readable configuration files.', 'easy', 1),

('Cloud', 'What is the primary purpose of a Kubernetes Pod?',
'To store persistent data', 'The smallest deployable unit, containing one or more containers', 'A load balancer', 'A virtual machine',
'B', 'A Kubernetes Pod is the smallest deployable unit and encapsulates one or more containers that share network and storage.', 'medium', 2),

('Cloud', 'What does CI/CD stand for?',
'Code Integration / Continuous Deployment', 'Continuous Integration / Continuous Delivery (or Deployment)', 'Cloud Infrastructure / Cloud DevOps', 'Code Inspection / Code Delivery',
'B', 'CI/CD stands for Continuous Integration / Continuous Delivery (or Deployment) — automating the build, test, and deployment pipeline.', 'easy', 3),

('Cloud', 'Which AWS service is used for serverless function execution?',
'EC2', 'S3', 'Lambda', 'RDS',
'C', 'AWS Lambda runs code in response to events without requiring server management — a core serverless compute service.', 'easy', 4),

('Cloud', 'What is the purpose of a load balancer?',
'Store static files', 'Distribute incoming traffic across multiple servers', 'Encrypt data at rest', 'Manage DNS records',
'B', 'A load balancer distributes incoming network traffic across multiple backend servers to improve availability and performance.', 'medium', 5);

-- UI/UX questions
INSERT INTO quiz_questions (position_type, question, option_a, option_b, option_c, option_d, correct_answer, explanation, difficulty, sort_order) VALUES

('UI/UX', 'What is a "wireframe" in UI/UX design?',
'A high-fidelity prototype with colors', 'A low-fidelity skeletal layout showing structure', 'A fully coded webpage', 'A branding guideline',
'B', 'Wireframes are low-fidelity blueprints that outline page structure and layout without visual design details.', 'easy', 1),

('UI/UX', 'What does WCAG 2.2 Level AA refer to?',
'A JavaScript framework version', 'Web Content Accessibility Guidelines for making content accessible', 'A color palette standard', 'A performance benchmark',
'B', 'WCAG (Web Content Accessibility Guidelines) defines standards for making web content accessible to people with disabilities.', 'medium', 2),

('UI/UX', 'What is a "design system"?',
'A collection of project management tools', 'A reusable set of components, styles, and guidelines for consistent product design', 'A wireframing software', 'A user testing method',
'B', 'A design system is a comprehensive set of reusable components, design tokens, and guidelines that teams use to build consistent products.', 'medium', 3),

('UI/UX', 'Which research method involves observing real users completing tasks?',
'A/B Testing', 'Usability Testing', 'Card Sorting', 'Heuristic Evaluation',
'B', 'Usability testing involves watching real users complete specific tasks to identify pain points and areas for improvement.', 'easy', 4),

('UI/UX', 'What is the "8pt grid system" in UI design?',
'A font size rule', 'A spacing system where all dimensions are multiples of 8', 'An 8-column layout', 'A color system with 8 shades',
'B', 'The 8pt grid system uses multiples of 8 (8, 16, 24, 32...) for sizing and spacing, creating visual harmony and consistency.', 'medium', 5);

-- Full Stack questions
INSERT INTO quiz_questions (position_type, question, option_a, option_b, option_c, option_d, correct_answer, explanation, difficulty, sort_order) VALUES

('Full Stack', 'What is CORS and why does it matter?',
'A caching strategy', 'Cross-Origin Resource Sharing — browser security controlling which domains can request resources', 'A CSS framework', 'A database query language',
'B', 'CORS (Cross-Origin Resource Sharing) is a browser mechanism that controls cross-domain HTTP requests for security.', 'medium', 1),

('Full Stack', 'What is the difference between Authentication and Authorization?',
'They are the same thing', 'Authentication verifies WHO you are; Authorization determines WHAT you can do', 'Authorization happens before Authentication', 'Authentication is only for APIs',
'B', 'Authentication verifies identity (login), while Authorization determines permissions (what resources you can access).', 'medium', 2),

('Full Stack', 'What is a JWT (JSON Web Token)?',
'A database format', 'A self-contained token for securely transmitting information between parties', 'A CSS animation', 'A JavaScript testing library',
'B', 'JWT is a compact, URL-safe means of representing claims to be transferred between two parties, commonly used for authentication.', 'medium', 3),

('Full Stack', 'In a MERN stack, what does each letter represent?',
'MySQL, Express, React, Node', 'MongoDB, Express, React, Node', 'MongoDB, Ember, Redux, Next', 'MySQL, Ember, React, Node',
'B', 'MERN stands for MongoDB, Express.js, React, and Node.js — a popular full-stack JavaScript technology stack.', 'easy', 4),

('Full Stack', 'What is the purpose of environment variables (.env files)?',
'To store database records', 'To keep sensitive configuration (API keys, passwords) out of source code', 'To define CSS variables', 'To configure DNS settings',
'B', 'Environment variables store sensitive configuration data that should not be committed to version control, like API keys and database credentials.', 'easy', 5);