-- Authentication System - Users Table Migration
-- Run this after the main schema to add authentication

USE zuriel_invoice_system;

-- Login Attempts Table (for security - track failed login attempts)
CREATE TABLE IF NOT EXISTS login_attempts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    success BOOLEAN DEFAULT FALSE,
    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_username_ip ON login_attempts(username, ip_address);
CREATE INDEX idx_attempted_at ON login_attempts(attempted_at);

-- Add created_by and updated_by to existing tables (optional - for audit trail)
-- Uncomment these if you want to track who created/modified records

-- ALTER TABLE invoices ADD COLUMN created_by INT AFTER status;
-- ALTER TABLE invoices ADD COLUMN updated_by INT AFTER created_by;
-- ALTER TABLE invoices ADD FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;
-- ALTER TABLE invoices ADD FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

-- ALTER TABLE receipts ADD COLUMN created_by INT AFTER status;
-- ALTER TABLE receipts ADD COLUMN updated_by INT AFTER created_by;
-- ALTER TABLE receipts ADD FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;
-- ALTER TABLE receipts ADD FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

-- ALTER TABLE customers ADD COLUMN created_by INT AFTER updated_at;
-- ALTER TABLE customers ADD FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;

-- ALTER TABLE products ADD COLUMN created_by INT AFTER updated_at;
-- ALTER TABLE products ADD FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;